Help with Query
-
I'm having trouble coding for a specific scenario that's occurring. This query, run over a 2008 R2 instance, uses a linked server to hit a DB2 file. The script works except when the same SKU exists on multiple lines for a given order. When this scenario occurs the result set shows these as exceptions.
Any help would be appreciated.
Matt
with LX as
(
SELECT LORD AS ORD, RIGHT('0000' + CAST(LLINE AS VARCHAR(10)),4) AS LINE, LPROD AS SKU
FROM salsa.salsa1.lxf.ECLL01
WHERE LWHS='DC'
),
SCE AS
(
SELECT OD.ORDERKEY AS ORD, CASEWHEN OD.EXTERNLINENO LIKE '%[_]%' THEN LEFT(OD.EXTERNLINENO,4) ELSE RIGHT('0000' + OD.EXTERNLINENO,4) END AS LINE, OD.SKU AS SKU, REQUESTEDSHIPDATE
FROM SCPRD.wmwhse1.ORDERDETAIL OD
JOIN SCPRD.WMWHSE1.ORDERS O
ON OD.ORDERKEY=O.ORDERKEY
WHERE OD.STATUS <=92
)
SELECT 'DC' AS WHS,LX.ORD AS LX_ORD,LX.LINE AS LX_LINE,SCE.LINE AS SCE_LINE, LX.SKU AS LX_SKU, SCE.REQUESTEDSHIPDATE
FROM LX
JOIN SCE
ON
LX.ORD = SCE.ORD
ANDLX.SKU = SCE.SKU
AND LX.LINE <> SCE.LINE
ORDER BY SCE.REQUESTEDSHIPDATE, LX.ORD, LX.LINE
Current results:
-
mandrewthebarbarian (2/20/2014)
I'm having trouble coding for a specific scenario that's occurring. This query, run over a 2008 R2 instance, uses a linked server to hit a DB2 file. The script works except when the same SKU exists on multiple lines for a given order. When this scenario occurs the result set shows these as exceptions.Any help would be appreciated.
...
Help with what? Do you want the script to raise error? Ignore error?
What you would like to have as expected results?
-
The presented results are not exceptions that should be returned. How do I modify the query to exclude orders where the same SKU is present on multiple lines as long as the line numbers are the same in both cte result sets?
-
mandrewthebarbarian (2/20/2014)
The presented results are not exceptions that should be returned. How do I modify the query to exclude orders where the same SKU is present on multiple lines as long as the line numbers are the same in both cte result sets?That is possible. I can start from your current results:
DECLARE @yourcurrentresults TABLE (WHS char(2), LX_ORD int, LX_LINE char(4), SCE_LINE char(4), LX_SKU varchar(20), REQUESTSHIPDATE datetime)
INSERT @yourcurrentresults
VALUES ('DC',1062371,'0004','0005','SHGHA08TH','20140220 13:00:00')
,('DC',1062371,'0005','0004','SHGHA08TH','20140220 13:00:00')
,('DC',1062371,'0006','0007','SHGYA35','20140220 13:00:00')
,('DC',1062371,'0007','0006','SHGYA35','20140220 13:00:00')
,('DC',1062371,'0008','0009','SHWYA36','20140220 13:00:00')
,('DC',1062371,'0009','0008','SHWYA36','20140220 13:00:00')
;WITH dedupe
AS
(
SELECT YR.*, ROW_NUMBER() OVER (PARTITION BY WHS, LX_ORD, LX_SKU ORDER BY LX_LINE) RN
FROM @yourcurrentresults YR
)
SELECT WHS, LX_ORD, LX_LINE, SCE_LINE, LX_SKU, REQUESTSHIPDATE
FROM dedupe
WHERE RN = 1
ORDER BY WHS, LX_ORD, LX_LINE
It is possible to achieve the same dedupe earlier (just in your set of CTE's), but to help with it you should supply a bit more details in your post. Please read article under link at the bottom of my signature. It will explain what is usually expected in order to get most relevant and prompt help here.
-
This worked perfectly!
Thank you. It's greatly appreciated.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply