Hi:
I've a problem with the following querys, These two query is suppose to
produce a same result
but it is not, i don't know why.
The first query is using view, it procuce a correct result (2 rows),
the second query is using inline view (the inline view defination is
exactly the same as the view) but the result is wrong (4 rows).
>From the execution plan, the second query perform the join with the
inline view twist which is not correct.
Please help.
JCVoon
-- Join with view
SELECT Trx.PrincipalCode, Trx.ProductCode, SUM(Trx.BaseQuantity *
Trx.PhysicalFlag) AS BaseQty
FROM WmsStockLedger Trx
LEFT JOIN
(
SELECT * From view_PutHold
WHERE CompanyCode='HQ' And BranchCode = 'HQ'
) PutHold
ON PutHold.CompanyCode = Trx.CompanyCode
And PutHold.BranchCode = Trx.BranchCode
And PutHold.WONo = Trx.TxnNo
And PutHold.ProductCode = Trx.ProductCode
And PutHold.TallyInNo = Trx.TallyInNo
WHERE
Trx.CompanyCode='HQ'
And Trx.BranchCode='HQ'
And (CASE WHEN (LEFT(Trx.TXNNo,3) <> 'OP-') THEN
IsNull(PutHold.Completed,0) ELSE 1 END) = 1
GROUP BY Trx.PrincipalCode, Trx.ProductCode
HAVING SUM(Trx.BaseQuantity * Trx.PhysicalFlag) > 0
-- Join with inline view
SELECT Trx.PrincipalCode, Trx.ProductCode, SUM(Trx.BaseQuantity *
Trx.PhysicalFlag) AS BaseQty
FROM WmsStockLedger Trx
LEFT JOIN
(
Select Distinct Hd.CompanyCode, Hd.BranchCode, Hd.WoNo,
Dt.ProductCode, Dt.Completed, Ti.TallyInNo, Ti.PrincipalCode, dt.qty
FROM WmsPutawayHed Hd
INNER JOIN WmsPutawayDet Dt
ON Dt.CompanyCode = Hd.CompanyCode
And Dt.BranchCode = Hd.BranchCode
And Dt.WoNo = Hd.WoNo
And Dt.Completed = 1
INNER JOIN WmsTallyInHed Ti
ON Ti.CompanyCode = Hd.CompanyCode
And Ti.BranchCode = Hd.BranchCode
And Ti.TallyInNo = Hd.TallyInNo
WHERE Hd.CompanyCode = 'HQ'
And Hd.BranchCode = 'HQ'
) PutHold
ON PutHold.CompanyCode = Trx.CompanyCode
And PutHold.BranchCode = Trx.BranchCode
And PutHold.WONo = Trx.TxnNo
And PutHold.ProductCode = Trx.ProductCode
And PutHold.TallyInNo = Trx.TallyInNo
WHERE
Trx.CompanyCode='HQ'
And Trx.BranchCode='HQ'
And (CASE WHEN (LEFT(Trx.TXNNo,3) <> 'OP-') THEN
IsNull(PutHold.Completed,0) ELSE 1 END) = 1
GROUP BY Trx.PrincipalCode, Trx.ProductCode
HAVING SUM(Trx.BaseQuantity * Trx.PhysicalFlag) > 0
--Here is the DDL
CREATE TABLE [dbo].[WmsPutawayDet] (
[CompanyCode] [varchar] (2) NOT NULL ,
[BranchCode] [varchar] (2) NOT NULL ,
[WoNo] [varchar] (10) NOT NULL ,
[ProductCode] [varchar] (10) NOT NULL ,
[LocationCode] [varchar] (10) NOT NULL ,
[Qty] [numeric](18, 0) NOT NULL ,
[Completed] [bit] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[WmsPutawayHed] (
[CompanyCode] [varchar] (2) NOT NULL ,
[BranchCode] [varchar] (2) NOT NULL ,
[WoNo] [varchar] (10) NOT NULL ,
[TallyInNo] [varchar] (10) NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[WmsTallyInHed] (
[CompanyCode] [varchar] (2) NOT NULL ,
[BranchCode] [varchar] (2) NOT NULL ,
[TallyInNo] [varchar] (10) NOT NULL ,
[PrincipalCode] [varchar] (10) NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[wmsStockLedger] (
[CompanyCode] [varchar] (2) NOT NULL ,
[BranchCode] [varchar] (2) NOT NULL ,
[ProductCode] [varchar] (10) NOT NULL ,
[LocationCode] [varchar] (10) NOT NULL ,
[TallyInNo] [varchar] (10) NOT NULL ,
[PrincipalCode] [varchar] (10) NOT NULL ,
[TxnNo] [varchar] (10) NOT NULL ,
[BaseQuantity] [numeric](18, 0) NOT NULL ,
[PhysicalFlag] [int] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[WmsPutawayDet] ADD
CONSTRAINT [PK_WmsPutawayDet] PRIMARY KEY CLUSTERED
(
[CompanyCode],
[BranchCode],
[WoNo],
[ProductCode],
[LocationCode]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[WmsPutawayHed] ADD
CONSTRAINT [PK_WmsPutawayHed] PRIMARY KEY CLUSTERED
(
[CompanyCode],
[BranchCode],
[WoNo]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[WmsTallyInHed] ADD
CONSTRAINT [PK_WmsTallyInHed] PRIMARY KEY CLUSTERED
(
[CompanyCode],
[BranchCode],
[TallyInNo]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[wmsStockLedger] ADD
CONSTRAINT [PK_wmsStockLedger] PRIMARY KEY CLUSTERED
(
[CompanyCode],
[BranchCode],
[ProductCode],
[LocationCode],
[TallyInNo],
[PrincipalCode],
[TxnNo]
) ON [PRIMARY]
GO
create view view_PutHold as
Select Distinct Hd.CompanyCode, Hd.BranchCode, Hd.WoNo,
Dt.ProductCode, Dt.Completed, Ti.TallyInNo, Ti.PrincipalCode, Dt.qty
FROM WmsPutawayHed Hd
INNER JOIN WmsPutawayDet Dt
ON Dt.CompanyCode = Hd.CompanyCode
And Dt.BranchCode = Hd.BranchCode
And Dt.WoNo = Hd.WoNo
And Dt.Completed = 1
INNER JOIN WmsTallyInHed Ti
ON Ti.CompanyCode = Hd.CompanyCode
And Ti.BranchCode = Hd.BranchCode
And Ti.TallyInNo = Hd.TallyInNo
GO
INSERT INTO [WmsPutawayHed] VALUES('HQ','HQ','WO001','OP-001')
INSERT INTO [WmsPutawayHed] VALUES('HQ','HQ','WO002','OP-002')
INSERT INTO [WmsPutawayDet] VALUES('HQ','HQ','WO001','P1','A',5,1)
INSERT INTO [WmsPutawayDet] VALUES('HQ','HQ','WO001','P1','B',5,0)
INSERT INTO [WmsPutawayDet] VALUES('HQ','HQ','WO002','P2','A',10,1)
INSERT INTO [WmsPutawayDet] VALUES('HQ','HQ','WO002','P2','B',10,1)
INSERT INTO [WmsTallyInHed] VALUES('HQ','HQ','OP-001','P001')
INSERT INTO [WmsTallyInHed] VALUES('HQ','HQ','OP-002','P001')
INSERT INTO [WmsTallyInHed] VALUES('HQ','HQ','TI001','P001')
INSERT INTO [WmsTallyInHed] VALUES('HQ','HQ','TI002','P001')
INSERT INTO [WmsStockLedger]
VALUES('HQ','HQ','P1','A','OP-001','P001','WO001',5,1)
INSERT INTO [WmsStockLedger]
VALUES('HQ','HQ','P1','B','OP-001','P001','WO001',5,1)
INSERT INTO [WmsStockLedger]
VALUES('HQ','HQ','P1','HOLD','OP-001','P001','OP-001',10,1)
INSERT INTO [WmsStockLedger]
VALUES('HQ','HQ','P1','HOLD','OP-001','P001','WO001',10,-1)
INSERT INTO [WmsStockLedger]
VALUES('HQ','HQ','P2','A','OP-002','P001','WO002',10,1)
INSERT INTO [WmsStockLedger]
VALUES('HQ','HQ','P2','B','OP-002','P001','WO002',10,1)
INSERT INTO [WmsStockLedger]
VALUES('HQ','HQ','P2','HOLD','OP-002','P001','OP-002',20,1)
INSERT INTO [WmsStockLedger]
VALUES('HQ','HQ','P2','HOLD','OP-002','P001','WO002',20,-1)I am looking into this issue.
Looks like a problem with SQL server itself.
The query works as expected in SQL 2005 (returns only 2 rows in both cases)
Roji. P. Thomas
http://toponewithties.blogspot.com
"jcvoon" <jcvoon@.maximas.com.my> wrote in message
news:1136948778.631640.17620@.g49g2000cwa.googlegroups.com...
> Hi:
> I've a problem with the following querys, These two query is suppose to
> produce a same result
> but it is not, i don't know why.
> The first query is using view, it procuce a correct result (2 rows),
> the second query is using inline view (the inline view defination is
> exactly the same as the view) but the result is wrong (4 rows).
>
> inline view twist which is not correct.
> Please help.
> JCVoon
>
>
> -- Join with view
> SELECT Trx.PrincipalCode, Trx.ProductCode, SUM(Trx.BaseQuantity *
> Trx.PhysicalFlag) AS BaseQty
> FROM WmsStockLedger Trx
> LEFT JOIN
> (
> SELECT * From view_PutHold
> WHERE CompanyCode='HQ' And BranchCode = 'HQ'
> ) PutHold
> ON PutHold.CompanyCode = Trx.CompanyCode
> And PutHold.BranchCode = Trx.BranchCode
> And PutHold.WONo = Trx.TxnNo
> And PutHold.ProductCode = Trx.ProductCode
> And PutHold.TallyInNo = Trx.TallyInNo
> WHERE
> Trx.CompanyCode='HQ'
> And Trx.BranchCode='HQ'
> And (CASE WHEN (LEFT(Trx.TXNNo,3) <> 'OP-') THEN
> IsNull(PutHold.Completed,0) ELSE 1 END) = 1
> GROUP BY Trx.PrincipalCode, Trx.ProductCode
> HAVING SUM(Trx.BaseQuantity * Trx.PhysicalFlag) > 0
> -- Join with inline view
> SELECT Trx.PrincipalCode, Trx.ProductCode, SUM(Trx.BaseQuantity *
> Trx.PhysicalFlag) AS BaseQty
> FROM WmsStockLedger Trx
> LEFT JOIN
> (
> Select Distinct Hd.CompanyCode, Hd.BranchCode, Hd.WoNo,
> Dt.ProductCode, Dt.Completed, Ti.TallyInNo, Ti.PrincipalCode, dt.qty
> FROM WmsPutawayHed Hd
> INNER JOIN WmsPutawayDet Dt
> ON Dt.CompanyCode = Hd.CompanyCode
> And Dt.BranchCode = Hd.BranchCode
> And Dt.WoNo = Hd.WoNo
> And Dt.Completed = 1
> INNER JOIN WmsTallyInHed Ti
> ON Ti.CompanyCode = Hd.CompanyCode
> And Ti.BranchCode = Hd.BranchCode
> And Ti.TallyInNo = Hd.TallyInNo
> WHERE Hd.CompanyCode = 'HQ'
> And Hd.BranchCode = 'HQ'
> ) PutHold
> ON PutHold.CompanyCode = Trx.CompanyCode
> And PutHold.BranchCode = Trx.BranchCode
> And PutHold.WONo = Trx.TxnNo
> And PutHold.ProductCode = Trx.ProductCode
> And PutHold.TallyInNo = Trx.TallyInNo
> WHERE
> Trx.CompanyCode='HQ'
> And Trx.BranchCode='HQ'
> And (CASE WHEN (LEFT(Trx.TXNNo,3) <> 'OP-') THEN
> IsNull(PutHold.Completed,0) ELSE 1 END) = 1
> GROUP BY Trx.PrincipalCode, Trx.ProductCode
> HAVING SUM(Trx.BaseQuantity * Trx.PhysicalFlag) > 0
>
> --Here is the DDL
> CREATE TABLE [dbo].[WmsPutawayDet] (
> [CompanyCode] [varchar] (2) NOT NULL ,
> [BranchCode] [varchar] (2) NOT NULL ,
> [WoNo] [varchar] (10) NOT NULL ,
> [ProductCode] [varchar] (10) NOT NULL ,
> [LocationCode] [varchar] (10) NOT NULL ,
> [Qty] [numeric](18, 0) NOT NULL ,
> [Completed] [bit] NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[WmsPutawayHed] (
> [CompanyCode] [varchar] (2) NOT NULL ,
> [BranchCode] [varchar] (2) NOT NULL ,
> [WoNo] [varchar] (10) NOT NULL ,
> [TallyInNo] [varchar] (10) NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[WmsTallyInHed] (
> [CompanyCode] [varchar] (2) NOT NULL ,
> [BranchCode] [varchar] (2) NOT NULL ,
> [TallyInNo] [varchar] (10) NOT NULL ,
> [PrincipalCode] [varchar] (10) NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[wmsStockLedger] (
> [CompanyCode] [varchar] (2) NOT NULL ,
> [BranchCode] [varchar] (2) NOT NULL ,
> [ProductCode] [varchar] (10) NOT NULL ,
> [LocationCode] [varchar] (10) NOT NULL ,
> [TallyInNo] [varchar] (10) NOT NULL ,
> [PrincipalCode] [varchar] (10) NOT NULL ,
> [TxnNo] [varchar] (10) NOT NULL ,
> [BaseQuantity] [numeric](18, 0) NOT NULL ,
> [PhysicalFlag] [int] NOT NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[WmsPutawayDet] ADD
> CONSTRAINT [PK_WmsPutawayDet] PRIMARY KEY CLUSTERED
> (
> [CompanyCode],
> [BranchCode],
> [WoNo],
> [ProductCode],
> [LocationCode]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[WmsPutawayHed] ADD
> CONSTRAINT [PK_WmsPutawayHed] PRIMARY KEY CLUSTERED
> (
> [CompanyCode],
> [BranchCode],
> [WoNo]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[WmsTallyInHed] ADD
> CONSTRAINT [PK_WmsTallyInHed] PRIMARY KEY CLUSTERED
> (
> [CompanyCode],
> [BranchCode],
> [TallyInNo]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[wmsStockLedger] ADD
> CONSTRAINT [PK_wmsStockLedger] PRIMARY KEY CLUSTERED
> (
> [CompanyCode],
> [BranchCode],
> [ProductCode],
> [LocationCode],
> [TallyInNo],
> [PrincipalCode],
> [TxnNo]
> ) ON [PRIMARY]
> GO
> create view view_PutHold as
> Select Distinct Hd.CompanyCode, Hd.BranchCode, Hd.WoNo,
> Dt.ProductCode, Dt.Completed, Ti.TallyInNo, Ti.PrincipalCode, Dt.qty
> FROM WmsPutawayHed Hd
> INNER JOIN WmsPutawayDet Dt
> ON Dt.CompanyCode = Hd.CompanyCode
> And Dt.BranchCode = Hd.BranchCode
> And Dt.WoNo = Hd.WoNo
> And Dt.Completed = 1
> INNER JOIN WmsTallyInHed Ti
> ON Ti.CompanyCode = Hd.CompanyCode
> And Ti.BranchCode = Hd.BranchCode
> And Ti.TallyInNo = Hd.TallyInNo
> GO
> INSERT INTO [WmsPutawayHed] VALUES('HQ','HQ','WO001','OP-001')
> INSERT INTO [WmsPutawayHed] VALUES('HQ','HQ','WO002','OP-002')
> INSERT INTO [WmsPutawayDet] VALUES('HQ','HQ','WO001','P1','A',5,1)
> INSERT INTO [WmsPutawayDet] VALUES('HQ','HQ','WO001','P1','B',5,0)
> INSERT INTO [WmsPutawayDet] VALUES('HQ','HQ','WO002','P2','A',10,1)
> INSERT INTO [WmsPutawayDet] VALUES('HQ','HQ','WO002','P2','B',10,1)
> INSERT INTO [WmsTallyInHed] VALUES('HQ','HQ','OP-001','P001')
> INSERT INTO [WmsTallyInHed] VALUES('HQ','HQ','OP-002','P001')
> INSERT INTO [WmsTallyInHed] VALUES('HQ','HQ','TI001','P001')
> INSERT INTO [WmsTallyInHed] VALUES('HQ','HQ','TI002','P001')
> INSERT INTO [WmsStockLedger]
> VALUES('HQ','HQ','P1','A','OP-001','P001','WO001',5,1)
> INSERT INTO [WmsStockLedger]
> VALUES('HQ','HQ','P1','B','OP-001','P001','WO001',5,1)
> INSERT INTO [WmsStockLedger]
> VALUES('HQ','HQ','P1','HOLD','OP-001','P001','OP-001',10,1)
> INSERT INTO [WmsStockLedger]
> VALUES('HQ','HQ','P1','HOLD','OP-001','P001','WO001',10,-1)
> INSERT INTO [WmsStockLedger]
> VALUES('HQ','HQ','P2','A','OP-002','P001','WO002',10,1)
> INSERT INTO [WmsStockLedger]
> VALUES('HQ','HQ','P2','B','OP-002','P001','WO002',10,1)
> INSERT INTO [WmsStockLedger]
> VALUES('HQ','HQ','P2','HOLD','OP-002','P001','OP-002',20,1)
> INSERT INTO [WmsStockLedger]
> VALUES('HQ','HQ','P2','HOLD','OP-002','P001','WO002',20,-1)
>|||The problem appears to be in the section
And (CASE WHEN (LEFT(Trx.TXNNo,3) <> 'OP-') THEN
IsNull(PutHold.Completed,0) ELSE 1 END) = 1
If you just comment that and run the query, the result is correct.
Also if you comment the
SUM(Trx.BaseQuantity * Trx.PhysicalFlag)
line, the query gives the correct result.
I am still not sure whether its a known bug. I will update you once I have
more info.
BTW thankls for posting the DDL.
SELECT Trx.PrincipalCode, Trx.ProductCode, SUM(Trx.BaseQuantity *
Trx.PhysicalFlag)
FROM WmsStockLedger Trx
LEFT JOIN
(Select Distinct Hd.CompanyCode, Hd.BranchCode, Hd.WoNo,
Dt.ProductCode, Dt.Completed, Ti.TallyInNo, Ti.PrincipalCode, dt.qty
FROM WmsPutawayHed Hd
INNER JOIN WmsPutawayDet Dt
ON Dt.CompanyCode = Hd.CompanyCode
And Dt.BranchCode = Hd.BranchCode
And Dt.WoNo = Hd.WoNo
And Dt.Completed = 1
INNER JOIN WmsTallyInHed Ti
ON Ti.CompanyCode = Hd.CompanyCode
And Ti.BranchCode = Hd.BranchCode
And Ti.TallyInNo = Hd.TallyInNo
WHERE Hd.CompanyCode = 'HQ'
And Hd.BranchCode = 'HQ') PutHold
ON PutHold.CompanyCode = Trx.CompanyCode
And PutHold.BranchCode = Trx.BranchCode
And PutHold.WONo = Trx.TxnNo
And PutHold.ProductCode = Trx.ProductCode
And PutHold.TallyInNo = Trx.TallyInNo
WHERE
Trx.CompanyCode='HQ'
And Trx.BranchCode='HQ'
--And (CASE WHEN (LEFT(Trx.TXNNo,3) <> 'OP-') THEN
--IsNull(PutHold.Completed,0) ELSE 1 END) = 1
GROUP BY Trx.PrincipalCode, Trx.ProductCode
HAVING SUM(Trx.BaseQuantity * Trx.PhysicalFlag) > 0
Roji. P. Thomas
http://toponewithties.blogspot.com
"Roji. P. Thomas" <thomasroji@.gmail.com> wrote in message
news:OJsl8IpFGHA.2320@.TK2MSFTNGP11.phx.gbl...
>I am looking into this issue.
> Looks like a problem with SQL server itself.
> The query works as expected in SQL 2005 (returns only 2 rows in both
> cases)
> --
> Roji. P. Thomas
> http://toponewithties.blogspot.com
>
> "jcvoon" <jcvoon@.maximas.com.my> wrote in message
> news:1136948778.631640.17620@.g49g2000cwa.googlegroups.com...
>|||I observed that commenting the line
will solve the problem.
So here is a workaround, other than using the view.
SELECT PrincipalCode, ProductCode, BaseQty
FROM
(
SELECT Trx.PrincipalCode, Trx.ProductCode, SUM(Trx.BaseQuantity *
Trx.PhysicalFlag) AS BaseQty
FROM WmsStockLedger Trx
LEFT JOIN
(Select Distinct Hd.CompanyCode, Hd.BranchCode, Hd.WoNo,
Dt.ProductCode, Dt.Completed, Ti.TallyInNo, Ti.PrincipalCode, dt.qty
FROM WmsPutawayHed Hd
INNER JOIN WmsPutawayDet Dt
ON Dt.CompanyCode = Hd.CompanyCode
And Dt.BranchCode = Hd.BranchCode
And Dt.WoNo = Hd.WoNo
And Dt.Completed = 1
INNER JOIN WmsTallyInHed Ti
ON Ti.CompanyCode = Hd.CompanyCode
And Ti.BranchCode = Hd.BranchCode
And Ti.TallyInNo = Hd.TallyInNo
WHERE Hd.CompanyCode = 'HQ'
And Hd.BranchCode = 'HQ') PutHold
ON PutHold.CompanyCode = Trx.CompanyCode
And PutHold.BranchCode = Trx.BranchCode
And PutHold.WONo = Trx.TxnNo
And PutHold.ProductCode = Trx.ProductCode
And PutHold.TallyInNo = Trx.TallyInNo
WHERE
Trx.CompanyCode='HQ'
And Trx.BranchCode='HQ'
AND (LEFT(Trx.TXNNo,3) = 'OP-'
OR PutHold.Completed = 1)
GROUP BY Trx.PrincipalCode, Trx.ProductCode)T
WHERE BaseQty > 0
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"Roji. P. Thomas" <thomasroji@.gmail.com> wrote in message
news:uciunfpFGHA.1260@.TK2MSFTNGP15.phx.gbl...
> The problem appears to be in the section
> And (CASE WHEN (LEFT(Trx.TXNNo,3) <> 'OP-') THEN
> IsNull(PutHold.Completed,0) ELSE 1 END) = 1
> If you just comment that and run the query, the result is correct.
> Also if you comment the
> SUM(Trx.BaseQuantity * Trx.PhysicalFlag)
> line, the query gives the correct result.
> I am still not sure whether its a known bug. I will update you once I have
> more info.
> BTW thankls for posting the DDL.
>
> SELECT Trx.PrincipalCode, Trx.ProductCode, SUM(Trx.BaseQuantity *
> Trx.PhysicalFlag)
> FROM WmsStockLedger Trx
> LEFT JOIN
> (Select Distinct Hd.CompanyCode, Hd.BranchCode, Hd.WoNo,
> Dt.ProductCode, Dt.Completed, Ti.TallyInNo, Ti.PrincipalCode, dt.qty
> FROM WmsPutawayHed Hd
> INNER JOIN WmsPutawayDet Dt
> ON Dt.CompanyCode = Hd.CompanyCode
> And Dt.BranchCode = Hd.BranchCode
> And Dt.WoNo = Hd.WoNo
> And Dt.Completed = 1
> INNER JOIN WmsTallyInHed Ti
> ON Ti.CompanyCode = Hd.CompanyCode
> And Ti.BranchCode = Hd.BranchCode
> And Ti.TallyInNo = Hd.TallyInNo
> WHERE Hd.CompanyCode = 'HQ'
> And Hd.BranchCode = 'HQ') PutHold
> ON PutHold.CompanyCode = Trx.CompanyCode
> And PutHold.BranchCode = Trx.BranchCode
> And PutHold.WONo = Trx.TxnNo
> And PutHold.ProductCode = Trx.ProductCode
> And PutHold.TallyInNo = Trx.TallyInNo
> WHERE
> Trx.CompanyCode='HQ'
> And Trx.BranchCode='HQ'
> --And (CASE WHEN (LEFT(Trx.TXNNo,3) <> 'OP-') THEN
> --IsNull(PutHold.Completed,0) ELSE 1 END) = 1
> GROUP BY Trx.PrincipalCode, Trx.ProductCode
> HAVING SUM(Trx.BaseQuantity * Trx.PhysicalFlag) > 0
> --
> Roji. P. Thomas
> http://toponewithties.blogspot.com
>
> "Roji. P. Thomas" <thomasroji@.gmail.com> wrote in message
> news:OJsl8IpFGHA.2320@.TK2MSFTNGP11.phx.gbl...
>|||Roji. P. Thomas:
Thanks for your help.
Comment the HAVING clause will also return 2 rows.
Please update me if u found any thing.
Regards
JCVoon|||>I observed that commenting the line
>will solve the problem
Read
I observed that commenting the line
HAVING SUM(Trx.BaseQuantity * Trx.PhysicalFlag) > 0
will solve the problem
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"Roji. P. Thomas" <thomasroji@.gmail.com> wrote in message
news:uqT$jHqFGHA.516@.TK2MSFTNGP15.phx.gbl...
>I observed that commenting the line
> will solve the problem.
> So here is a workaround, other than using the view.
> SELECT PrincipalCode, ProductCode, BaseQty
> FROM
> (
> SELECT Trx.PrincipalCode, Trx.ProductCode, SUM(Trx.BaseQuantity *
> Trx.PhysicalFlag) AS BaseQty
> FROM WmsStockLedger Trx
> LEFT JOIN
> (Select Distinct Hd.CompanyCode, Hd.BranchCode, Hd.WoNo,
> Dt.ProductCode, Dt.Completed, Ti.TallyInNo, Ti.PrincipalCode, dt.qty
> FROM WmsPutawayHed Hd
> INNER JOIN WmsPutawayDet Dt
> ON Dt.CompanyCode = Hd.CompanyCode
> And Dt.BranchCode = Hd.BranchCode
> And Dt.WoNo = Hd.WoNo
> And Dt.Completed = 1
> INNER JOIN WmsTallyInHed Ti
> ON Ti.CompanyCode = Hd.CompanyCode
> And Ti.BranchCode = Hd.BranchCode
> And Ti.TallyInNo = Hd.TallyInNo
> WHERE Hd.CompanyCode = 'HQ'
> And Hd.BranchCode = 'HQ') PutHold
> ON PutHold.CompanyCode = Trx.CompanyCode
> And PutHold.BranchCode = Trx.BranchCode
> And PutHold.WONo = Trx.TxnNo
> And PutHold.ProductCode = Trx.ProductCode
> And PutHold.TallyInNo = Trx.TallyInNo
> WHERE
> Trx.CompanyCode='HQ'
> And Trx.BranchCode='HQ'
> AND (LEFT(Trx.TXNNo,3) = 'OP-'
> OR PutHold.Completed = 1)
> GROUP BY Trx.PrincipalCode, Trx.ProductCode)T
> WHERE BaseQty > 0
> --
> Roji. P. Thomas
> Net Asset Management
> http://toponewithties.blogspot.com
>
> "Roji. P. Thomas" <thomasroji@.gmail.com> wrote in message
> news:uciunfpFGHA.1260@.TK2MSFTNGP15.phx.gbl...
>|||Here is a repro for others looking into the problem.
(SQL Server 2000 SP4)
The query without the last line (HAVING ) returns 3 rows, which is correct.
With HAVING it returns 6 rows and the result is incorrect
Use Pubs
GO
SELECT T.pub_id, T.type, SUM(T.price * 1) AS BasePrice
FROM Titles T
LEFT JOIN
(Select NULL) X (pub_id)
ON X.pub_id = T.pub_id
WHERE LEFT(T.title,3) = 'The'
GROUP BY T.pub_id, T.type
HAVING SUM(T.price * 1) > 0
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"jcvoon" <jcvoon@.maximas.com.my> wrote in message
news:1136948778.631640.17620@.g49g2000cwa.googlegroups.com...
> Hi:
> I've a problem with the following querys, These two query is suppose to
> produce a same result
> but it is not, i don't know why.
> The first query is using view, it procuce a correct result (2 rows),
> the second query is using inline view (the inline view defination is
> exactly the same as the view) but the result is wrong (4 rows).
>
> inline view twist which is not correct.
> Please help.
> JCVoon
>
>
> -- Join with view
> SELECT Trx.PrincipalCode, Trx.ProductCode, SUM(Trx.BaseQuantity *
> Trx.PhysicalFlag) AS BaseQty
> FROM WmsStockLedger Trx
> LEFT JOIN
> (
> SELECT * From view_PutHold
> WHERE CompanyCode='HQ' And BranchCode = 'HQ'
> ) PutHold
> ON PutHold.CompanyCode = Trx.CompanyCode
> And PutHold.BranchCode = Trx.BranchCode
> And PutHold.WONo = Trx.TxnNo
> And PutHold.ProductCode = Trx.ProductCode
> And PutHold.TallyInNo = Trx.TallyInNo
> WHERE
> Trx.CompanyCode='HQ'
> And Trx.BranchCode='HQ'
> And (CASE WHEN (LEFT(Trx.TXNNo,3) <> 'OP-') THEN
> IsNull(PutHold.Completed,0) ELSE 1 END) = 1
> GROUP BY Trx.PrincipalCode, Trx.ProductCode
> HAVING SUM(Trx.BaseQuantity * Trx.PhysicalFlag) > 0
> -- Join with inline view
> SELECT Trx.PrincipalCode, Trx.ProductCode, SUM(Trx.BaseQuantity *
> Trx.PhysicalFlag) AS BaseQty
> FROM WmsStockLedger Trx
> LEFT JOIN
> (
> Select Distinct Hd.CompanyCode, Hd.BranchCode, Hd.WoNo,
> Dt.ProductCode, Dt.Completed, Ti.TallyInNo, Ti.PrincipalCode, dt.qty
> FROM WmsPutawayHed Hd
> INNER JOIN WmsPutawayDet Dt
> ON Dt.CompanyCode = Hd.CompanyCode
> And Dt.BranchCode = Hd.BranchCode
> And Dt.WoNo = Hd.WoNo
> And Dt.Completed = 1
> INNER JOIN WmsTallyInHed Ti
> ON Ti.CompanyCode = Hd.CompanyCode
> And Ti.BranchCode = Hd.BranchCode
> And Ti.TallyInNo = Hd.TallyInNo
> WHERE Hd.CompanyCode = 'HQ'
> And Hd.BranchCode = 'HQ'
> ) PutHold
> ON PutHold.CompanyCode = Trx.CompanyCode
> And PutHold.BranchCode = Trx.BranchCode
> And PutHold.WONo = Trx.TxnNo
> And PutHold.ProductCode = Trx.ProductCode
> And PutHold.TallyInNo = Trx.TallyInNo
> WHERE
> Trx.CompanyCode='HQ'
> And Trx.BranchCode='HQ'
> And (CASE WHEN (LEFT(Trx.TXNNo,3) <> 'OP-') THEN
> IsNull(PutHold.Completed,0) ELSE 1 END) = 1
> GROUP BY Trx.PrincipalCode, Trx.ProductCode
> HAVING SUM(Trx.BaseQuantity * Trx.PhysicalFlag) > 0
>
> --Here is the DDL
> CREATE TABLE [dbo].[WmsPutawayDet] (
> [CompanyCode] [varchar] (2) NOT NULL ,
> [BranchCode] [varchar] (2) NOT NULL ,
> [WoNo] [varchar] (10) NOT NULL ,
> [ProductCode] [varchar] (10) NOT NULL ,
> [LocationCode] [varchar] (10) NOT NULL ,
> [Qty] [numeric](18, 0) NOT NULL ,
> [Completed] [bit] NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[WmsPutawayHed] (
> [CompanyCode] [varchar] (2) NOT NULL ,
> [BranchCode] [varchar] (2) NOT NULL ,
> [WoNo] [varchar] (10) NOT NULL ,
> [TallyInNo] [varchar] (10) NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[WmsTallyInHed] (
> [CompanyCode] [varchar] (2) NOT NULL ,
> [BranchCode] [varchar] (2) NOT NULL ,
> [TallyInNo] [varchar] (10) NOT NULL ,
> [PrincipalCode] [varchar] (10) NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[wmsStockLedger] (
> [CompanyCode] [varchar] (2) NOT NULL ,
> [BranchCode] [varchar] (2) NOT NULL ,
> [ProductCode] [varchar] (10) NOT NULL ,
> [LocationCode] [varchar] (10) NOT NULL ,
> [TallyInNo] [varchar] (10) NOT NULL ,
> [PrincipalCode] [varchar] (10) NOT NULL ,
> [TxnNo] [varchar] (10) NOT NULL ,
> [BaseQuantity] [numeric](18, 0) NOT NULL ,
> [PhysicalFlag] [int] NOT NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[WmsPutawayDet] ADD
> CONSTRAINT [PK_WmsPutawayDet] PRIMARY KEY CLUSTERED
> (
> [CompanyCode],
> [BranchCode],
> [WoNo],
> [ProductCode],
> [LocationCode]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[WmsPutawayHed] ADD
> CONSTRAINT [PK_WmsPutawayHed] PRIMARY KEY CLUSTERED
> (
> [CompanyCode],
> [BranchCode],
> [WoNo]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[WmsTallyInHed] ADD
> CONSTRAINT [PK_WmsTallyInHed] PRIMARY KEY CLUSTERED
> (
> [CompanyCode],
> [BranchCode],
> [TallyInNo]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[wmsStockLedger] ADD
> CONSTRAINT [PK_wmsStockLedger] PRIMARY KEY CLUSTERED
> (
> [CompanyCode],
> [BranchCode],
> [ProductCode],
> [LocationCode],
> [TallyInNo],
> [PrincipalCode],
> [TxnNo]
> ) ON [PRIMARY]
> GO
> create view view_PutHold as
> Select Distinct Hd.CompanyCode, Hd.BranchCode, Hd.WoNo,
> Dt.ProductCode, Dt.Completed, Ti.TallyInNo, Ti.PrincipalCode, Dt.qty
> FROM WmsPutawayHed Hd
> INNER JOIN WmsPutawayDet Dt
> ON Dt.CompanyCode = Hd.CompanyCode
> And Dt.BranchCode = Hd.BranchCode
> And Dt.WoNo = Hd.WoNo
> And Dt.Completed = 1
> INNER JOIN WmsTallyInHed Ti
> ON Ti.CompanyCode = Hd.CompanyCode
> And Ti.BranchCode = Hd.BranchCode
> And Ti.TallyInNo = Hd.TallyInNo
> GO
> INSERT INTO [WmsPutawayHed] VALUES('HQ','HQ','WO001','OP-001')
> INSERT INTO [WmsPutawayHed] VALUES('HQ','HQ','WO002','OP-002')
> INSERT INTO [WmsPutawayDet] VALUES('HQ','HQ','WO001','P1','A',5,1)
> INSERT INTO [WmsPutawayDet] VALUES('HQ','HQ','WO001','P1','B',5,0)
> INSERT INTO [WmsPutawayDet] VALUES('HQ','HQ','WO002','P2','A',10,1)
> INSERT INTO [WmsPutawayDet] VALUES('HQ','HQ','WO002','P2','B',10,1)
> INSERT INTO [WmsTallyInHed] VALUES('HQ','HQ','OP-001','P001')
> INSERT INTO [WmsTallyInHed] VALUES('HQ','HQ','OP-002','P001')
> INSERT INTO [WmsTallyInHed] VALUES('HQ','HQ','TI001','P001')
> INSERT INTO [WmsTallyInHed] VALUES('HQ','HQ','TI002','P001')
> INSERT INTO [WmsStockLedger]
> VALUES('HQ','HQ','P1','A','OP-001','P001','WO001',5,1)
> INSERT INTO [WmsStockLedger]
> VALUES('HQ','HQ','P1','B','OP-001','P001','WO001',5,1)
> INSERT INTO [WmsStockLedger]
> VALUES('HQ','HQ','P1','HOLD','OP-001','P001','OP-001',10,1)
> INSERT INTO [WmsStockLedger]
> VALUES('HQ','HQ','P1','HOLD','OP-001','P001','WO001',10,-1)
> INSERT INTO [WmsStockLedger]
> VALUES('HQ','HQ','P2','A','OP-002','P001','WO002',10,1)
> INSERT INTO [WmsStockLedger]
> VALUES('HQ','HQ','P2','B','OP-002','P001','WO002',10,1)
> INSERT INTO [WmsStockLedger]
> VALUES('HQ','HQ','P2','HOLD','OP-002','P001','OP-002',20,1)
> INSERT INTO [WmsStockLedger]
> VALUES('HQ','HQ','P2','HOLD','OP-002','P001','WO002',20,-1)
>|||Yes. this is a known bug.
http://support.microsoft.com/kb/308458/en-us
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"Roji. P. Thomas" <thomasroji@.gmail.com> wrote in message
news:ezkvteqFGHA.3000@.TK2MSFTNGP14.phx.gbl...
> Here is a repro for others looking into the problem.
> (SQL Server 2000 SP4)
> The query without the last line (HAVING ) returns 3 rows, which is
> correct.
> With HAVING it returns 6 rows and the result is incorrect
>
> Use Pubs
> GO
> SELECT T.pub_id, T.type, SUM(T.price * 1) AS BasePrice
> FROM Titles T
> LEFT JOIN
> (Select NULL) X (pub_id)
> ON X.pub_id = T.pub_id
> WHERE LEFT(T.title,3) = 'The'
> GROUP BY T.pub_id, T.type
> HAVING SUM(T.price * 1) > 0
>
> --
> Roji. P. Thomas
> Net Asset Management
> http://toponewithties.blogspot.com
>
> "jcvoon" <jcvoon@.maximas.com.my> wrote in message
> news:1136948778.631640.17620@.g49g2000cwa.googlegroups.com...
>|||Here is the best fix so far.
Just change LEFT(Trx.TXNNo,3) with SUBSTRING(Trx.TXNNo,1,3)
That seems to prevent the otimizer from doing the incorrect cross join.
SELECT Trx.PrincipalCode, Trx.ProductCode, SUM(Trx.BaseQuantity*
Trx.PhysicalFlag) AS BaseQty
FROM WmsStockLedger Trx
LEFT JOIN
(
Select Distinct Hd.CompanyCode, Hd.BranchCode, Hd.WoNo,
Dt.ProductCode, Dt.Completed, Ti.TallyInNo, Ti.PrincipalCode, dt.qty
FROM WmsPutawayHed Hd
INNER JOIN WmsPutawayDet Dt
ON Dt.CompanyCode = Hd.CompanyCode
And Dt.BranchCode = Hd.BranchCode
And Dt.WoNo = Hd.WoNo
And Dt.Completed = 1
INNER JOIN WmsTallyInHed Ti
ON Ti.CompanyCode = Hd.CompanyCode
And Ti.BranchCode = Hd.BranchCode
And Ti.TallyInNo = Hd.TallyInNo
WHERE Hd.CompanyCode = 'HQ'
And Hd.BranchCode = 'HQ'
) PutHold
ON PutHold.CompanyCode = Trx.CompanyCode
And PutHold.BranchCode = Trx.BranchCode
And PutHold.WONo = Trx.TxnNo
And PutHold.ProductCode = Trx.ProductCode
And PutHold.TallyInNo = Trx.TallyInNo
WHERE
Trx.CompanyCode='HQ'
And Trx.BranchCode='HQ'
And (CASE WHEN (SUBSTRING(Trx.TXNNo,1,3) <> 'OP-') THEN
IsNull(PutHold.Completed,0) ELSE 1 END) = 1
GROUP BY Trx.PrincipalCode, Trx.ProductCode
HAVING SUM(Trx.BaseQuantity * Trx.PhysicalFlag) > 0
Regards
Roji. P. Thomas
http://toponewithties.blogspot.com
"jcvoon" <jcvoon@.maximas.com.my> wrote in message
news:1136948778.631640.17620@.g49g2000cwa.googlegroups.com...
> Hi:
> I've a problem with the following querys, These two query is suppose to
> produce a same result
> but it is not, i don't know why.
> The first query is using view, it procuce a correct result (2 rows),
> the second query is using inline view (the inline view defination is
> exactly the same as the view) but the result is wrong (4 rows).
>
> inline view twist which is not correct.
> Please help.
> JCVoon
>
>
> -- Join with view
> SELECT Trx.PrincipalCode, Trx.ProductCode, SUM(Trx.BaseQuantity *
> Trx.PhysicalFlag) AS BaseQty
> FROM WmsStockLedger Trx
> LEFT JOIN
> (
> SELECT * From view_PutHold
> WHERE CompanyCode='HQ' And BranchCode = 'HQ'
> ) PutHold
> ON PutHold.CompanyCode = Trx.CompanyCode
> And PutHold.BranchCode = Trx.BranchCode
> And PutHold.WONo = Trx.TxnNo
> And PutHold.ProductCode = Trx.ProductCode
> And PutHold.TallyInNo = Trx.TallyInNo
> WHERE
> Trx.CompanyCode='HQ'
> And Trx.BranchCode='HQ'
> And (CASE WHEN (LEFT(Trx.TXNNo,3) <> 'OP-') THEN
> IsNull(PutHold.Completed,0) ELSE 1 END) = 1
> GROUP BY Trx.PrincipalCode, Trx.ProductCode
> HAVING SUM(Trx.BaseQuantity * Trx.PhysicalFlag) > 0
> -- Join with inline view
> SELECT Trx.PrincipalCode, Trx.ProductCode, SUM(Trx.BaseQuantity *
> Trx.PhysicalFlag) AS BaseQty
> FROM WmsStockLedger Trx
> LEFT JOIN
> (
> Select Distinct Hd.CompanyCode, Hd.BranchCode, Hd.WoNo,
> Dt.ProductCode, Dt.Completed, Ti.TallyInNo, Ti.PrincipalCode, dt.qty
> FROM WmsPutawayHed Hd
> INNER JOIN WmsPutawayDet Dt
> ON Dt.CompanyCode = Hd.CompanyCode
> And Dt.BranchCode = Hd.BranchCode
> And Dt.WoNo = Hd.WoNo
> And Dt.Completed = 1
> INNER JOIN WmsTallyInHed Ti
> ON Ti.CompanyCode = Hd.CompanyCode
> And Ti.BranchCode = Hd.BranchCode
> And Ti.TallyInNo = Hd.TallyInNo
> WHERE Hd.CompanyCode = 'HQ'
> And Hd.BranchCode = 'HQ'
> ) PutHold
> ON PutHold.CompanyCode = Trx.CompanyCode
> And PutHold.BranchCode = Trx.BranchCode
> And PutHold.WONo = Trx.TxnNo
> And PutHold.ProductCode = Trx.ProductCode
> And PutHold.TallyInNo = Trx.TallyInNo
> WHERE
> Trx.CompanyCode='HQ'
> And Trx.BranchCode='HQ'
> And (CASE WHEN (LEFT(Trx.TXNNo,3) <> 'OP-') THEN
> IsNull(PutHold.Completed,0) ELSE 1 END) = 1
> GROUP BY Trx.PrincipalCode, Trx.ProductCode
> HAVING SUM(Trx.BaseQuantity * Trx.PhysicalFlag) > 0
>
> --Here is the DDL
> CREATE TABLE [dbo].[WmsPutawayDet] (
> [CompanyCode] [varchar] (2) NOT NULL ,
> [BranchCode] [varchar] (2) NOT NULL ,
> [WoNo] [varchar] (10) NOT NULL ,
> [ProductCode] [varchar] (10) NOT NULL ,
> [LocationCode] [varchar] (10) NOT NULL ,
> [Qty] [numeric](18, 0) NOT NULL ,
> [Completed] [bit] NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[WmsPutawayHed] (
> [CompanyCode] [varchar] (2) NOT NULL ,
> [BranchCode] [varchar] (2) NOT NULL ,
> [WoNo] [varchar] (10) NOT NULL ,
> [TallyInNo] [varchar] (10) NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[WmsTallyInHed] (
> [CompanyCode] [varchar] (2) NOT NULL ,
> [BranchCode] [varchar] (2) NOT NULL ,
> [TallyInNo] [varchar] (10) NOT NULL ,
> [PrincipalCode] [varchar] (10) NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[wmsStockLedger] (
> [CompanyCode] [varchar] (2) NOT NULL ,
> [BranchCode] [varchar] (2) NOT NULL ,
> [ProductCode] [varchar] (10) NOT NULL ,
> [LocationCode] [varchar] (10) NOT NULL ,
> [TallyInNo] [varchar] (10) NOT NULL ,
> [PrincipalCode] [varchar] (10) NOT NULL ,
> [TxnNo] [varchar] (10) NOT NULL ,
> [BaseQuantity] [numeric](18, 0) NOT NULL ,
> [PhysicalFlag] [int] NOT NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[WmsPutawayDet] ADD
> CONSTRAINT [PK_WmsPutawayDet] PRIMARY KEY CLUSTERED
> (
> [CompanyCode],
> [BranchCode],
> [WoNo],
> [ProductCode],
> [LocationCode]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[WmsPutawayHed] ADD
> CONSTRAINT [PK_WmsPutawayHed] PRIMARY KEY CLUSTERED
> (
> [CompanyCode],
> [BranchCode],
> [WoNo]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[WmsTallyInHed] ADD
> CONSTRAINT [PK_WmsTallyInHed] PRIMARY KEY CLUSTERED
> (
> [CompanyCode],
> [BranchCode],
> [TallyInNo]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[wmsStockLedger] ADD
> CONSTRAINT [PK_wmsStockLedger] PRIMARY KEY CLUSTERED
> (
> [CompanyCode],
> [BranchCode],
> [ProductCode],
> [LocationCode],
> [TallyInNo],
> [PrincipalCode],
> [TxnNo]
> ) ON [PRIMARY]
> GO
> create view view_PutHold as
> Select Distinct Hd.CompanyCode, Hd.BranchCode, Hd.WoNo,
> Dt.ProductCode, Dt.Completed, Ti.TallyInNo, Ti.PrincipalCode, Dt.qty
> FROM WmsPutawayHed Hd
> INNER JOIN WmsPutawayDet Dt
> ON Dt.CompanyCode = Hd.CompanyCode
> And Dt.BranchCode = Hd.BranchCode
> And Dt.WoNo = Hd.WoNo
> And Dt.Completed = 1
> INNER JOIN WmsTallyInHed Ti
> ON Ti.CompanyCode = Hd.CompanyCode
> And Ti.BranchCode = Hd.BranchCode
> And Ti.TallyInNo = Hd.TallyInNo
> GO
> INSERT INTO [WmsPutawayHed] VALUES('HQ','HQ','WO001','OP-001')
> INSERT INTO [WmsPutawayHed] VALUES('HQ','HQ','WO002','OP-002')
> INSERT INTO [WmsPutawayDet] VALUES('HQ','HQ','WO001','P1','A',5,1)
> INSERT INTO [WmsPutawayDet] VALUES('HQ','HQ','WO001','P1','B',5,0)
> INSERT INTO [WmsPutawayDet] VALUES('HQ','HQ','WO002','P2','A',10,1)
> INSERT INTO [WmsPutawayDet] VALUES('HQ','HQ','WO002','P2','B',10,1)
> INSERT INTO [WmsTallyInHed] VALUES('HQ','HQ','OP-001','P001')
> INSERT INTO [WmsTallyInHed] VALUES('HQ','HQ','OP-002','P001')
> INSERT INTO [WmsTallyInHed] VALUES('HQ','HQ','TI001','P001')
> INSERT INTO [WmsTallyInHed] VALUES('HQ','HQ','TI002','P001')
> INSERT INTO [WmsStockLedger]
> VALUES('HQ','HQ','P1','A','OP-001','P001','WO001',5,1)
> INSERT INTO [WmsStockLedger]
> VALUES('HQ','HQ','P1','B','OP-001','P001','WO001',5,1)
> INSERT INTO [WmsStockLedger]
> VALUES('HQ','HQ','P1','HOLD','OP-001','P001','OP-001',10,1)
> INSERT INTO [WmsStockLedger]
> VALUES('HQ','HQ','P1','HOLD','OP-001','P001','WO001',10,-1)
> INSERT INTO [WmsStockLedger]
> VALUES('HQ','HQ','P2','A','OP-002','P001','WO002',10,1)
> INSERT INTO [WmsStockLedger]
> VALUES('HQ','HQ','P2','B','OP-002','P001','WO002',10,1)
> INSERT INTO [WmsStockLedger]
> VALUES('HQ','HQ','P2','HOLD','OP-002','P001','OP-002',20,1)
> INSERT INTO [WmsStockLedger]
> VALUES('HQ','HQ','P2','HOLD','OP-002','P001','WO002',20,-1)
>|||Roji. P. Thomas:
Thank you so much.
I've tested my original query on SQL2000PE with SP4, the problem still
exist.
With your work around my query working fine now.
Just wonder how do u know change LEFT(Trx.TXNNo,3) with
SUBSTRING(Trx.TXNNo,1,3) will solved the problem ?
Thanks
JCVoon
Friday, February 24, 2012
JOIN Query Problem
Hi, I'm not that experienced with this so I need some help :)
I've got 2 tables a Header and a Line table
Now I want to select all headers but I want to filter out those that
have lines with the Status Finished
So I came up with this query:
SELECT *
FROM Header
LEFT JOIN Line ON (Header.ID=Line.HeaderID AND
Line.Status<>'FINISHED')
WHERE Header.ID<>'';
The problem here is that I get all headers even the ones with the have
lines with the status Finished. Only those lines don't show, but the
orders do.
Can't I do this in 1 single query?
Thanks in advanceHi
SELECT *
FROM Header
JOIN Line ON (Header.ID=Line.HeaderID ) WHERE Line.Status<>'FINISHED' AND
Header.ID<>''
<kenny.vaes@.gmail.com> wrote in message
news:1133263151.681455.181630@.z14g2000cwz.googlegroups.com...
> Hi, I'm not that experienced with this so I need some help :)
> I've got 2 tables a Header and a Line table
> Now I want to select all headers but I want to filter out those that
> have lines with the Status Finished
> So I came up with this query:
> SELECT *
> FROM Header
> LEFT JOIN Line ON (Header.ID=Line.HeaderID AND
> Line.Status<>'FINISHED')
> WHERE Header.ID<>'';
> The problem here is that I get all headers even the ones with the have
> lines with the status Finished. Only those lines don't show, but the
> orders do.
> Can't I do this in 1 single query?
> Thanks in advance
>|||And this here don=B4t work for you ?
SELECT *
FROM Header
LEFT JOIN Line ON (Header.ID=3DLine.HeaderID
WHERE Header.ID<>'' AND Line.Status<>'FINISHED'=20
HTH, jens Suessmeyer.|||Without that parentethes which priduces an error:
SELECT *
FROM Header
LEFT JOIN Line ON Header.ID=Line.HeaderID
WHERE Header.ID<>'' AND Line.Status<>'FINISHED'|||Jens
Yep, that's exactly what I've posted , in that case he does not need to
have LEFT JOIN . It produces the same result
"Jens" <Jens@.sqlserver2005.de> wrote in message
news:1133264002.077565.231590@.z14g2000cwz.googlegroups.com...
> Without that parentethes which priduces an error:
> SELECT *
> FROM Header
> LEFT JOIN Line ON Header.ID=Line.HeaderID
> WHERE Header.ID<>'' AND Line.Status<>'FINISHED'
>|||When I use this syntax I do not see the Headers that do not have a
corresponding line.
When i do not use the filter AND Line.Status<>'FINISHED' I see all
Headers, even the ones that have no lines. But when I now filter on the
status. All the ones With status = finished dissappear but also the
empty ones.
And i really need to see the empty ones also :(|||Or is there a way to select only the headers that have no Lines? That
way I can put all the ID's in a collection and do a select at the end
of al those ID's.
So it would be a select of all the ones that have not been finished and
afterwards one woth all the empty ones.|||kenny.vaes@.gmail.com wrote on 29 Nov 2005 04:59:49 -0800:
> When I use this syntax I do not see the Headers that do not have a
> corresponding line.
> When i do not use the filter AND Line.Status<>'FINISHED' I see all
> Headers, even the ones that have no lines. But when I now filter on the
> status. All the ones With status = finished dissappear but also the
> empty ones.
> And i really need to see the empty ones also :(
SELECT *
FROM Header
LEFT JOIN Line ON Header.ID=Line.HeaderID
WHERE Header.ID<>'' AND (Line.Status<>'FINISHED' or Line.Status IS NULL)
Dan
I've got 2 tables a Header and a Line table
Now I want to select all headers but I want to filter out those that
have lines with the Status Finished
So I came up with this query:
SELECT *
FROM Header
LEFT JOIN Line ON (Header.ID=Line.HeaderID AND
Line.Status<>'FINISHED')
WHERE Header.ID<>'';
The problem here is that I get all headers even the ones with the have
lines with the status Finished. Only those lines don't show, but the
orders do.
Can't I do this in 1 single query?
Thanks in advanceHi
SELECT *
FROM Header
JOIN Line ON (Header.ID=Line.HeaderID ) WHERE Line.Status<>'FINISHED' AND
Header.ID<>''
<kenny.vaes@.gmail.com> wrote in message
news:1133263151.681455.181630@.z14g2000cwz.googlegroups.com...
> Hi, I'm not that experienced with this so I need some help :)
> I've got 2 tables a Header and a Line table
> Now I want to select all headers but I want to filter out those that
> have lines with the Status Finished
> So I came up with this query:
> SELECT *
> FROM Header
> LEFT JOIN Line ON (Header.ID=Line.HeaderID AND
> Line.Status<>'FINISHED')
> WHERE Header.ID<>'';
> The problem here is that I get all headers even the ones with the have
> lines with the status Finished. Only those lines don't show, but the
> orders do.
> Can't I do this in 1 single query?
> Thanks in advance
>|||And this here don=B4t work for you ?
SELECT *
FROM Header
LEFT JOIN Line ON (Header.ID=3DLine.HeaderID
WHERE Header.ID<>'' AND Line.Status<>'FINISHED'=20
HTH, jens Suessmeyer.|||Without that parentethes which priduces an error:
SELECT *
FROM Header
LEFT JOIN Line ON Header.ID=Line.HeaderID
WHERE Header.ID<>'' AND Line.Status<>'FINISHED'|||Jens
Yep, that's exactly what I've posted , in that case he does not need to
have LEFT JOIN . It produces the same result
"Jens" <Jens@.sqlserver2005.de> wrote in message
news:1133264002.077565.231590@.z14g2000cwz.googlegroups.com...
> Without that parentethes which priduces an error:
> SELECT *
> FROM Header
> LEFT JOIN Line ON Header.ID=Line.HeaderID
> WHERE Header.ID<>'' AND Line.Status<>'FINISHED'
>|||When I use this syntax I do not see the Headers that do not have a
corresponding line.
When i do not use the filter AND Line.Status<>'FINISHED' I see all
Headers, even the ones that have no lines. But when I now filter on the
status. All the ones With status = finished dissappear but also the
empty ones.
And i really need to see the empty ones also :(|||Or is there a way to select only the headers that have no Lines? That
way I can put all the ID's in a collection and do a select at the end
of al those ID's.
So it would be a select of all the ones that have not been finished and
afterwards one woth all the empty ones.|||kenny.vaes@.gmail.com wrote on 29 Nov 2005 04:59:49 -0800:
> When I use this syntax I do not see the Headers that do not have a
> corresponding line.
> When i do not use the filter AND Line.Status<>'FINISHED' I see all
> Headers, even the ones that have no lines. But when I now filter on the
> status. All the ones With status = finished dissappear but also the
> empty ones.
> And i really need to see the empty ones also :(
SELECT *
FROM Header
LEFT JOIN Line ON Header.ID=Line.HeaderID
WHERE Header.ID<>'' AND (Line.Status<>'FINISHED' or Line.Status IS NULL)
Dan
Join Query on a PK
I am building a small database that is somewhat like a ledger for pricing team here. In a given month the get 15 cases or so. I made the case number my pk. A case number is the pricer's first name and last name then a three digit number. So RB001. Then I have a table that has initials and full names. What I want to do is to be able to rip the RB out and compare that against the table with the names, so the pricers can pull records by their name...Try: SELECT case_nr[1,2] FROM ...
Don't know if this is standard SQL but it works fine in Informix...
Regards|||SUBSTRING(casenumber FROM 1 FOR 2)
also, it will probably help if you would post your questions in the forum specific to your particular database system
this forum is for standard SQL, the language, and while all database systems support standard SQL to one degree or another, it is in the area of functions that support is most sporadic (to say nothing of the availability of many non-standard but quite useful functions in different databases)|||Or simply: SUBSTRING(casenumber, 1, 2)
In M$ Access you may need to use: LEFT(casenumber,1)
:D|||Or simply: SUBSTRING(casenumber, 1, 2)
In M$ Access you may need to use: LEFT(casenumber,1)
:Dunless Access automatically doubles up bytes, i would suggest LEFT(casenumber,2)
:cool:|||Ooops, yes that was a typo. :rolleyes:|||Yet some other systems will needSUBSTR(casenumber,1,2)|||I made the case number my pk. A case number is the pricer's first name and last name then a three digit number. So RB001.
An interesting alternative (especially in terms of performance) could be to define a two-column PK. The first column would then have "RB" and the second one "001", which (1) avoids the costly substring() construct, and (2) allows e.g. having an index on that first column for efficient retrieval. Moreover it's more flexible in that it will easily allow e.g. 3-letter initials in the future without having to change any of your queries (which is not the case now).|||It is an Access DB. But seeing this was strictly how to pull information from a table USING SQL, I figured it was more appropriate to ask a question directly to the SQL Forum.
Actually it'd be left$(CaseNo,2), but this doesn't work.
Peter - I think I may have to split them up like you said.|||... doo bee doo ... USING SQL ... doo bee doo
there is sql, and there is sql, and there is standard sql...
... and then there is access sql ;)
the whole point about what we are telling you is that unless you know the difference between the various types of sql, and how they are likely to vary from standard sql, it would be far better for you if you would post in the specific forum for your specific database
just trying to save you the agro, man
;)|||ok thanks, i'll see what i can do
Don't know if this is standard SQL but it works fine in Informix...
Regards|||SUBSTRING(casenumber FROM 1 FOR 2)
also, it will probably help if you would post your questions in the forum specific to your particular database system
this forum is for standard SQL, the language, and while all database systems support standard SQL to one degree or another, it is in the area of functions that support is most sporadic (to say nothing of the availability of many non-standard but quite useful functions in different databases)|||Or simply: SUBSTRING(casenumber, 1, 2)
In M$ Access you may need to use: LEFT(casenumber,1)
:D|||Or simply: SUBSTRING(casenumber, 1, 2)
In M$ Access you may need to use: LEFT(casenumber,1)
:Dunless Access automatically doubles up bytes, i would suggest LEFT(casenumber,2)
:cool:|||Ooops, yes that was a typo. :rolleyes:|||Yet some other systems will needSUBSTR(casenumber,1,2)|||I made the case number my pk. A case number is the pricer's first name and last name then a three digit number. So RB001.
An interesting alternative (especially in terms of performance) could be to define a two-column PK. The first column would then have "RB" and the second one "001", which (1) avoids the costly substring() construct, and (2) allows e.g. having an index on that first column for efficient retrieval. Moreover it's more flexible in that it will easily allow e.g. 3-letter initials in the future without having to change any of your queries (which is not the case now).|||It is an Access DB. But seeing this was strictly how to pull information from a table USING SQL, I figured it was more appropriate to ask a question directly to the SQL Forum.
Actually it'd be left$(CaseNo,2), but this doesn't work.
Peter - I think I may have to split them up like you said.|||... doo bee doo ... USING SQL ... doo bee doo
there is sql, and there is sql, and there is standard sql...
... and then there is access sql ;)
the whole point about what we are telling you is that unless you know the difference between the various types of sql, and how they are likely to vary from standard sql, it would be far better for you if you would post in the specific forum for your specific database
just trying to save you the agro, man
;)|||ok thanks, i'll see what i can do
Join query not working
howz yew all
Iam in a middle of a project and iam stuck at reporting ill cut the
details it is so that i have three tables one is
"tbl_Transactions" (containing columns "Transaction_DetailID,
Transaction_Amount, Transaction_Type, Account_ID, Transaction_ID")
Transaction_Type contains CR for credit and DR for debit.
and other is
"tbl_TransactionDetails" (containing columns "Transaction_ID,
Transaction_Date")
and the third table is
"tbl_Account" (containing columns "Account_ID, Account_DateOfCreation,
Account_ParentID, Account_Name, Account_Description)
Now what iam not been able to do is that
i want to select Transaction_ID, Transaction_Date, Account_Name(for
Transaction_Type debit), Account_Name(for Transaction_Type Credit),
one debit and multiple credit entries and vice versa, iam trying to
join all tables but i dont know how to work :s can sum 1 plz help me
thnx in advance
take care
bye
Hi
> i want to select Transaction_ID, Transaction_Date, Account_Name(for
> Transaction_Type debit), Account_Name(for Transaction_Type Credit),
> one debit and multiple credit entries and vice versa, iam trying to
> join all tables but i dont know how to work :s can sum 1 plz help me
>
select * from tbl_Transactions t join tbl_TransactionDetails td on
t.Transaction_ID=td.Transaction_ID join tbl_Account ta
on ta.Account_ID=t.Account_ID
<umairsyed19@.gmail.com> wrote in message
news:c28a6557-2dfc-4efb-ad9f-c46aea287c12@.v3g2000hsc.googlegroups.com...
> howz yew all
> Iam in a middle of a project and iam stuck at reporting ill cut the
> details it is so that i have three tables one is
> "tbl_Transactions" (containing columns "Transaction_DetailID,
> Transaction_Amount, Transaction_Type, Account_ID, Transaction_ID")
> Transaction_Type contains CR for credit and DR for debit.
> and other is
> "tbl_TransactionDetails" (containing columns "Transaction_ID,
> Transaction_Date")
> and the third table is
> "tbl_Account" (containing columns "Account_ID, Account_DateOfCreation,
> Account_ParentID, Account_Name, Account_Description)
> Now what iam not been able to do is that
> i want to select Transaction_ID, Transaction_Date, Account_Name(for
> Transaction_Type debit), Account_Name(for Transaction_Type Credit),
> one debit and multiple credit entries and vice versa, iam trying to
> join all tables but i dont know how to work :s can sum 1 plz help me
> thnx in advance
> take care
> bye
Iam in a middle of a project and iam stuck at reporting ill cut the
details it is so that i have three tables one is
"tbl_Transactions" (containing columns "Transaction_DetailID,
Transaction_Amount, Transaction_Type, Account_ID, Transaction_ID")
Transaction_Type contains CR for credit and DR for debit.
and other is
"tbl_TransactionDetails" (containing columns "Transaction_ID,
Transaction_Date")
and the third table is
"tbl_Account" (containing columns "Account_ID, Account_DateOfCreation,
Account_ParentID, Account_Name, Account_Description)
Now what iam not been able to do is that
i want to select Transaction_ID, Transaction_Date, Account_Name(for
Transaction_Type debit), Account_Name(for Transaction_Type Credit),
one debit and multiple credit entries and vice versa, iam trying to
join all tables but i dont know how to work :s can sum 1 plz help me
thnx in advance
take care
bye
Hi
> i want to select Transaction_ID, Transaction_Date, Account_Name(for
> Transaction_Type debit), Account_Name(for Transaction_Type Credit),
> one debit and multiple credit entries and vice versa, iam trying to
> join all tables but i dont know how to work :s can sum 1 plz help me
>
select * from tbl_Transactions t join tbl_TransactionDetails td on
t.Transaction_ID=td.Transaction_ID join tbl_Account ta
on ta.Account_ID=t.Account_ID
<umairsyed19@.gmail.com> wrote in message
news:c28a6557-2dfc-4efb-ad9f-c46aea287c12@.v3g2000hsc.googlegroups.com...
> howz yew all
> Iam in a middle of a project and iam stuck at reporting ill cut the
> details it is so that i have three tables one is
> "tbl_Transactions" (containing columns "Transaction_DetailID,
> Transaction_Amount, Transaction_Type, Account_ID, Transaction_ID")
> Transaction_Type contains CR for credit and DR for debit.
> and other is
> "tbl_TransactionDetails" (containing columns "Transaction_ID,
> Transaction_Date")
> and the third table is
> "tbl_Account" (containing columns "Account_ID, Account_DateOfCreation,
> Account_ParentID, Account_Name, Account_Description)
> Now what iam not been able to do is that
> i want to select Transaction_ID, Transaction_Date, Account_Name(for
> Transaction_Type debit), Account_Name(for Transaction_Type Credit),
> one debit and multiple credit entries and vice versa, iam trying to
> join all tables but i dont know how to work :s can sum 1 plz help me
> thnx in advance
> take care
> bye
Join query not working
howz yew all
Iam in a middle of a project and iam stuck at reporting ill cut the
details it is so that i have three tables one is
"tbl_Transactions" (containing columns "Transaction_DetailID,
Transaction_Amount, Transaction_Type, Account_ID, Transaction_ID")
Transaction_Type contains CR for credit and DR for debit.
and other is
"tbl_TransactionDetails" (containing columns "Transaction_ID,
Transaction_Date")
and the third table is
"tbl_Account" (containing columns "Account_ID, Account_DateOfCreation,
Account_ParentID, Account_Name, Account_Description)
Now what iam not been able to do is that
i want to select Transaction_ID, Transaction_Date, Account_Name(for
Transaction_Type debit), Account_Name(for Transaction_Type Credit),
one debit and multiple credit entries and vice versa, iam trying to
join all tables but i dont know how to work :s can sum 1 plz help me
thnx in advance
take care
byeHi
> i want to select Transaction_ID, Transaction_Date, Account_Name(for
> Transaction_Type debit), Account_Name(for Transaction_Type Credit),
> one debit and multiple credit entries and vice versa, iam trying to
> join all tables but i dont know how to work :s can sum 1 plz help me
>
select * from tbl_Transactions t join tbl_TransactionDetails td on
t.Transaction_ID=td.Transaction_ID join tbl_Account ta
on ta.Account_ID=t.Account_ID
<umairsyed19@.gmail.com> wrote in message
news:c28a6557-2dfc-4efb-ad9f-c46aea287c12@.v3g2000hsc.googlegroups.com...
> howz yew all
> Iam in a middle of a project and iam stuck at reporting ill cut the
> details it is so that i have three tables one is
> "tbl_Transactions" (containing columns "Transaction_DetailID,
> Transaction_Amount, Transaction_Type, Account_ID, Transaction_ID")
> Transaction_Type contains CR for credit and DR for debit.
> and other is
> "tbl_TransactionDetails" (containing columns "Transaction_ID,
> Transaction_Date")
> and the third table is
> "tbl_Account" (containing columns "Account_ID, Account_DateOfCreation,
> Account_ParentID, Account_Name, Account_Description)
> Now what iam not been able to do is that
> i want to select Transaction_ID, Transaction_Date, Account_Name(for
> Transaction_Type debit), Account_Name(for Transaction_Type Credit),
> one debit and multiple credit entries and vice versa, iam trying to
> join all tables but i dont know how to work :s can sum 1 plz help me
> thnx in advance
> take care
> bye
Iam in a middle of a project and iam stuck at reporting ill cut the
details it is so that i have three tables one is
"tbl_Transactions" (containing columns "Transaction_DetailID,
Transaction_Amount, Transaction_Type, Account_ID, Transaction_ID")
Transaction_Type contains CR for credit and DR for debit.
and other is
"tbl_TransactionDetails" (containing columns "Transaction_ID,
Transaction_Date")
and the third table is
"tbl_Account" (containing columns "Account_ID, Account_DateOfCreation,
Account_ParentID, Account_Name, Account_Description)
Now what iam not been able to do is that
i want to select Transaction_ID, Transaction_Date, Account_Name(for
Transaction_Type debit), Account_Name(for Transaction_Type Credit),
one debit and multiple credit entries and vice versa, iam trying to
join all tables but i dont know how to work :s can sum 1 plz help me
thnx in advance
take care
byeHi
> i want to select Transaction_ID, Transaction_Date, Account_Name(for
> Transaction_Type debit), Account_Name(for Transaction_Type Credit),
> one debit and multiple credit entries and vice versa, iam trying to
> join all tables but i dont know how to work :s can sum 1 plz help me
>
select * from tbl_Transactions t join tbl_TransactionDetails td on
t.Transaction_ID=td.Transaction_ID join tbl_Account ta
on ta.Account_ID=t.Account_ID
<umairsyed19@.gmail.com> wrote in message
news:c28a6557-2dfc-4efb-ad9f-c46aea287c12@.v3g2000hsc.googlegroups.com...
> howz yew all
> Iam in a middle of a project and iam stuck at reporting ill cut the
> details it is so that i have three tables one is
> "tbl_Transactions" (containing columns "Transaction_DetailID,
> Transaction_Amount, Transaction_Type, Account_ID, Transaction_ID")
> Transaction_Type contains CR for credit and DR for debit.
> and other is
> "tbl_TransactionDetails" (containing columns "Transaction_ID,
> Transaction_Date")
> and the third table is
> "tbl_Account" (containing columns "Account_ID, Account_DateOfCreation,
> Account_ParentID, Account_Name, Account_Description)
> Now what iam not been able to do is that
> i want to select Transaction_ID, Transaction_Date, Account_Name(for
> Transaction_Type debit), Account_Name(for Transaction_Type Credit),
> one debit and multiple credit entries and vice versa, iam trying to
> join all tables but i dont know how to work :s can sum 1 plz help me
> thnx in advance
> take care
> bye
Join Query In SQL Server
Hi All,
I need you help about query in SQL Server. I have one table
ItemLocation. It has 2 fields
Item Location
A Loc1
B Loc2
C Loc1
D Loc3
E Loc2
F Loc2
I do query to select like this :
select Item from ItemLocation where Location='Loc1'
select Item from ItemLocation where Location='Loc2'
select Item from ItemLocation where Location='Loc3'
But i don't know how to join three query in above, so the result will
become like this
Loc1 Loc2 Loc3
A B D
C E
F
Location in ItemLocation is fix. Only three locations (Loc1, Loc2 and
Loc3).
Thanks for your help.Hi
Usually this is best left to your client application to display the data.
If you have a way of ranking the data then you can use something like:
CREATE TABLE #locs ( id int not null identity, type char(1), loc char(4) )
INSERT INTO #locs ( type, loc )
SELECT 'A', 'Loc1'
UNION ALL SELECT 'B', 'Loc2'
UNION ALL SELECT 'C', 'Loc1'
UNION ALL SELECT 'D', 'Loc3'
UNION ALL SELECT 'E', 'Loc2'
UNION ALL SELECT 'F', 'Loc2'
SELECT * from #locs
SELECT r.rank, d1.[type],d2.[type],d3.[type]
FROM
( SELECT 1 AS Rank
UNION ALL SELECT 2
UNION ALL SELECT 3
UNION ALL SELECT 4
UNION ALL SELECT 5
UNION ALL SELECT 6
UNION ALL SELECT 7
UNION ALL SELECT 8
UNION ALL SELECT 9
UNION ALL SELECT 10 ) AS r
LEFT JOIN
( SELECT ( SELECT COUNT(*) FROM #locs m where m.id <= l.id AND m.loc = l.loc
) as RANK,
[type],
loc
FROM #locs l WHERE l.loc = 'loc1') d1 ON r.rank = d1.rank
LEFT JOIN
( SELECT ( SELECT COUNT(*) FROM #locs m where m.id <= l.id AND m.loc = l.loc
) as RANK,
[type],
loc
FROM #locs l WHERE l.loc = 'loc2') d2 ON r.rank = d2.rank
LEFT JOIN
( SELECT ( SELECT COUNT(*) FROM #locs m where m.id <= l.id AND m.loc = l.loc
) as RANK,
[type],
loc
FROM #locs l WHERE l.loc = 'loc3') d3 ON r.rank = d3.rank
WHERE d1.rank IS NOT NULL
OR d2.rank IS NOT NULL
OR d3.rank IS NOT NULL
If you are going to have lots of rows then you may want to create a numbers
table.
For more information you may want to check out
http://msdn.microsoft.com/library/d...y/en-us/acda...
If you need to have a dynamic pivot you may also want to look at Itziks
articles on crosstabs in SQL Server magazine
http://www.sqlmag.com/Articles/Arti...5608/15608.html
If you are using SQL 2005 then you could use the PIVOT function.
John
"afang" wrote:
> Hi All,
> I need you help about query in SQL Server. I have one table
> ItemLocation. It has 2 fields
> Item Location
> A Loc1
> B Loc2
> C Loc1
> D Loc3
> E Loc2
> F Loc2
> I do query to select like this :
> select Item from ItemLocation where Location='Loc1'
> select Item from ItemLocation where Location='Loc2'
> select Item from ItemLocation where Location='Loc3'
> But i don't know how to join three query in above, so the result will
> become like this
> Loc1 Loc2 Loc3
> A B D
> C E
> F
> Location in ItemLocation is fix. Only three locations (Loc1, Loc2 and
> Loc3).
> Thanks for your help.
>
I need you help about query in SQL Server. I have one table
ItemLocation. It has 2 fields
Item Location
A Loc1
B Loc2
C Loc1
D Loc3
E Loc2
F Loc2
I do query to select like this :
select Item from ItemLocation where Location='Loc1'
select Item from ItemLocation where Location='Loc2'
select Item from ItemLocation where Location='Loc3'
But i don't know how to join three query in above, so the result will
become like this
Loc1 Loc2 Loc3
A B D
C E
F
Location in ItemLocation is fix. Only three locations (Loc1, Loc2 and
Loc3).
Thanks for your help.Hi
Usually this is best left to your client application to display the data.
If you have a way of ranking the data then you can use something like:
CREATE TABLE #locs ( id int not null identity, type char(1), loc char(4) )
INSERT INTO #locs ( type, loc )
SELECT 'A', 'Loc1'
UNION ALL SELECT 'B', 'Loc2'
UNION ALL SELECT 'C', 'Loc1'
UNION ALL SELECT 'D', 'Loc3'
UNION ALL SELECT 'E', 'Loc2'
UNION ALL SELECT 'F', 'Loc2'
SELECT * from #locs
SELECT r.rank, d1.[type],d2.[type],d3.[type]
FROM
( SELECT 1 AS Rank
UNION ALL SELECT 2
UNION ALL SELECT 3
UNION ALL SELECT 4
UNION ALL SELECT 5
UNION ALL SELECT 6
UNION ALL SELECT 7
UNION ALL SELECT 8
UNION ALL SELECT 9
UNION ALL SELECT 10 ) AS r
LEFT JOIN
( SELECT ( SELECT COUNT(*) FROM #locs m where m.id <= l.id AND m.loc = l.loc
) as RANK,
[type],
loc
FROM #locs l WHERE l.loc = 'loc1') d1 ON r.rank = d1.rank
LEFT JOIN
( SELECT ( SELECT COUNT(*) FROM #locs m where m.id <= l.id AND m.loc = l.loc
) as RANK,
[type],
loc
FROM #locs l WHERE l.loc = 'loc2') d2 ON r.rank = d2.rank
LEFT JOIN
( SELECT ( SELECT COUNT(*) FROM #locs m where m.id <= l.id AND m.loc = l.loc
) as RANK,
[type],
loc
FROM #locs l WHERE l.loc = 'loc3') d3 ON r.rank = d3.rank
WHERE d1.rank IS NOT NULL
OR d2.rank IS NOT NULL
OR d3.rank IS NOT NULL
If you are going to have lots of rows then you may want to create a numbers
table.
For more information you may want to check out
http://msdn.microsoft.com/library/d...y/en-us/acda...
If you need to have a dynamic pivot you may also want to look at Itziks
articles on crosstabs in SQL Server magazine
http://www.sqlmag.com/Articles/Arti...5608/15608.html
If you are using SQL 2005 then you could use the PIVOT function.
John
"afang" wrote:
> Hi All,
> I need you help about query in SQL Server. I have one table
> ItemLocation. It has 2 fields
> Item Location
> A Loc1
> B Loc2
> C Loc1
> D Loc3
> E Loc2
> F Loc2
> I do query to select like this :
> select Item from ItemLocation where Location='Loc1'
> select Item from ItemLocation where Location='Loc2'
> select Item from ItemLocation where Location='Loc3'
> But i don't know how to join three query in above, so the result will
> become like this
> Loc1 Loc2 Loc3
> A B D
> C E
> F
> Location in ItemLocation is fix. Only three locations (Loc1, Loc2 and
> Loc3).
> Thanks for your help.
>
Join Query In SQL Server
Hi All,
I need you help about query in SQL Server. I have one table
ItemLocation. It has 2 fields
Item Location
A Loc1
B Loc2
C Loc1
D Loc3
E Loc2
F Loc2
I do query to select like this :
select Item from ItemLocation where Location='Loc1'
select Item from ItemLocation where Location='Loc2'
select Item from ItemLocation where Location='Loc3'
But i don't know how to join three query in above, so the result will
become like this
Loc1 Loc2 Loc3
A B D
C E
F
Location in ItemLocation is fix. Only three locations (Loc1, Loc2 and
Loc3).
Thanks for your help.Hi
Usually this is best left to your client application to display the data.
If you have a way of ranking the data then you can use something like:
CREATE TABLE #locs ( id int not null identity, type char(1), loc char(4) )
INSERT INTO #locs ( type, loc )
SELECT 'A', 'Loc1'
UNION ALL SELECT 'B', 'Loc2'
UNION ALL SELECT 'C', 'Loc1'
UNION ALL SELECT 'D', 'Loc3'
UNION ALL SELECT 'E', 'Loc2'
UNION ALL SELECT 'F', 'Loc2'
SELECT * from #locs
SELECT r.rank, d1.[type],d2.[type],d3.[type]
FROM
( SELECT 1 AS Rank
UNION ALL SELECT 2
UNION ALL SELECT 3
UNION ALL SELECT 4
UNION ALL SELECT 5
UNION ALL SELECT 6
UNION ALL SELECT 7
UNION ALL SELECT 8
UNION ALL SELECT 9
UNION ALL SELECT 10 ) AS r
LEFT JOIN
( SELECT ( SELECT COUNT(*) FROM #locs m where m.id <= l.id AND m.loc = l.loc
) as RANK,
[type],
loc
FROM #locs l WHERE l.loc = 'loc1') d1 ON r.rank = d1.rank
LEFT JOIN
( SELECT ( SELECT COUNT(*) FROM #locs m where m.id <= l.id AND m.loc = l.loc
) as RANK,
[type],
loc
FROM #locs l WHERE l.loc = 'loc2') d2 ON r.rank = d2.rank
LEFT JOIN
( SELECT ( SELECT COUNT(*) FROM #locs m where m.id <= l.id AND m.loc = l.loc
) as RANK,
[type],
loc
FROM #locs l WHERE l.loc = 'loc3') d3 ON r.rank = d3.rank
WHERE d1.rank IS NOT NULL
OR d2.rank IS NOT NULL
OR d3.rank IS NOT NULL
If you are going to have lots of rows then you may want to create a numbers
table.
For more information you may want to check out
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acda...
If you need to have a dynamic pivot you may also want to look at Itziks
articles on crosstabs in SQL Server magazine
http://www.sqlmag.com/Articles/ArticleID/15608/15608.html
If you are using SQL 2005 then you could use the PIVOT function.
John
"afang" wrote:
> Hi All,
> I need you help about query in SQL Server. I have one table
> ItemLocation. It has 2 fields
> Item Location
> A Loc1
> B Loc2
> C Loc1
> D Loc3
> E Loc2
> F Loc2
> I do query to select like this :
> select Item from ItemLocation where Location='Loc1'
> select Item from ItemLocation where Location='Loc2'
> select Item from ItemLocation where Location='Loc3'
> But i don't know how to join three query in above, so the result will
> become like this
> Loc1 Loc2 Loc3
> A B D
> C E
> F
> Location in ItemLocation is fix. Only three locations (Loc1, Loc2 and
> Loc3).
> Thanks for your help.
>
I need you help about query in SQL Server. I have one table
ItemLocation. It has 2 fields
Item Location
A Loc1
B Loc2
C Loc1
D Loc3
E Loc2
F Loc2
I do query to select like this :
select Item from ItemLocation where Location='Loc1'
select Item from ItemLocation where Location='Loc2'
select Item from ItemLocation where Location='Loc3'
But i don't know how to join three query in above, so the result will
become like this
Loc1 Loc2 Loc3
A B D
C E
F
Location in ItemLocation is fix. Only three locations (Loc1, Loc2 and
Loc3).
Thanks for your help.Hi
Usually this is best left to your client application to display the data.
If you have a way of ranking the data then you can use something like:
CREATE TABLE #locs ( id int not null identity, type char(1), loc char(4) )
INSERT INTO #locs ( type, loc )
SELECT 'A', 'Loc1'
UNION ALL SELECT 'B', 'Loc2'
UNION ALL SELECT 'C', 'Loc1'
UNION ALL SELECT 'D', 'Loc3'
UNION ALL SELECT 'E', 'Loc2'
UNION ALL SELECT 'F', 'Loc2'
SELECT * from #locs
SELECT r.rank, d1.[type],d2.[type],d3.[type]
FROM
( SELECT 1 AS Rank
UNION ALL SELECT 2
UNION ALL SELECT 3
UNION ALL SELECT 4
UNION ALL SELECT 5
UNION ALL SELECT 6
UNION ALL SELECT 7
UNION ALL SELECT 8
UNION ALL SELECT 9
UNION ALL SELECT 10 ) AS r
LEFT JOIN
( SELECT ( SELECT COUNT(*) FROM #locs m where m.id <= l.id AND m.loc = l.loc
) as RANK,
[type],
loc
FROM #locs l WHERE l.loc = 'loc1') d1 ON r.rank = d1.rank
LEFT JOIN
( SELECT ( SELECT COUNT(*) FROM #locs m where m.id <= l.id AND m.loc = l.loc
) as RANK,
[type],
loc
FROM #locs l WHERE l.loc = 'loc2') d2 ON r.rank = d2.rank
LEFT JOIN
( SELECT ( SELECT COUNT(*) FROM #locs m where m.id <= l.id AND m.loc = l.loc
) as RANK,
[type],
loc
FROM #locs l WHERE l.loc = 'loc3') d3 ON r.rank = d3.rank
WHERE d1.rank IS NOT NULL
OR d2.rank IS NOT NULL
OR d3.rank IS NOT NULL
If you are going to have lots of rows then you may want to create a numbers
table.
For more information you may want to check out
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acda...
If you need to have a dynamic pivot you may also want to look at Itziks
articles on crosstabs in SQL Server magazine
http://www.sqlmag.com/Articles/ArticleID/15608/15608.html
If you are using SQL 2005 then you could use the PIVOT function.
John
"afang" wrote:
> Hi All,
> I need you help about query in SQL Server. I have one table
> ItemLocation. It has 2 fields
> Item Location
> A Loc1
> B Loc2
> C Loc1
> D Loc3
> E Loc2
> F Loc2
> I do query to select like this :
> select Item from ItemLocation where Location='Loc1'
> select Item from ItemLocation where Location='Loc2'
> select Item from ItemLocation where Location='Loc3'
> But i don't know how to join three query in above, so the result will
> become like this
> Loc1 Loc2 Loc3
> A B D
> C E
> F
> Location in ItemLocation is fix. Only three locations (Loc1, Loc2 and
> Loc3).
> Thanks for your help.
>
Subscribe to:
Posts (Atom)