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).
>>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)
>|||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...
>> 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)
>|||And (CASE WHEN (LEFT(Trx.TXNNo,3) <> 'OP-') THEN
IsNull(PutHold.Completed,0) ELSE 1 END) = 1
Could be re-written
AND ( LEFT(Trx.TXNNo,3) = 'OP-'
OR PutHold.Completed = 1 )
John
"Roji. P. Thomas" wrote:
> 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...
> >> 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)
> >>
> >
> >
>
>|||Right. But that doesn't seems to solve the original problem.
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:5440A06F-56F1-4A6A-8817-7EAF7A5E8413@.microsoft.com...
> And (CASE WHEN (LEFT(Trx.TXNNo,3) <> 'OP-') THEN
> IsNull(PutHold.Completed,0) ELSE 1 END) = 1
> Could be re-written
> AND ( LEFT(Trx.TXNNo,3) = 'OP-'
> OR PutHold.Completed = 1 )
> John
> "Roji. P. Thomas" wrote:
>> 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...
>> >> 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)
>> >>
>> >
>> >
>>|||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.
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...
>>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).
>>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)
>>
>|||John
Thanks for the reply.
I'll rewrite my original query to the way u shown me.
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...
>> 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...
>> 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)
>>
>>
>|||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).
>>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)
>|||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...
>> 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)
>|||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).
>>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)
>|||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|||JCVoon,
I just played around with it and found that neither RIGHT or SUBSTRING
function causes this problem.
Only the usage of LEFT function alongwith an OUTER JOIN, DERIVED TABLE,
GROUP BY , HAVING and an AGGREGATE function causes the query optimizer to go
for an incorrect plan that causes a CROSS JOIN.
--
Regards
Roji. P. Thomas
http://toponewithties.blogspot.com
"jcvoon" <jcvoon@.maximas.com.my> wrote in message
news:1137040431.065223.265280@.g43g2000cwa.googlegroups.com...
> 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
>|||Hi,
I believe the problem was not caused by CROSS JOIN. We can simulate the
CROSS JOIN situation by converting the LEFT JOIN to CROSS JOIN right?
The result will be different, the GROUP BY will still work.
So, I believe it was actually fix in SP2 as stated in the article.
I believe the problem is much similar to the situation in the below
article:
http://www.support.microsoft.com/kb/884864
The article above was fixed in SP3 or 3a. Both problems are the same I
believe whereby both are caused by "Table / Lazy Spool" selected by the
query optimizer, but the problem JCVoon posted was slightly different
in which where the table spool happened for the whole query, and
returns duplicates records, and finally INNER JOIN without condition to
the main result... or something like that, you can see it in the
"Execution Plan".
I notice that when I put the whole query as a sub-query, the optimizer
does not use "Table / Lazy Spool"?
E.g.
Use Pubs
GO
Select * FROM(
SELECT T.Title, T.pub_id, T.type, SUM(IsNull(T.price, 0) * 1) AS
BasePrice
FROM Titles T
LEFT JOIN
(Select pub_id FROM publishers) X
ON X.pub_id = T.pub_id
WHERE LEFT(T.title,3) = 'The'
GROUP BY T.Title, T.pub_id, T.type
HAVING SUM(IsNull(T.price, 0) * 1) > 0
) X
I also notice a workaround the problem to prevent the "Table / Lazy
Spool" from happening, which in a way, can be said as FORCING the query
optimizer to use a different plan.
E.g.
Use Pubs
GO
SELECT T.Title, T.pub_id, T.type, SUM(IsNull(T.price, 0) * 1) AS
BasePrice
FROM Titles T
LEFT JOIN
(Select pub_id FROM publishers) X
ON X.pub_id = T.pub_id
LEFT JOIN
(Select TOP 0 pub_id FROM titleview) M
ON 1=1
WHERE LEFT(T.title,3) = 'The'
GROUP BY T.Title, T.pub_id, T.type
HAVING SUM(IsNull(T.price, 0) * 1) > 0
Notice I added this line:
LEFT JOIN
(Select TOP 0 pub_id FROM titleview) M
ON 1=1
I notice that when we use a VIEW as the sub-query/derived table, the
query optimizer does not use the "Table / Lazy Spool", thus I included
this line so it will choose so called the best plan.
Anyway, is this a new bug dicovered in SQL Server 2000? Similar to Kb
884864? But I think there won't be any Service Pack release for SQL
Server 2000 anymore right?
Roji. P. Thomas wrote:
> JCVoon,
> I just played around with it and found that neither RIGHT or SUBSTRING
> function causes this problem.
> Only the usage of LEFT function alongwith an OUTER JOIN, DERIVED TABLE,
> GROUP BY , HAVING and an AGGREGATE function causes the query optimizer to go
> for an incorrect plan that causes a CROSS JOIN.
> --
> Regards
> Roji. P. Thomas
> http://toponewithties.blogspot.com
> "jcvoon" <jcvoon@.maximas.com.my> wrote in message
> news:1137040431.065223.265280@.g43g2000cwa.googlegroups.com...
> > 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
> >|||Andy,
See my comments inline.
> I believe the problem was not caused by CROSS JOIN. We can simulate the
> CROSS JOIN situation by converting the LEFT JOIN to CROSS JOIN right?
> The result will be different, the GROUP BY will still work.
I am almost sure that the problem caused because of the incorrect CROSS
JOIN. If you apply
a CROSS JOIN with one set without the HAVING clause and one set with the
HAVING clause,
you will get the exact same results and execution plan. (See below)
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
SELECT T.pub_id, T.type,X.BasePrice FROM
(
SELECT T.pub_id, T.type, SUM(T.price * 1) AS BasePrice
FROM Titles T --CROSS JOIN Titles R
LEFT JOIN
(Select NULL) X (pub_id)
ON X.pub_id = T.pub_id
WHERE SUBSTRING(T.Title,1,3)= 'The'
--AND LEFT(R.Title,3)= 'The'
GROUP BY T.pub_id, T.type
HAVING SUM(T.price * 1) > 0
)T CROSS JOIN (
SELECT T.pub_id, T.type, SUM(T.price * 1) AS BasePrice
FROM Titles T --CROSS JOIN Titles R
LEFT JOIN
(Select NULL) X (pub_id)
ON X.pub_id = T.pub_id
WHERE SUBSTRING(T.Title,1,3)= 'The'
--AND LEFT(R.Title,3)= 'The'
GROUP BY T.pub_id, T.type
--HAVING SUM(T.price * 1) > 0
)X
--Execution Plan
[First Query]
(1 row(s) affected)
StmtText
|--Nested Loops(Inner Join)
|--Compute Scalar(DEFINE:([Expr1005]=If ([Expr1018]=0) then NULL else
[Expr1019]))
| |--Stream Aggregate(GROUP BY:([T].[type], [T].[pub_id])
DEFINE:([Expr1018]=COUNT_BIG([T].[price]*1.00),
[Expr1019]=SUM([T].[price]*1.00)))
| |--Sort(ORDER BY:([T].[type] ASC, [T].[pub_id] ASC))
| |--Clustered Index
Scan(OBJECT:([pubs].[dbo].[titles].[UPKCL_titleidind] AS [T]),
WHERE:(substring([T].[title], 1, 3)='The'))
|--Table Spool
|--Filter(WHERE:([Expr1002]>0.00))
|--Compute Scalar(DEFINE:([Expr1002]=If ([Expr1020]=0) then
NULL else [Expr1021]))
|--Stream Aggregate(GROUP BY:([T].[type],
[T].[pub_id]) DEFINE:([Expr1020]=COUNT_BIG([T].[price]*1.00),
[Expr1021]=SUM([T].[price]*1.00)))
|--Sort(ORDER BY:([T].[type] ASC, [T].[pub_id]
ASC))
|--Clustered Index
Scan(OBJECT:([pubs].[dbo].[titles].[UPKCL_titleidind] AS [T]),
WHERE:(substring([T].[title], 1, 3)='The'))
(11 row(s) affected)
[Second Query]
(1 row(s) affected)
StmtText
|--Nested Loops(Inner Join)
|--Compute Scalar(DEFINE:([Expr1005]=If ([Expr1018]=0) then NULL else
[Expr1019]))
| |--Stream Aggregate(GROUP BY:([T].[type], [T].[pub_id])
DEFINE:([Expr1018]=COUNT_BIG([T].[price]*1.00),
[Expr1019]=SUM([T].[price]*1.00)))
| |--Sort(ORDER BY:([T].[type] ASC, [T].[pub_id] ASC))
| |--Clustered Index
Scan(OBJECT:([pubs].[dbo].[titles].[UPKCL_titleidind] AS [T]),
WHERE:(substring([T].[title], 1, 3)='The'))
|--Table Spool
|--Filter(WHERE:([Expr1002]>0.00))
|--Compute Scalar(DEFINE:([Expr1002]=If ([Expr1020]=0) then
NULL else [Expr1021]))
|--Stream Aggregate(GROUP BY:([T].[type],
[T].[pub_id]) DEFINE:([Expr1020]=COUNT_BIG([T].[price]*1.00),
[Expr1021]=SUM([T].[price]*1.00)))
|--Sort(ORDER BY:([T].[type] ASC, [T].[pub_id]
ASC))
|--Clustered Index
Scan(OBJECT:([pubs].[dbo].[titles].[UPKCL_titleidind] AS [T]),
WHERE:(substring([T].[title], 1, 3)='The'))
(11 row(s) affected)
>I believe the problem is much similar to the situation in the below
>article:
>http://www.support.microsoft.com/kb/884864
The title of the KB article says, "You may receive incorrect results when
you run a query in SQL Server 2000".
Well, I have nothing to say about that. But the article describes the cause
of the problem as "the execution plan that
SQL Server 2000 chooses uses a multi-parent spool that has the primary and
the secondary spools on the inner side of a nested loop join."
But for the problem at hand, we have only one TableSpool/Lazy Spool on the
inner side of the join.
> I notice that when I put the whole query as a sub-query, the optimizer
> does not use "Table / Lazy Spool"?
Yes. That was one of the workaround suggested before.
>Anyway, is this a new bug discovered in SQL Server 2000?
I think So.
> But I think there won't be any Service Pack release for SQL
>Server 2000 anymore right?
I don't know. But I already reported this in a private NG.
--
Regards
Roji. P. Thomas
http://toponewithties.blogspot.com
"Andy214" <Andy214@.gmail.com> wrote in message
news:1137137578.286943.77370@.z14g2000cwz.googlegroups.com...
> Hi,
> I believe the problem was not caused by CROSS JOIN. We can simulate the
> CROSS JOIN situation by converting the LEFT JOIN to CROSS JOIN right?
> The result will be different, the GROUP BY will still work.
> So, I believe it was actually fix in SP2 as stated in the article.
> I believe the problem is much similar to the situation in the below
> article:
> http://www.support.microsoft.com/kb/884864
> The article above was fixed in SP3 or 3a. Both problems are the same I
> believe whereby both are caused by "Table / Lazy Spool" selected by the
> query optimizer, but the problem JCVoon posted was slightly different
> in which where the table spool happened for the whole query, and
> returns duplicates records, and finally INNER JOIN without condition to
> the main result... or something like that, you can see it in the
> "Execution Plan".
> I notice that when I put the whole query as a sub-query, the optimizer
> does not use "Table / Lazy Spool"?
> E.g.
> Use Pubs
> GO
> Select * FROM(
> SELECT T.Title, T.pub_id, T.type, SUM(IsNull(T.price, 0) * 1) AS
> BasePrice
> FROM Titles T
> LEFT JOIN
> (Select pub_id FROM publishers) X
> ON X.pub_id = T.pub_id
> WHERE LEFT(T.title,3) = 'The'
> GROUP BY T.Title, T.pub_id, T.type
> HAVING SUM(IsNull(T.price, 0) * 1) > 0
> ) X
> I also notice a workaround the problem to prevent the "Table / Lazy
> Spool" from happening, which in a way, can be said as FORCING the query
> optimizer to use a different plan.
> E.g.
> Use Pubs
> GO
> SELECT T.Title, T.pub_id, T.type, SUM(IsNull(T.price, 0) * 1) AS
> BasePrice
> FROM Titles T
> LEFT JOIN
> (Select pub_id FROM publishers) X
> ON X.pub_id = T.pub_id
> LEFT JOIN
> (Select TOP 0 pub_id FROM titleview) M
> ON 1=1
> WHERE LEFT(T.title,3) = 'The'
> GROUP BY T.Title, T.pub_id, T.type
> HAVING SUM(IsNull(T.price, 0) * 1) > 0
> Notice I added this line:
> LEFT JOIN
> (Select TOP 0 pub_id FROM titleview) M
> ON 1=1
> I notice that when we use a VIEW as the sub-query/derived table, the
> query optimizer does not use the "Table / Lazy Spool", thus I included
> this line so it will choose so called the best plan.
> Anyway, is this a new bug dicovered in SQL Server 2000? Similar to Kb
> 884864? But I think there won't be any Service Pack release for SQL
> Server 2000 anymore right?
> Roji. P. Thomas wrote:
>> JCVoon,
>> I just played around with it and found that neither RIGHT or SUBSTRING
>> function causes this problem.
>> Only the usage of LEFT function alongwith an OUTER JOIN, DERIVED TABLE,
>> GROUP BY , HAVING and an AGGREGATE function causes the query optimizer to
>> go
>> for an incorrect plan that causes a CROSS JOIN.
>> --
>> Regards
>> Roji. P. Thomas
>> http://toponewithties.blogspot.com
>> "jcvoon" <jcvoon@.maximas.com.my> wrote in message
>> news:1137040431.065223.265280@.g43g2000cwa.googlegroups.com...
>> > 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
>> >
>|||Roji. P. Thomas,
For your CROSS JOIN, you can replace it with LEFT JOIN (giving criteria
ON 1=1), it will produce the same result...
>The title of the KB article says, "You may receive incorrect results when
>you run a query in SQL Server 2000".
>Well, I have nothing to say about that. But the article describes the cause
>of the problem as "the execution plan that
>SQL Server 2000 chooses uses a multi-parent spool that has the primary and
>the secondary spools on the inner side of a nested loop join."
>But for the problem at hand, we have only one TableSpool/Lazy Spool on the
>inner side of the join.
Well, that's what I mean by similar but slightly different. Um... what
I mean was actually the problem caused by "Table / Lazy Spool".
Somehow, somewhere, the query optimizer chooses the "Table / Lazy
Spool"...
Well, like another example I posted, where I used another JOIN to
forced the query optimizer to use a different plan by:
LEFT JOIN
(Select TOP 0 title from titleview)
ON 1=1
It changes it query plan...
>Yes. That was one of the workaround suggested before.
But how come when put under sub-query, the optimizer uses a different
plan? Hm...
One more thing, I notice that in the execution plan, the LEFT()
function was actually interprested as SUBSTRING()... So... how come
when used LEFT and SUBSTRING() have differences for the above case?
Hm...|||Andy,
> For your CROSS JOIN, you can replace it with LEFT JOIN (giving criteria
> ON 1=1), it will produce the same result...
An INNER or OUTER JOIN based on a dummy condition like 1=1 is equivalent to
CROSS JOIN,
because every row in the left set matches to every row in the right set.
All three of the following query will produce the same result.
SELECT *
FROM
(SELECT 1 AS col1
UNION ALL SELECT 2) A
INNER JOIN
(SELECT 'A' as col2
UNION ALL SELECT 'B') B
ON 1 =1
SELECT *
FROM
(SELECT 1 AS col1
UNION ALL SELECT 2) A
LEFT JOIN
(SELECT 'A' as col2
UNION ALL SELECT 'B') B
ON 1 =1
SELECT *
FROM
(SELECT 1 AS col1
UNION ALL SELECT 2) A
CROSS JOIN
(SELECT 'A' as col2
UNION ALL SELECT 'B') B
> I mean was actually the problem caused by "Table / Lazy Spool".
> Somehow, somewhere, the query optimizer chooses the "Table / Lazy
> Spool"...
That's correct. Once we get rid of the Table Spool, the query return
correct results.
> One more thing, I notice that in the execution plan, the LEFT()
> function was actually interpreted as SUBSTRING()... So... how come
> when used LEFT and SUBSTRING() have differences for the above case?
You are right. But that happens after the optimizer devices the wrong plan.
--
Regards
Roji. P. Thomas
http://toponewithties.blogspot.com|||Raji. P. Thomas,
>That's correct. Once we get rid of the Table Spool, the query return
correct results.
Is there a way to prevent the query optimizer to use the "Table / Lazy
Spool"? Instead of forcing it with LEFT JOIN To A Derived View? I mean
as an option or something. If it can do it by force, it should be able
to do without the derived view. Or perhaps need a fix from MS?
Actually, do you have any idea why is there the need for "Table / Lazy
Spool" when it produces wrong results?