Join query with view and inline view produced a different result

Hi:
I've a problem with the following querys, These two query is suppose to
produce a same result
but it is not, i don't know why.
The first query is using view, it procuce a correct result (2 rows),
the second query is using inline view (the inline view defination is
exactly the same as the view) but the result is wrong (4 rows).

>From the execution plan, the second query perform the join with the
inline view twist which is not correct.
Please help.
JCVoon
-- Join with view
SELECT Trx.PrincipalCode, Trx.ProductCode, SUM(Trx.BaseQuantity *
Trx.PhysicalFlag) AS BaseQty
FROM WmsStockLedger Trx
LEFT JOIN
(
SELECT * From view_PutHold
WHERE CompanyCode='HQ' And BranchCode = 'HQ'
) PutHold
ON PutHold.CompanyCode = Trx.CompanyCode
And PutHold.BranchCode = Trx.BranchCode
And PutHold.WONo = Trx.TxnNo
And PutHold.ProductCode = Trx.ProductCode
And PutHold.TallyInNo = Trx.TallyInNo
WHERE
Trx.CompanyCode='HQ'
And Trx.BranchCode='HQ'
And (CASE WHEN (LEFT(Trx.TXNNo,3) <> 'OP-') THEN
IsNull(PutHold.Completed,0) ELSE 1 END) = 1
GROUP BY Trx.PrincipalCode, Trx.ProductCode
HAVING SUM(Trx.BaseQuantity * Trx.PhysicalFlag) > 0
-- Join with inline view
SELECT Trx.PrincipalCode, Trx.ProductCode, SUM(Trx.BaseQuantity *
Trx.PhysicalFlag) AS BaseQty
FROM WmsStockLedger Trx
LEFT JOIN
(
Select Distinct Hd.CompanyCode, Hd.BranchCode, Hd.WoNo,
Dt.ProductCode, Dt.Completed, Ti.TallyInNo, Ti.PrincipalCode, dt.qty
FROM WmsPutawayHed Hd
INNER JOIN WmsPutawayDet Dt
ON Dt.CompanyCode = Hd.CompanyCode
And Dt.BranchCode = Hd.BranchCode
And Dt.WoNo = Hd.WoNo
And Dt.Completed = 1
INNER JOIN WmsTallyInHed Ti
ON Ti.CompanyCode = Hd.CompanyCode
And Ti.BranchCode = Hd.BranchCode
And Ti.TallyInNo = Hd.TallyInNo
WHERE Hd.CompanyCode = 'HQ'
And Hd.BranchCode = 'HQ'
) PutHold
ON PutHold.CompanyCode = Trx.CompanyCode
And PutHold.BranchCode = Trx.BranchCode
And PutHold.WONo = Trx.TxnNo
And PutHold.ProductCode = Trx.ProductCode
And PutHold.TallyInNo = Trx.TallyInNo
WHERE
Trx.CompanyCode='HQ'
And Trx.BranchCode='HQ'
And (CASE WHEN (LEFT(Trx.TXNNo,3) <> 'OP-') THEN
IsNull(PutHold.Completed,0) ELSE 1 END) = 1
GROUP BY Trx.PrincipalCode, Trx.ProductCode
HAVING SUM(Trx.BaseQuantity * Trx.PhysicalFlag) > 0
--Here is the DDL
CREATE TABLE [dbo].[WmsPutawayDet] (
[CompanyCode] [varchar] (2) NOT NULL ,
[BranchCode] [varchar] (2) NOT NULL ,
[WoNo] [varchar] (10) NOT NULL ,
[ProductCode] [varchar] (10) NOT NULL ,
[LocationCode] [varchar] (10) NOT NULL ,
[Qty] [numeric](18, 0) NOT NULL ,
[Completed] [bit] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[WmsPutawayHed] (
[CompanyCode] [varchar] (2) NOT NULL ,
[BranchCode] [varchar] (2) NOT NULL ,
[WoNo] [varchar] (10) NOT NULL ,
[TallyInNo] [varchar] (10) NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[WmsTallyInHed] (
[CompanyCode] [varchar] (2) NOT NULL ,
[BranchCode] [varchar] (2) NOT NULL ,
[TallyInNo] [varchar] (10) NOT NULL ,
[PrincipalCode] [varchar] (10) NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[wmsStockLedger] (
[CompanyCode] [varchar] (2) NOT NULL ,
[BranchCode] [varchar] (2) NOT NULL ,
[ProductCode] [varchar] (10) NOT NULL ,
[LocationCode] [varchar] (10) NOT NULL ,
[TallyInNo] [varchar] (10) NOT NULL ,
[PrincipalCode] [varchar] (10) NOT NULL ,
[TxnNo] [varchar] (10) NOT NULL ,
[BaseQuantity] [numeric](18, 0) NOT NULL ,
[PhysicalFlag] [int] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[WmsPutawayDet] ADD
CONSTRAINT [PK_WmsPutawayDet] PRIMARY KEY CLUSTERED
(
[CompanyCode],
[BranchCode],
[WoNo],
[ProductCode],
[LocationCode]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[WmsPutawayHed] ADD
CONSTRAINT [PK_WmsPutawayHed] PRIMARY KEY CLUSTERED
(
[CompanyCode],
[BranchCode],
[WoNo]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[WmsTallyInHed] ADD
CONSTRAINT [PK_WmsTallyInHed] PRIMARY KEY CLUSTERED
(
[CompanyCode],
[BranchCode],
[TallyInNo]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[wmsStockLedger] ADD
CONSTRAINT [PK_wmsStockLedger] PRIMARY KEY CLUSTERED
(
[CompanyCode],
[BranchCode],
[ProductCode],
[LocationCode],
[TallyInNo],
[PrincipalCode],
[TxnNo]
) ON [PRIMARY]
GO
create view view_PutHold as
Select Distinct Hd.CompanyCode, Hd.BranchCode, Hd.WoNo,
Dt.ProductCode, Dt.Completed, Ti.TallyInNo, Ti.PrincipalCode, Dt.qty
FROM WmsPutawayHed Hd
INNER JOIN WmsPutawayDet Dt
ON Dt.CompanyCode = Hd.CompanyCode
And Dt.BranchCode = Hd.BranchCode
And Dt.WoNo = Hd.WoNo
And Dt.Completed = 1
INNER JOIN WmsTallyInHed Ti
ON Ti.CompanyCode = Hd.CompanyCode
And Ti.BranchCode = Hd.BranchCode
And Ti.TallyInNo = Hd.TallyInNo
GO
INSERT INTO [WmsPutawayHed] VALUES('HQ','HQ','WO001','OP-001')
INSERT INTO [WmsPutawayHed] VALUES('HQ','HQ','WO002','OP-002')
INSERT INTO [WmsPutawayDet] VALUES('HQ','HQ','WO001','P1','A',5,1)
INSERT INTO [WmsPutawayDet] VALUES('HQ','HQ','WO001','P1','B',5,0)
INSERT INTO [WmsPutawayDet] VALUES('HQ','HQ','WO002','P2','A',10,1)
INSERT INTO [WmsPutawayDet] VALUES('HQ','HQ','WO002','P2','B',10,1)
INSERT INTO [WmsTallyInHed] VALUES('HQ','HQ','OP-001','P001')
INSERT INTO [WmsTallyInHed] VALUES('HQ','HQ','OP-002','P001')
INSERT INTO [WmsTallyInHed] VALUES('HQ','HQ','TI001','P001')
INSERT INTO [WmsTallyInHed] VALUES('HQ','HQ','TI002','P001')
INSERT INTO [WmsStockLedger]
VALUES('HQ','HQ','P1','A','OP-001','P001','WO001',5,1)
INSERT INTO [WmsStockLedger]
VALUES('HQ','HQ','P1','B','OP-001','P001','WO001',5,1)
INSERT INTO [WmsStockLedger]
VALUES('HQ','HQ','P1','HOLD','OP-001','P001','OP-001',10,1)
INSERT INTO [WmsStockLedger]
VALUES('HQ','HQ','P1','HOLD','OP-001','P001','WO001',10,-1)
INSERT INTO [WmsStockLedger]
VALUES('HQ','HQ','P2','A','OP-002','P001','WO002',10,1)
INSERT INTO [WmsStockLedger]
VALUES('HQ','HQ','P2','B','OP-002','P001','WO002',10,1)
INSERT INTO [WmsStockLedger]
VALUES('HQ','HQ','P2','HOLD','OP-002','P001','OP-002',20,1)
INSERT INTO [WmsStockLedger]
VALUES('HQ','HQ','P2','HOLD','OP-002','P001','WO002',20,-1)I am looking into this issue.
Looks like a problem with SQL server itself.
The query works as expected in SQL 2005 (returns only 2 rows in both cases)
Roji. P. Thomas
http://toponewithties.blogspot.com
"jcvoon" <jcvoon@.maximas.com.my> wrote in message
news:1136948778.631640.17620@.g49g2000cwa.googlegroups.com...
> Hi:
> I've a problem with the following querys, These two query is suppose to
> produce a same result
> but it is not, i don't know why.
> The first query is using view, it procuce a correct result (2 rows),
> the second query is using inline view (the inline view defination is
> exactly the same as the view) but the result is wrong (4 rows).
>
> inline view twist which is not correct.
> Please help.
> JCVoon
>
>
> -- Join with view
> SELECT Trx.PrincipalCode, Trx.ProductCode, SUM(Trx.BaseQuantity *
> Trx.PhysicalFlag) AS BaseQty
> FROM WmsStockLedger Trx
> LEFT JOIN
> (
> SELECT * From view_PutHold
> WHERE CompanyCode='HQ' And BranchCode = 'HQ'
> ) PutHold
> ON PutHold.CompanyCode = Trx.CompanyCode
> And PutHold.BranchCode = Trx.BranchCode
> And PutHold.WONo = Trx.TxnNo
> And PutHold.ProductCode = Trx.ProductCode
> And PutHold.TallyInNo = Trx.TallyInNo
> WHERE
> Trx.CompanyCode='HQ'
> And Trx.BranchCode='HQ'
> And (CASE WHEN (LEFT(Trx.TXNNo,3) <> 'OP-') THEN
> IsNull(PutHold.Completed,0) ELSE 1 END) = 1
> GROUP BY Trx.PrincipalCode, Trx.ProductCode
> HAVING SUM(Trx.BaseQuantity * Trx.PhysicalFlag) > 0
> -- Join with inline view
> SELECT Trx.PrincipalCode, Trx.ProductCode, SUM(Trx.BaseQuantity *
> Trx.PhysicalFlag) AS BaseQty
> FROM WmsStockLedger Trx
> LEFT JOIN
> (
> Select Distinct Hd.CompanyCode, Hd.BranchCode, Hd.WoNo,
> Dt.ProductCode, Dt.Completed, Ti.TallyInNo, Ti.PrincipalCode, dt.qty
> FROM WmsPutawayHed Hd
> INNER JOIN WmsPutawayDet Dt
> ON Dt.CompanyCode = Hd.CompanyCode
> And Dt.BranchCode = Hd.BranchCode
> And Dt.WoNo = Hd.WoNo
> And Dt.Completed = 1
> INNER JOIN WmsTallyInHed Ti
> ON Ti.CompanyCode = Hd.CompanyCode
> And Ti.BranchCode = Hd.BranchCode
> And Ti.TallyInNo = Hd.TallyInNo
> WHERE Hd.CompanyCode = 'HQ'
> And Hd.BranchCode = 'HQ'
> ) PutHold
> ON PutHold.CompanyCode = Trx.CompanyCode
> And PutHold.BranchCode = Trx.BranchCode
> And PutHold.WONo = Trx.TxnNo
> And PutHold.ProductCode = Trx.ProductCode
> And PutHold.TallyInNo = Trx.TallyInNo
> WHERE
> Trx.CompanyCode='HQ'
> And Trx.BranchCode='HQ'
> And (CASE WHEN (LEFT(Trx.TXNNo,3) <> 'OP-') THEN
> IsNull(PutHold.Completed,0) ELSE 1 END) = 1
> GROUP BY Trx.PrincipalCode, Trx.ProductCode
> HAVING SUM(Trx.BaseQuantity * Trx.PhysicalFlag) > 0
>
> --Here is the DDL
> CREATE TABLE [dbo].[WmsPutawayDet] (
> [CompanyCode] [varchar] (2) NOT NULL ,
> [BranchCode] [varchar] (2) NOT NULL ,
> [WoNo] [varchar] (10) NOT NULL ,
> [ProductCode] [varchar] (10) NOT NULL ,
> [LocationCode] [varchar] (10) NOT NULL ,
> [Qty] [numeric](18, 0) NOT NULL ,
> [Completed] [bit] NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[WmsPutawayHed] (
> [CompanyCode] [varchar] (2) NOT NULL ,
> [BranchCode] [varchar] (2) NOT NULL ,
> [WoNo] [varchar] (10) NOT NULL ,
> [TallyInNo] [varchar] (10) NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[WmsTallyInHed] (
> [CompanyCode] [varchar] (2) NOT NULL ,
> [BranchCode] [varchar] (2) NOT NULL ,
> [TallyInNo] [varchar] (10) NOT NULL ,
> [PrincipalCode] [varchar] (10) NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[wmsStockLedger] (
> [CompanyCode] [varchar] (2) NOT NULL ,
> [BranchCode] [varchar] (2) NOT NULL ,
> [ProductCode] [varchar] (10) NOT NULL ,
> [LocationCode] [varchar] (10) NOT NULL ,
> [TallyInNo] [varchar] (10) NOT NULL ,
> [PrincipalCode] [varchar] (10) NOT NULL ,
> [TxnNo] [varchar] (10) NOT NULL ,
> [BaseQuantity] [numeric](18, 0) NOT NULL ,
> [PhysicalFlag] [int] NOT NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[WmsPutawayDet] ADD
> CONSTRAINT [PK_WmsPutawayDet] PRIMARY KEY CLUSTERED
> (
> [CompanyCode],
> [BranchCode],
> [WoNo],
> [ProductCode],
> [LocationCode]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[WmsPutawayHed] ADD
> CONSTRAINT [PK_WmsPutawayHed] PRIMARY KEY CLUSTERED
> (
> [CompanyCode],
> [BranchCode],
> [WoNo]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[WmsTallyInHed] ADD
> CONSTRAINT [PK_WmsTallyInHed] PRIMARY KEY CLUSTERED
> (
> [CompanyCode],
> [BranchCode],
> [TallyInNo]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[wmsStockLedger] ADD
> CONSTRAINT [PK_wmsStockLedger] PRIMARY KEY CLUSTERED
> (
> [CompanyCode],
> [BranchCode],
> [ProductCode],
> [LocationCode],
> [TallyInNo],
> [PrincipalCode],
> [TxnNo]
> ) ON [PRIMARY]
> GO
> create view view_PutHold as
> Select Distinct Hd.CompanyCode, Hd.BranchCode, Hd.WoNo,
> Dt.ProductCode, Dt.Completed, Ti.TallyInNo, Ti.PrincipalCode, Dt.qty
> FROM WmsPutawayHed Hd
> INNER JOIN WmsPutawayDet Dt
> ON Dt.CompanyCode = Hd.CompanyCode
> And Dt.BranchCode = Hd.BranchCode
> And Dt.WoNo = Hd.WoNo
> And Dt.Completed = 1
> INNER JOIN WmsTallyInHed Ti
> ON Ti.CompanyCode = Hd.CompanyCode
> And Ti.BranchCode = Hd.BranchCode
> And Ti.TallyInNo = Hd.TallyInNo
> GO
> INSERT INTO [WmsPutawayHed] VALUES('HQ','HQ','WO001','OP-001')
> INSERT INTO [WmsPutawayHed] VALUES('HQ','HQ','WO002','OP-002')
> INSERT INTO [WmsPutawayDet] VALUES('HQ','HQ','WO001','P1','A',5,1)
> INSERT INTO [WmsPutawayDet] VALUES('HQ','HQ','WO001','P1','B',5,0)
> INSERT INTO [WmsPutawayDet] VALUES('HQ','HQ','WO002','P2','A',10,1)
> INSERT INTO [WmsPutawayDet] VALUES('HQ','HQ','WO002','P2','B',10,1)
> INSERT INTO [WmsTallyInHed] VALUES('HQ','HQ','OP-001','P001')
> INSERT INTO [WmsTallyInHed] VALUES('HQ','HQ','OP-002','P001')
> INSERT INTO [WmsTallyInHed] VALUES('HQ','HQ','TI001','P001')
> INSERT INTO [WmsTallyInHed] VALUES('HQ','HQ','TI002','P001')
> INSERT INTO [WmsStockLedger]
> VALUES('HQ','HQ','P1','A','OP-001','P001','WO001',5,1)
> INSERT INTO [WmsStockLedger]
> VALUES('HQ','HQ','P1','B','OP-001','P001','WO001',5,1)
> INSERT INTO [WmsStockLedger]
> VALUES('HQ','HQ','P1','HOLD','OP-001','P001','OP-001',10,1)
> INSERT INTO [WmsStockLedger]
> VALUES('HQ','HQ','P1','HOLD','OP-001','P001','WO001',10,-1)
> INSERT INTO [WmsStockLedger]
> VALUES('HQ','HQ','P2','A','OP-002','P001','WO002',10,1)
> INSERT INTO [WmsStockLedger]
> VALUES('HQ','HQ','P2','B','OP-002','P001','WO002',10,1)
> INSERT INTO [WmsStockLedger]
> VALUES('HQ','HQ','P2','HOLD','OP-002','P001','OP-002',20,1)
> INSERT INTO [WmsStockLedger]
> VALUES('HQ','HQ','P2','HOLD','OP-002','P001','WO002',20,-1)
>|||The problem appears to be in the section
And (CASE WHEN (LEFT(Trx.TXNNo,3) <> 'OP-') THEN
IsNull(PutHold.Completed,0) ELSE 1 END) = 1
If you just comment that and run the query, the result is correct.
Also if you comment the
SUM(Trx.BaseQuantity * Trx.PhysicalFlag)
line, the query gives the correct result.
I am still not sure whether its a known bug. I will update you once I have
more info.
BTW thankls for posting the DDL.
SELECT Trx.PrincipalCode, Trx.ProductCode, SUM(Trx.BaseQuantity *
Trx.PhysicalFlag)
FROM WmsStockLedger Trx
LEFT JOIN
(Select Distinct Hd.CompanyCode, Hd.BranchCode, Hd.WoNo,
Dt.ProductCode, Dt.Completed, Ti.TallyInNo, Ti.PrincipalCode, dt.qty
FROM WmsPutawayHed Hd
INNER JOIN WmsPutawayDet Dt
ON Dt.CompanyCode = Hd.CompanyCode
And Dt.BranchCode = Hd.BranchCode
And Dt.WoNo = Hd.WoNo
And Dt.Completed = 1
INNER JOIN WmsTallyInHed Ti
ON Ti.CompanyCode = Hd.CompanyCode
And Ti.BranchCode = Hd.BranchCode
And Ti.TallyInNo = Hd.TallyInNo
WHERE Hd.CompanyCode = 'HQ'
And Hd.BranchCode = 'HQ') PutHold
ON PutHold.CompanyCode = Trx.CompanyCode
And PutHold.BranchCode = Trx.BranchCode
And PutHold.WONo = Trx.TxnNo
And PutHold.ProductCode = Trx.ProductCode
And PutHold.TallyInNo = Trx.TallyInNo
WHERE
Trx.CompanyCode='HQ'
And Trx.BranchCode='HQ'
--And (CASE WHEN (LEFT(Trx.TXNNo,3) <> 'OP-') THEN
--IsNull(PutHold.Completed,0) ELSE 1 END) = 1
GROUP BY Trx.PrincipalCode, Trx.ProductCode
HAVING SUM(Trx.BaseQuantity * Trx.PhysicalFlag) > 0
Roji. P. Thomas
http://toponewithties.blogspot.com
"Roji. P. Thomas" <thomasroji@.gmail.com> wrote in message
news:OJsl8IpFGHA.2320@.TK2MSFTNGP11.phx.gbl...
>I am looking into this issue.
> Looks like a problem with SQL server itself.
> The query works as expected in SQL 2005 (returns only 2 rows in both
> cases)
> --
> Roji. P. Thomas
> http://toponewithties.blogspot.com
>
> "jcvoon" <jcvoon@.maximas.com.my> wrote in message
> news:1136948778.631640.17620@.g49g2000cwa.googlegroups.com...
>|||I observed that commenting the line
will solve the problem.
So here is a workaround, other than using the view.
SELECT PrincipalCode, ProductCode, BaseQty
FROM
(
SELECT Trx.PrincipalCode, Trx.ProductCode, SUM(Trx.BaseQuantity *
Trx.PhysicalFlag) AS BaseQty
FROM WmsStockLedger Trx
LEFT JOIN
(Select Distinct Hd.CompanyCode, Hd.BranchCode, Hd.WoNo,
Dt.ProductCode, Dt.Completed, Ti.TallyInNo, Ti.PrincipalCode, dt.qty
FROM WmsPutawayHed Hd
INNER JOIN WmsPutawayDet Dt
ON Dt.CompanyCode = Hd.CompanyCode
And Dt.BranchCode = Hd.BranchCode
And Dt.WoNo = Hd.WoNo
And Dt.Completed = 1
INNER JOIN WmsTallyInHed Ti
ON Ti.CompanyCode = Hd.CompanyCode
And Ti.BranchCode = Hd.BranchCode
And Ti.TallyInNo = Hd.TallyInNo
WHERE Hd.CompanyCode = 'HQ'
And Hd.BranchCode = 'HQ') PutHold
ON PutHold.CompanyCode = Trx.CompanyCode
And PutHold.BranchCode = Trx.BranchCode
And PutHold.WONo = Trx.TxnNo
And PutHold.ProductCode = Trx.ProductCode
And PutHold.TallyInNo = Trx.TallyInNo
WHERE
Trx.CompanyCode='HQ'
And Trx.BranchCode='HQ'
AND (LEFT(Trx.TXNNo,3) = 'OP-'
OR PutHold.Completed = 1)
GROUP BY Trx.PrincipalCode, Trx.ProductCode)T
WHERE BaseQty > 0
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"Roji. P. Thomas" <thomasroji@.gmail.com> wrote in message
news:uciunfpFGHA.1260@.TK2MSFTNGP15.phx.gbl...
> The problem appears to be in the section
> And (CASE WHEN (LEFT(Trx.TXNNo,3) <> 'OP-') THEN
> IsNull(PutHold.Completed,0) ELSE 1 END) = 1
> If you just comment that and run the query, the result is correct.
> Also if you comment the
> SUM(Trx.BaseQuantity * Trx.PhysicalFlag)
> line, the query gives the correct result.
> I am still not sure whether its a known bug. I will update you once I have
> more info.
> BTW thankls for posting the DDL.
>
> SELECT Trx.PrincipalCode, Trx.ProductCode, SUM(Trx.BaseQuantity *
> Trx.PhysicalFlag)
> FROM WmsStockLedger Trx
> LEFT JOIN
> (Select Distinct Hd.CompanyCode, Hd.BranchCode, Hd.WoNo,
> Dt.ProductCode, Dt.Completed, Ti.TallyInNo, Ti.PrincipalCode, dt.qty
> FROM WmsPutawayHed Hd
> INNER JOIN WmsPutawayDet Dt
> ON Dt.CompanyCode = Hd.CompanyCode
> And Dt.BranchCode = Hd.BranchCode
> And Dt.WoNo = Hd.WoNo
> And Dt.Completed = 1
> INNER JOIN WmsTallyInHed Ti
> ON Ti.CompanyCode = Hd.CompanyCode
> And Ti.BranchCode = Hd.BranchCode
> And Ti.TallyInNo = Hd.TallyInNo
> WHERE Hd.CompanyCode = 'HQ'
> And Hd.BranchCode = 'HQ') PutHold
> ON PutHold.CompanyCode = Trx.CompanyCode
> And PutHold.BranchCode = Trx.BranchCode
> And PutHold.WONo = Trx.TxnNo
> And PutHold.ProductCode = Trx.ProductCode
> And PutHold.TallyInNo = Trx.TallyInNo
> WHERE
> Trx.CompanyCode='HQ'
> And Trx.BranchCode='HQ'
> --And (CASE WHEN (LEFT(Trx.TXNNo,3) <> 'OP-') THEN
> --IsNull(PutHold.Completed,0) ELSE 1 END) = 1
> GROUP BY Trx.PrincipalCode, Trx.ProductCode
> HAVING SUM(Trx.BaseQuantity * Trx.PhysicalFlag) > 0
> --
> Roji. P. Thomas
> http://toponewithties.blogspot.com
>
> "Roji. P. Thomas" <thomasroji@.gmail.com> wrote in message
> news:OJsl8IpFGHA.2320@.TK2MSFTNGP11.phx.gbl...
>|||Roji. P. Thomas:
Thanks for your help.
Comment the HAVING clause will also return 2 rows.
Please update me if u found any thing.
Regards
JCVoon|||>I observed that commenting the line

>will solve the problem
Read
I observed that commenting the line
HAVING SUM(Trx.BaseQuantity * Trx.PhysicalFlag) > 0
will solve the problem
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"Roji. P. Thomas" <thomasroji@.gmail.com> wrote in message
news:uqT$jHqFGHA.516@.TK2MSFTNGP15.phx.gbl...
>I observed that commenting the line
> will solve the problem.
> So here is a workaround, other than using the view.
> SELECT PrincipalCode, ProductCode, BaseQty
> FROM
> (
> SELECT Trx.PrincipalCode, Trx.ProductCode, SUM(Trx.BaseQuantity *
> Trx.PhysicalFlag) AS BaseQty
> FROM WmsStockLedger Trx
> LEFT JOIN
> (Select Distinct Hd.CompanyCode, Hd.BranchCode, Hd.WoNo,
> Dt.ProductCode, Dt.Completed, Ti.TallyInNo, Ti.PrincipalCode, dt.qty
> FROM WmsPutawayHed Hd
> INNER JOIN WmsPutawayDet Dt
> ON Dt.CompanyCode = Hd.CompanyCode
> And Dt.BranchCode = Hd.BranchCode
> And Dt.WoNo = Hd.WoNo
> And Dt.Completed = 1
> INNER JOIN WmsTallyInHed Ti
> ON Ti.CompanyCode = Hd.CompanyCode
> And Ti.BranchCode = Hd.BranchCode
> And Ti.TallyInNo = Hd.TallyInNo
> WHERE Hd.CompanyCode = 'HQ'
> And Hd.BranchCode = 'HQ') PutHold
> ON PutHold.CompanyCode = Trx.CompanyCode
> And PutHold.BranchCode = Trx.BranchCode
> And PutHold.WONo = Trx.TxnNo
> And PutHold.ProductCode = Trx.ProductCode
> And PutHold.TallyInNo = Trx.TallyInNo
> WHERE
> Trx.CompanyCode='HQ'
> And Trx.BranchCode='HQ'
> AND (LEFT(Trx.TXNNo,3) = 'OP-'
> OR PutHold.Completed = 1)
> GROUP BY Trx.PrincipalCode, Trx.ProductCode)T
> WHERE BaseQty > 0
> --
> Roji. P. Thomas
> Net Asset Management
> http://toponewithties.blogspot.com
>
> "Roji. P. Thomas" <thomasroji@.gmail.com> wrote in message
> news:uciunfpFGHA.1260@.TK2MSFTNGP15.phx.gbl...
>|||Here is a repro for others looking into the problem.
(SQL Server 2000 SP4)
The query without the last line (HAVING ) returns 3 rows, which is correct.
With HAVING it returns 6 rows and the result is incorrect
Use Pubs
GO
SELECT T.pub_id, T.type, SUM(T.price * 1) AS BasePrice
FROM Titles T
LEFT JOIN
(Select NULL) X (pub_id)
ON X.pub_id = T.pub_id
WHERE LEFT(T.title,3) = 'The'
GROUP BY T.pub_id, T.type
HAVING SUM(T.price * 1) > 0
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"jcvoon" <jcvoon@.maximas.com.my> wrote in message
news:1136948778.631640.17620@.g49g2000cwa.googlegroups.com...
> Hi:
> I've a problem with the following querys, These two query is suppose to
> produce a same result
> but it is not, i don't know why.
> The first query is using view, it procuce a correct result (2 rows),
> the second query is using inline view (the inline view defination is
> exactly the same as the view) but the result is wrong (4 rows).
>
> inline view twist which is not correct.
> Please help.
> JCVoon
>
>
> -- Join with view
> SELECT Trx.PrincipalCode, Trx.ProductCode, SUM(Trx.BaseQuantity *
> Trx.PhysicalFlag) AS BaseQty
> FROM WmsStockLedger Trx
> LEFT JOIN
> (
> SELECT * From view_PutHold
> WHERE CompanyCode='HQ' And BranchCode = 'HQ'
> ) PutHold
> ON PutHold.CompanyCode = Trx.CompanyCode
> And PutHold.BranchCode = Trx.BranchCode
> And PutHold.WONo = Trx.TxnNo
> And PutHold.ProductCode = Trx.ProductCode
> And PutHold.TallyInNo = Trx.TallyInNo
> WHERE
> Trx.CompanyCode='HQ'
> And Trx.BranchCode='HQ'
> And (CASE WHEN (LEFT(Trx.TXNNo,3) <> 'OP-') THEN
> IsNull(PutHold.Completed,0) ELSE 1 END) = 1
> GROUP BY Trx.PrincipalCode, Trx.ProductCode
> HAVING SUM(Trx.BaseQuantity * Trx.PhysicalFlag) > 0
> -- Join with inline view
> SELECT Trx.PrincipalCode, Trx.ProductCode, SUM(Trx.BaseQuantity *
> Trx.PhysicalFlag) AS BaseQty
> FROM WmsStockLedger Trx
> LEFT JOIN
> (
> Select Distinct Hd.CompanyCode, Hd.BranchCode, Hd.WoNo,
> Dt.ProductCode, Dt.Completed, Ti.TallyInNo, Ti.PrincipalCode, dt.qty
> FROM WmsPutawayHed Hd
> INNER JOIN WmsPutawayDet Dt
> ON Dt.CompanyCode = Hd.CompanyCode
> And Dt.BranchCode = Hd.BranchCode
> And Dt.WoNo = Hd.WoNo
> And Dt.Completed = 1
> INNER JOIN WmsTallyInHed Ti
> ON Ti.CompanyCode = Hd.CompanyCode
> And Ti.BranchCode = Hd.BranchCode
> And Ti.TallyInNo = Hd.TallyInNo
> WHERE Hd.CompanyCode = 'HQ'
> And Hd.BranchCode = 'HQ'
> ) PutHold
> ON PutHold.CompanyCode = Trx.CompanyCode
> And PutHold.BranchCode = Trx.BranchCode
> And PutHold.WONo = Trx.TxnNo
> And PutHold.ProductCode = Trx.ProductCode
> And PutHold.TallyInNo = Trx.TallyInNo
> WHERE
> Trx.CompanyCode='HQ'
> And Trx.BranchCode='HQ'
> And (CASE WHEN (LEFT(Trx.TXNNo,3) <> 'OP-') THEN
> IsNull(PutHold.Completed,0) ELSE 1 END) = 1
> GROUP BY Trx.PrincipalCode, Trx.ProductCode
> HAVING SUM(Trx.BaseQuantity * Trx.PhysicalFlag) > 0
>
> --Here is the DDL
> CREATE TABLE [dbo].[WmsPutawayDet] (
> [CompanyCode] [varchar] (2) NOT NULL ,
> [BranchCode] [varchar] (2) NOT NULL ,
> [WoNo] [varchar] (10) NOT NULL ,
> [ProductCode] [varchar] (10) NOT NULL ,
> [LocationCode] [varchar] (10) NOT NULL ,
> [Qty] [numeric](18, 0) NOT NULL ,
> [Completed] [bit] NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[WmsPutawayHed] (
> [CompanyCode] [varchar] (2) NOT NULL ,
> [BranchCode] [varchar] (2) NOT NULL ,
> [WoNo] [varchar] (10) NOT NULL ,
> [TallyInNo] [varchar] (10) NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[WmsTallyInHed] (
> [CompanyCode] [varchar] (2) NOT NULL ,
> [BranchCode] [varchar] (2) NOT NULL ,
> [TallyInNo] [varchar] (10) NOT NULL ,
> [PrincipalCode] [varchar] (10) NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[wmsStockLedger] (
> [CompanyCode] [varchar] (2) NOT NULL ,
> [BranchCode] [varchar] (2) NOT NULL ,
> [ProductCode] [varchar] (10) NOT NULL ,
> [LocationCode] [varchar] (10) NOT NULL ,
> [TallyInNo] [varchar] (10) NOT NULL ,
> [PrincipalCode] [varchar] (10) NOT NULL ,
> [TxnNo] [varchar] (10) NOT NULL ,
> [BaseQuantity] [numeric](18, 0) NOT NULL ,
> [PhysicalFlag] [int] NOT NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[WmsPutawayDet] ADD
> CONSTRAINT [PK_WmsPutawayDet] PRIMARY KEY CLUSTERED
> (
> [CompanyCode],
> [BranchCode],
> [WoNo],
> [ProductCode],
> [LocationCode]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[WmsPutawayHed] ADD
> CONSTRAINT [PK_WmsPutawayHed] PRIMARY KEY CLUSTERED
> (
> [CompanyCode],
> [BranchCode],
> [WoNo]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[WmsTallyInHed] ADD
> CONSTRAINT [PK_WmsTallyInHed] PRIMARY KEY CLUSTERED
> (
> [CompanyCode],
> [BranchCode],
> [TallyInNo]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[wmsStockLedger] ADD
> CONSTRAINT [PK_wmsStockLedger] PRIMARY KEY CLUSTERED
> (
> [CompanyCode],
> [BranchCode],
> [ProductCode],
> [LocationCode],
> [TallyInNo],
> [PrincipalCode],
> [TxnNo]
> ) ON [PRIMARY]
> GO
> create view view_PutHold as
> Select Distinct Hd.CompanyCode, Hd.BranchCode, Hd.WoNo,
> Dt.ProductCode, Dt.Completed, Ti.TallyInNo, Ti.PrincipalCode, Dt.qty
> FROM WmsPutawayHed Hd
> INNER JOIN WmsPutawayDet Dt
> ON Dt.CompanyCode = Hd.CompanyCode
> And Dt.BranchCode = Hd.BranchCode
> And Dt.WoNo = Hd.WoNo
> And Dt.Completed = 1
> INNER JOIN WmsTallyInHed Ti
> ON Ti.CompanyCode = Hd.CompanyCode
> And Ti.BranchCode = Hd.BranchCode
> And Ti.TallyInNo = Hd.TallyInNo
> GO
> INSERT INTO [WmsPutawayHed] VALUES('HQ','HQ','WO001','OP-001')
> INSERT INTO [WmsPutawayHed] VALUES('HQ','HQ','WO002','OP-002')
> INSERT INTO [WmsPutawayDet] VALUES('HQ','HQ','WO001','P1','A',5,1)
> INSERT INTO [WmsPutawayDet] VALUES('HQ','HQ','WO001','P1','B',5,0)
> INSERT INTO [WmsPutawayDet] VALUES('HQ','HQ','WO002','P2','A',10,1)
> INSERT INTO [WmsPutawayDet] VALUES('HQ','HQ','WO002','P2','B',10,1)
> INSERT INTO [WmsTallyInHed] VALUES('HQ','HQ','OP-001','P001')
> INSERT INTO [WmsTallyInHed] VALUES('HQ','HQ','OP-002','P001')
> INSERT INTO [WmsTallyInHed] VALUES('HQ','HQ','TI001','P001')
> INSERT INTO [WmsTallyInHed] VALUES('HQ','HQ','TI002','P001')
> INSERT INTO [WmsStockLedger]
> VALUES('HQ','HQ','P1','A','OP-001','P001','WO001',5,1)
> INSERT INTO [WmsStockLedger]
> VALUES('HQ','HQ','P1','B','OP-001','P001','WO001',5,1)
> INSERT INTO [WmsStockLedger]
> VALUES('HQ','HQ','P1','HOLD','OP-001','P001','OP-001',10,1)
> INSERT INTO [WmsStockLedger]
> VALUES('HQ','HQ','P1','HOLD','OP-001','P001','WO001',10,-1)
> INSERT INTO [WmsStockLedger]
> VALUES('HQ','HQ','P2','A','OP-002','P001','WO002',10,1)
> INSERT INTO [WmsStockLedger]
> VALUES('HQ','HQ','P2','B','OP-002','P001','WO002',10,1)
> INSERT INTO [WmsStockLedger]
> VALUES('HQ','HQ','P2','HOLD','OP-002','P001','OP-002',20,1)
> INSERT INTO [WmsStockLedger]
> VALUES('HQ','HQ','P2','HOLD','OP-002','P001','WO002',20,-1)
>|||Yes. this is a known bug.
http://support.microsoft.com/kb/308458/en-us
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"Roji. P. Thomas" <thomasroji@.gmail.com> wrote in message
news:ezkvteqFGHA.3000@.TK2MSFTNGP14.phx.gbl...
> Here is a repro for others looking into the problem.
> (SQL Server 2000 SP4)
> The query without the last line (HAVING ) returns 3 rows, which is
> correct.
> With HAVING it returns 6 rows and the result is incorrect
>
> Use Pubs
> GO
> SELECT T.pub_id, T.type, SUM(T.price * 1) AS BasePrice
> FROM Titles T
> LEFT JOIN
> (Select NULL) X (pub_id)
> ON X.pub_id = T.pub_id
> WHERE LEFT(T.title,3) = 'The'
> GROUP BY T.pub_id, T.type
> HAVING SUM(T.price * 1) > 0
>
> --
> Roji. P. Thomas
> Net Asset Management
> http://toponewithties.blogspot.com
>
> "jcvoon" <jcvoon@.maximas.com.my> wrote in message
> news:1136948778.631640.17620@.g49g2000cwa.googlegroups.com...
>|||Here is the best fix so far.
Just change LEFT(Trx.TXNNo,3) with SUBSTRING(Trx.TXNNo,1,3)
That seems to prevent the otimizer from doing the incorrect cross join.
SELECT Trx.PrincipalCode, Trx.ProductCode, SUM(Trx.BaseQuantity*
Trx.PhysicalFlag) AS BaseQty
FROM WmsStockLedger Trx
LEFT JOIN
(
Select Distinct Hd.CompanyCode, Hd.BranchCode, Hd.WoNo,
Dt.ProductCode, Dt.Completed, Ti.TallyInNo, Ti.PrincipalCode, dt.qty
FROM WmsPutawayHed Hd
INNER JOIN WmsPutawayDet Dt
ON Dt.CompanyCode = Hd.CompanyCode
And Dt.BranchCode = Hd.BranchCode
And Dt.WoNo = Hd.WoNo
And Dt.Completed = 1
INNER JOIN WmsTallyInHed Ti
ON Ti.CompanyCode = Hd.CompanyCode
And Ti.BranchCode = Hd.BranchCode
And Ti.TallyInNo = Hd.TallyInNo
WHERE Hd.CompanyCode = 'HQ'
And Hd.BranchCode = 'HQ'
) PutHold
ON PutHold.CompanyCode = Trx.CompanyCode
And PutHold.BranchCode = Trx.BranchCode
And PutHold.WONo = Trx.TxnNo
And PutHold.ProductCode = Trx.ProductCode
And PutHold.TallyInNo = Trx.TallyInNo
WHERE
Trx.CompanyCode='HQ'
And Trx.BranchCode='HQ'
And (CASE WHEN (SUBSTRING(Trx.TXNNo,1,3) <> 'OP-') THEN
IsNull(PutHold.Completed,0) ELSE 1 END) = 1
GROUP BY Trx.PrincipalCode, Trx.ProductCode
HAVING SUM(Trx.BaseQuantity * Trx.PhysicalFlag) > 0
Regards
Roji. P. Thomas
http://toponewithties.blogspot.com
"jcvoon" <jcvoon@.maximas.com.my> wrote in message
news:1136948778.631640.17620@.g49g2000cwa.googlegroups.com...
> Hi:
> I've a problem with the following querys, These two query is suppose to
> produce a same result
> but it is not, i don't know why.
> The first query is using view, it procuce a correct result (2 rows),
> the second query is using inline view (the inline view defination is
> exactly the same as the view) but the result is wrong (4 rows).
>
> inline view twist which is not correct.
> Please help.
> JCVoon
>
>
> -- Join with view
> SELECT Trx.PrincipalCode, Trx.ProductCode, SUM(Trx.BaseQuantity *
> Trx.PhysicalFlag) AS BaseQty
> FROM WmsStockLedger Trx
> LEFT JOIN
> (
> SELECT * From view_PutHold
> WHERE CompanyCode='HQ' And BranchCode = 'HQ'
> ) PutHold
> ON PutHold.CompanyCode = Trx.CompanyCode
> And PutHold.BranchCode = Trx.BranchCode
> And PutHold.WONo = Trx.TxnNo
> And PutHold.ProductCode = Trx.ProductCode
> And PutHold.TallyInNo = Trx.TallyInNo
> WHERE
> Trx.CompanyCode='HQ'
> And Trx.BranchCode='HQ'
> And (CASE WHEN (LEFT(Trx.TXNNo,3) <> 'OP-') THEN
> IsNull(PutHold.Completed,0) ELSE 1 END) = 1
> GROUP BY Trx.PrincipalCode, Trx.ProductCode
> HAVING SUM(Trx.BaseQuantity * Trx.PhysicalFlag) > 0
> -- Join with inline view
> SELECT Trx.PrincipalCode, Trx.ProductCode, SUM(Trx.BaseQuantity *
> Trx.PhysicalFlag) AS BaseQty
> FROM WmsStockLedger Trx
> LEFT JOIN
> (
> Select Distinct Hd.CompanyCode, Hd.BranchCode, Hd.WoNo,
> Dt.ProductCode, Dt.Completed, Ti.TallyInNo, Ti.PrincipalCode, dt.qty
> FROM WmsPutawayHed Hd
> INNER JOIN WmsPutawayDet Dt
> ON Dt.CompanyCode = Hd.CompanyCode
> And Dt.BranchCode = Hd.BranchCode
> And Dt.WoNo = Hd.WoNo
> And Dt.Completed = 1
> INNER JOIN WmsTallyInHed Ti
> ON Ti.CompanyCode = Hd.CompanyCode
> And Ti.BranchCode = Hd.BranchCode
> And Ti.TallyInNo = Hd.TallyInNo
> WHERE Hd.CompanyCode = 'HQ'
> And Hd.BranchCode = 'HQ'
> ) PutHold
> ON PutHold.CompanyCode = Trx.CompanyCode
> And PutHold.BranchCode = Trx.BranchCode
> And PutHold.WONo = Trx.TxnNo
> And PutHold.ProductCode = Trx.ProductCode
> And PutHold.TallyInNo = Trx.TallyInNo
> WHERE
> Trx.CompanyCode='HQ'
> And Trx.BranchCode='HQ'
> And (CASE WHEN (LEFT(Trx.TXNNo,3) <> 'OP-') THEN
> IsNull(PutHold.Completed,0) ELSE 1 END) = 1
> GROUP BY Trx.PrincipalCode, Trx.ProductCode
> HAVING SUM(Trx.BaseQuantity * Trx.PhysicalFlag) > 0
>
> --Here is the DDL
> CREATE TABLE [dbo].[WmsPutawayDet] (
> [CompanyCode] [varchar] (2) NOT NULL ,
> [BranchCode] [varchar] (2) NOT NULL ,
> [WoNo] [varchar] (10) NOT NULL ,
> [ProductCode] [varchar] (10) NOT NULL ,
> [LocationCode] [varchar] (10) NOT NULL ,
> [Qty] [numeric](18, 0) NOT NULL ,
> [Completed] [bit] NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[WmsPutawayHed] (
> [CompanyCode] [varchar] (2) NOT NULL ,
> [BranchCode] [varchar] (2) NOT NULL ,
> [WoNo] [varchar] (10) NOT NULL ,
> [TallyInNo] [varchar] (10) NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[WmsTallyInHed] (
> [CompanyCode] [varchar] (2) NOT NULL ,
> [BranchCode] [varchar] (2) NOT NULL ,
> [TallyInNo] [varchar] (10) NOT NULL ,
> [PrincipalCode] [varchar] (10) NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[wmsStockLedger] (
> [CompanyCode] [varchar] (2) NOT NULL ,
> [BranchCode] [varchar] (2) NOT NULL ,
> [ProductCode] [varchar] (10) NOT NULL ,
> [LocationCode] [varchar] (10) NOT NULL ,
> [TallyInNo] [varchar] (10) NOT NULL ,
> [PrincipalCode] [varchar] (10) NOT NULL ,
> [TxnNo] [varchar] (10) NOT NULL ,
> [BaseQuantity] [numeric](18, 0) NOT NULL ,
> [PhysicalFlag] [int] NOT NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[WmsPutawayDet] ADD
> CONSTRAINT [PK_WmsPutawayDet] PRIMARY KEY CLUSTERED
> (
> [CompanyCode],
> [BranchCode],
> [WoNo],
> [ProductCode],
> [LocationCode]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[WmsPutawayHed] ADD
> CONSTRAINT [PK_WmsPutawayHed] PRIMARY KEY CLUSTERED
> (
> [CompanyCode],
> [BranchCode],
> [WoNo]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[WmsTallyInHed] ADD
> CONSTRAINT [PK_WmsTallyInHed] PRIMARY KEY CLUSTERED
> (
> [CompanyCode],
> [BranchCode],
> [TallyInNo]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[wmsStockLedger] ADD
> CONSTRAINT [PK_wmsStockLedger] PRIMARY KEY CLUSTERED
> (
> [CompanyCode],
> [BranchCode],
> [ProductCode],
> [LocationCode],
> [TallyInNo],
> [PrincipalCode],
> [TxnNo]
> ) ON [PRIMARY]
> GO
> create view view_PutHold as
> Select Distinct Hd.CompanyCode, Hd.BranchCode, Hd.WoNo,
> Dt.ProductCode, Dt.Completed, Ti.TallyInNo, Ti.PrincipalCode, Dt.qty
> FROM WmsPutawayHed Hd
> INNER JOIN WmsPutawayDet Dt
> ON Dt.CompanyCode = Hd.CompanyCode
> And Dt.BranchCode = Hd.BranchCode
> And Dt.WoNo = Hd.WoNo
> And Dt.Completed = 1
> INNER JOIN WmsTallyInHed Ti
> ON Ti.CompanyCode = Hd.CompanyCode
> And Ti.BranchCode = Hd.BranchCode
> And Ti.TallyInNo = Hd.TallyInNo
> GO
> INSERT INTO [WmsPutawayHed] VALUES('HQ','HQ','WO001','OP-001')
> INSERT INTO [WmsPutawayHed] VALUES('HQ','HQ','WO002','OP-002')
> INSERT INTO [WmsPutawayDet] VALUES('HQ','HQ','WO001','P1','A',5,1)
> INSERT INTO [WmsPutawayDet] VALUES('HQ','HQ','WO001','P1','B',5,0)
> INSERT INTO [WmsPutawayDet] VALUES('HQ','HQ','WO002','P2','A',10,1)
> INSERT INTO [WmsPutawayDet] VALUES('HQ','HQ','WO002','P2','B',10,1)
> INSERT INTO [WmsTallyInHed] VALUES('HQ','HQ','OP-001','P001')
> INSERT INTO [WmsTallyInHed] VALUES('HQ','HQ','OP-002','P001')
> INSERT INTO [WmsTallyInHed] VALUES('HQ','HQ','TI001','P001')
> INSERT INTO [WmsTallyInHed] VALUES('HQ','HQ','TI002','P001')
> INSERT INTO [WmsStockLedger]
> VALUES('HQ','HQ','P1','A','OP-001','P001','WO001',5,1)
> INSERT INTO [WmsStockLedger]
> VALUES('HQ','HQ','P1','B','OP-001','P001','WO001',5,1)
> INSERT INTO [WmsStockLedger]
> VALUES('HQ','HQ','P1','HOLD','OP-001','P001','OP-001',10,1)
> INSERT INTO [WmsStockLedger]
> VALUES('HQ','HQ','P1','HOLD','OP-001','P001','WO001',10,-1)
> INSERT INTO [WmsStockLedger]
> VALUES('HQ','HQ','P2','A','OP-002','P001','WO002',10,1)
> INSERT INTO [WmsStockLedger]
> VALUES('HQ','HQ','P2','B','OP-002','P001','WO002',10,1)
> INSERT INTO [WmsStockLedger]
> VALUES('HQ','HQ','P2','HOLD','OP-002','P001','OP-002',20,1)
> INSERT INTO [WmsStockLedger]
> VALUES('HQ','HQ','P2','HOLD','OP-002','P001','WO002',20,-1)
>|||Roji. P. Thomas:
Thank you so much.
I've tested my original query on SQL2000PE with SP4, the problem still
exist.
With your work around my query working fine now.
Just wonder how do u know change LEFT(Trx.TXNNo,3) with
SUBSTRING(Trx.TXNNo,1,3) will solved the problem ?
Thanks
JCVoon