April 3, 2014 at 10:39 am
I feel like this should be easy but my T-SQL is a little rusty so I'm looking for suggestions. Hopefully, when it posts I won't lose my primitive formatting.
I've got three rows like this:
PONum POLineNum POSublineNum SublineInstance BillOfLading InvoiceNumber Vessel
1 1 1 1 XYZ NULL NULL
1 1 1 2 NULL ABC NULL
1 1 1 3 NULL NULL S.S. MINNOW
I want there to be one row:
1 1 1 XYZ ABC S.S. MINNOW
Is this a good scenario for a MERGE statement or some hybrid UNION. Trying to avoid RBAR. It seems like MERGE would get it done but I'm open to ideas.
"Beliefs" get in the way of learning.
April 3, 2014 at 10:51 am
Try using MIN() and group by PONum, POLineNum & POSublineNum.
April 3, 2014 at 1:24 pm
Straight forward using Window functions
DECLARE @POSTUFF TABLE
(
PONum INT NULL
,POLineNum INT NULL
,POSublineNum INT NULL
,SublineInstance INT NULL
,BillOfLading VARCHAR(10) NULL
,InvoiceNumber VARCHAR(10) NULL
,Vessel VARCHAR(25) NULL
);
INSERT INTO @POSTUFF
(
PONum
,POLineNum
,POSublineNum
,SublineInstance
,BillOfLading
,InvoiceNumber
,Vessel
)
VALUES
(1, 1, 1, 1, 'XYZ', NULL, NULL)
,(1, 1, 1, 2, NULL, 'ABC' ,NULL)
,(1, 1, 1, 3, NULL, NULL, 'S.S. MINNOW')
SELECT
PONum
,POLineNum
,POSublineNum
,SublineInstance
,BillOfLading
,InvoiceNumber
,Vessel
FROM
(
SELECT
ROW_NUMBER() OVER
(
PARTITION BY PONum,POLineNum,POSublineNum
ORDER BY (SELECT NULL)
) AS COL_RID
,PONum
,POLineNum
,POSublineNum
,FIRST_VALUE(SublineInstance) OVER
(
PARTITION BY PONum,POLineNum,POSublineNum
ORDER BY SublineInstance ASC
ROWS BETWEEN UNBOUNDED PRECEDING
AND UNBOUNDED FOLLOWING
) AS SublineInstance
,LAST_VALUE(BillOfLading) OVER
(
PARTITION BY PONum,POLineNum,POSublineNum
ORDER BY CASE
WHEN BillOfLading IS NULL THEN ''
ELSE BillOfLading
END ASC
ROWS BETWEEN UNBOUNDED PRECEDING
AND UNBOUNDED FOLLOWING
) AS BillOfLading
,LAST_VALUE(InvoiceNumber) OVER
(
PARTITION BY PONum,POLineNum,POSublineNum
ORDER BY CASE
WHEN Vessel IS NULL THEN ''
ELSE InvoiceNumber
END
ROWS BETWEEN UNBOUNDED PRECEDING
AND UNBOUNDED FOLLOWING
) AS InvoiceNumber
,LAST_VALUE(Vessel) OVER
(
PARTITION BY PONum,POLineNum,POSublineNum
ORDER BY CASE
WHEN Vessel IS NULL THEN ''
ELSE Vessel
END
ROWS BETWEEN UNBOUNDED PRECEDING
AND UNBOUNDED FOLLOWING
) AS Vessel
FROM @POSTUFF
) AS X WHERE X.COL_RID = 1;
😎
April 3, 2014 at 1:41 pm
Eirikur Eiriksson (4/3/2014)
Straight forward using Window functions
Isn't that over complicating something that could be achieved like this?
SELECT PONum
,POLineNum
,POSublineNum
,MIN(SublineInstance) AS SublineInstance
,MIN(BillOfLading) AS BillOfLading
,MIN(InvoiceNumber) AS InvoiceNumber
,MIN(Vessel) AS Vessel
FROM POSTUFF
GROUP BY PONum
,POLineNum
,POSublineNum;
What would be the difference or how should it be an advantage? (It's an honest question because I haven't played much with windowing functions)
April 3, 2014 at 2:04 pm
Luis Cazares (4/3/2014)
Eirikur Eiriksson (4/3/2014)
Straight forward using Window functionsIsn't that over complicating something that could be achieved like this?
SELECT PONum
,POLineNum
,POSublineNum
,MIN(SublineInstance) AS SublineInstance
,MIN(BillOfLading) AS BillOfLading
,MIN(InvoiceNumber) AS InvoiceNumber
,MIN(Vessel) AS Vessel
FROM POSTUFF
GROUP BY PONum
,POLineNum
,POSublineNum;
What would be the difference or how should it be an advantage? (It's an honest question because I haven't played much with windowing functions)
In short, the group by is relying on the MIN function ignoring NULL values, when the Window function version allows for full control. Or rather, retains the details.
Each column has its own grouping and ordering, which can be specified by (almost) any condition within the set.
Your query performs better as it is, about 3 times I would guess. But when the logic is more complex than just ignoring NULL values, it looses the advantage.
Hope this makes sense, feel free to pop me questions on it, writing a little peace on the subjects.
😎
April 3, 2014 at 2:42 pm
I tried the MIN thing and it wasn't doing what I wanted because there were some empty strings rather than NULLS in the data but when I changed it to MAX it worked like a champ filtering out empty strings and NULL's and leaving me with the data set I needed which I subsequently aggregate at the PO_LINE level.
Thanks for all your suggestions. I had forgotten that you can use MIN and MAX against string data in certain scenarios.
"Beliefs" get in the way of learning.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply