March 3, 2010 at 11:42 am
Hi,
I need some help to accomplish what I thought would be a fairly simple task, but now I'm not so sure. I need to return a specific set of barrels for each inventory transaction. Each transaction will have a different Barrel Count, so I created a virtual table to hold the barrel count for each transaction, but I am not able to use the TOP clause as I hoped. In the below example, I'm trying to return the newest barrels, but I am not allowed to reference the column inside the TOP clause. Can anyone direct me to an alternate method to accomplish this?
SELECT TOP (BrcBarrelCount)
IntKey,
BrlKey
FROMInventoryTran
INNER JOIN
@BarrelCount ON (BrcTranID= IntTranIDAND
BrcLine= IntLine)
ORDER BY BrlDate DESC
The reference to column "BrcBarrelCount" is not allowed in the argument of the TOP clause. Only references to columns at an outer scope or standalone expressions and subqueries are allowed here.
March 3, 2010 at 11:58 am
I think dynamic SQL is your solution here, unless you can set a variable ahead of your query.
March 3, 2010 at 12:10 pm
The value for the top clause can be a variable .
So
Declare @mytop int
select @mytop = max(brlcount) from ...
select top ( @mytop ) ...
no dynamic sql needed !
You can also use a ranking function so you can join with that.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
March 3, 2010 at 12:15 pm
Would you please provide table def, some sample data and expected result? I'm sure it's possible to do it without dynamic sql but I'd like to have something to play around with... (side note: ready to use sample data as described in the first link in my signature preferred... 😉 )
March 3, 2010 at 1:00 pm
Here are some very basic schemas with a small amount of data:
CREATE TABLE testInventoryTran(
IntTranIDVARCHAR(10)NOT NULL,
IntLineINTEGERNOT NULL,
IntLotIDVARCHAR(10)NOT NULL)
CREATE TABLE testBarrelCount (
BrcTranIDVARCHAR(10)NOT NULL,
BrcLineINTEGERNOT NULL,
BrcBarrelCountINTEGERNOT NULL)
CREATE TABLE testBarrels (
BrlBarrelIDVARCHAR(10)NOT NULL,
BrlLotIDVARCHAR(10)NOT NULL,
BrlDateDATETIMENOT NULL)
INSERT INTO testInventoryTran VALUES ('Tran 1',1,'Lot A')
INSERT INTO testInventoryTran VALUES ('Tran 1',2,'Lot B')
INSERT INTO testInventoryTran VALUES ('Tran 2',1,'Lot C')
INSERT INTO testBarrelCount VALUES ('Tran 1',1,1)
INSERT INTO testBarrelCount VALUES ('Tran 1',2,2)
INSERT INTO testBarrelCount VALUES ('Tran 2',1,3)
INSERT INTO testBarrels VALUES ('Brl 01','Lot A','2010/01/01')
INSERT INTO testBarrels VALUES ('Brl 02','Lot A','2010/01/02')
INSERT INTO testBarrels VALUES ('Brl 03','Lot A','2010/01/03')
INSERT INTO testBarrels VALUES ('Brl 04','Lot A','2010/01/04')
INSERT INTO testBarrels VALUES ('Brl 05','Lot B','2010/01/04')
INSERT INTO testBarrels VALUES ('Brl 06','Lot B','2010/01/05')
INSERT INTO testBarrels VALUES ('Brl 07','Lot B','2010/01/06')
INSERT INTO testBarrels VALUES ('Brl 08','Lot B','2010/01/07')
INSERT INTO testBarrels VALUES ('Brl 09','Lot C','2010/01/07')
INSERT INTO testBarrels VALUES ('Brl 10','Lot C','2010/01/08')
INSERT INTO testBarrels VALUES ('Brl 11','Lot C','2010/01/09')
INSERT INTO testBarrels VALUES ('Brl 12','Lot C','2010/01/10')
--QUERY
SELECT TOP (BrcBarrelCount)
IntTranID,
IntLine,
BrlBarrelID
FROMtestInventoryTran
INNER JOIN
testBarrels ON (BrlLotID = IntLotID)
INNER JOIN
testBarrelCount ON (BrcTranID= IntTranIDAND
BrcLine= IntLine)
ORDER BY BrlDate DESC
--EXPECTED RESULTS FROM QUERY
Tran 1, 1, Brl 04
Tran 1, 2, Brl 08
Tran 1, 2, Brl 07
Tran 2, 1, Brl 12
Tran 2, 1, Brl 11
Tran 2, 1, Brl 10
March 3, 2010 at 1:24 pm
First of all: thank your for taking the time to set up the sample data!!
Made it really easy to work on! Great job :Wow:
Here's what I came up with:
;WITH cte AS -- number the barrels per Inttranid
(
SELECT
inv.Inttranid,
inv.IntLine,
brl.brlbarrelid,
ROW_NUMBER() OVER(PARTITION BY IntTranid,intLine ORDER BY brlbarrelId DESC ) ROW
FROM testInventoryTran inv
INNER JOIN testBarrels brl
ON inv.intlotid=brl.BrlLotID
)
SELECT -- select the top x barrels as per brcbarrelcount
cte.Inttranid,
cte.IntLine,
cte.brlbarrelid
FROM cte
INNER JOIN testBarrelCount brc
ON brc.brctranid=cte.Inttranid
AND brc.brcLine=cte.IntLine
WHERE cte.row <= brc.brcbarrelcount
March 3, 2010 at 2:10 pm
Thanks! That works great on the sample. Now I'll try to implement in with my "real" tables and data and see what happens.:-D
March 3, 2010 at 2:19 pm
here's another version ...
Select *
from (
SELECT
rank () over( partition by IntTranID, IntLine order by IntTranID, IntLine , BrlDate DESC) as RNK
, BrcBarrelCount as MyTop
, *
FROM @testInventoryTran
INNER JOIN @testBarrels
ON ( BrlLotID = IntLotID )
INNER JOIN @testBarrelCount
ON (
BrcTranID = IntTranID
AND BrcLine = IntLine
)
) A
Where RNK <= MyTop
ORDER BY IntTranID
, IntLine
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
March 3, 2010 at 2:27 pm
That works, too! Thanks 😀
March 3, 2010 at 2:36 pm
If you compare ALZDBA's solution and mine you should focus on the different interpretation of where the ORDER BY section would apply to.
Also you'll see a difference if you have tie values in your data (to compare both solutions you'd need two identical rows in your testBarrels table. E.g.
INSERT INTO testBarrels VALUES ('Brl 04','Lot A','2010/01/04')
INSERT INTO testBarrels VALUES ('Brl 04','Lot A','2010/01/04')
Give it a try and you'll see the difference...
@ALZDA:
Is there any specific reason to repeat the columns of PARTITION BY (IntTranID, IntLine) in the ORDER BY section? I usually don't do that... Juste being curious...
March 5, 2010 at 12:23 am
hmmm ... must be some of my bad habits 😉
I haven't used ranking functions that much, so they don't come loosely out of my sleeves :Whistling:
I still need to put some performance testing time in it, to get a little at ease with them after all.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
March 8, 2010 at 6:29 am
APPLY makes this easy and intuitive:
SELECT IT.IntTranID,
IT.IntLine,
iTVF.BrlBarrelID
FROM testInventoryTran IT
JOIN testBarrelCount TBC
ON TBC.BrcTranID = IT.IntTranID
AND TBC.BrcLine = IT.IntLine
CROSS
APPLY (
-- Magic goes here:
SELECT TOP (TBC.BrcBarrelCount)
TB.BrlBarrelID,
TB.BrlDate
FROM testBarrels TB
WHERE TB.BrlLotID = IT.IntLotID
ORDER BY
TB.BrlDate DESC
) iTVF
ORDER BY
IT.IntTranID,
IT.IntLine,
iTVF.BrlDate DESC;
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply