November 6, 2017 at 3:01 am
I'm trying to null the values for BOMSEQ_0, CPNITMREF_0, YREF_0 and TEXTE_0 from both parts when the ITMBPS details duplicate the values down.
SELECT B.BOMSEQ_0, case when B1.ITMREF_0 IS NOT NULL then NULL else B.CPNITMREF_0 end as CPNITREF, case when B1.ITMREF_0 IS NOT NULL then NULL else B.YREF_0 end as YREF, case when B1.ITMREF_0 IS NOT NULL Then NULL else CONVERT(varchar(255), T.TEXTE_0) end as TEXTE, case when B1.CPNITMREF_0 IS NOT NULL then NULL else I.YSTOFCY_0 end as YSTOFCY, case when B1.CPNITMREF_0 IS NOT NULL then NULL else I.PIO_0 end as PIO, case when B1.CPNITMREF_0 IS NOT NULL then NULL else I.BPSNUM_0 end as BPSNUM, case when B1.CPNITMREF_0 IS NOT NULL then NULL else I.ITMREFBPS_0 end as ITMREFBPS,
B1.BOMSEQ_0, B1.CPNITMREF_0, B1.YREF_0, CONVERT(varchar(255), T.TEXTE_0) as TEXTE2, I.YSTOFCY_0, I.PIO_0, BPSNUM_0, I.ITMREFBPS_0
FROM [BOMD] B
INNER JOIN [BOMD] B1
ON B.CPNITMREF_0=B1.ITMREF_0
AND B.BOMALT_0=B1.BOMALT_0
LEFT JOIN [TEXCLOB] T
ON B1.BOMTEXNUM_0=T.CODE_0
INNER JOIN [ITMBPS] I
ON B1.CPNITMREF_0=I.ITMREF_0
WHERE B.ITMREF_0='50'
and B.BOMALT_0= 1
UNION ALL
SELECT B.BOMSEQ_0, B.CPNITMREF_0, B.YREF_0, CONVERT(varchar(255), T.TEXTE_0), I.YSTOFCY_0, I.PIO_0, I.BPSNUM_0, I.ITMREFBPS_0,
NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL
FROM [BOMD] B
LEFT JOIN [TEXCLOB] T
ON B.BOMTEXNUM_0=T.CODE_0
INNER JOIN [ITMBPS] I
ON B.CPNITMREF_0=I.ITMREF_0
WHERE B.ITMREF_0='50'
and B.BOMALT_0= 1
ORDER BY B.BOMSEQ_0, B1.BOMSEQ_0, YSTOFCY, YSTOFCY_0, PIO, PIO_0
I've tried using lag but I can't seem to get it to work.
Example data and results (shortened slightly to fit but hopefully you get the idea).
BOMD
ITMREF_0 CPNITMREF_0 BOMSEQ_0 BOMALT_0 YREF_0 BOMTEXNUM
50 120-001 1 1 F1 1
50 112-001 2 1 F2
50 110-001 3 1 F3
112-001 113-001 1 1 P2
112-001 113-002 2 1 P3 2
50 120-001 1 2 D1
50 112-001 2 2 D2
50 110-001 3 2 D3
112-001 113-001 1 2 Q2
112-001 113-004 2 2 Q3
TEXTCLOB
CODE_0 TEXTE_0
1 F1-10
2 P3-10
ITMBPS
ITMREF_0 YSTOFCY_0 BPSNUM_0 PIO_0
120-001 UK UK001 0
120-001 GER GER001 2
112-001 UK UK002 0
112-001 GER GER002 2
110-001 GER GER002 0
113-001 GER GER002 0
113-001 UK UK001 2
113-002 UK UK003 0
Desired Results
BOMSEQ CPNITMREF YREF BOMTEXNUM BPSNUM BOMSEQ CPNITMREF YREF BTEX BPSNUM
1 120-001 F1 F1-10 UK001 NULL NULL NULL NULL NULL
NULL NULL NULL NULL GER001 NULL NULL NULL NULL NULL
2 112-001 F2 NULL UK002 NULL NULL NULL NULL NULL
NULL NULL NULL NULL GER002 NULL NULL NULL NULL NULL
NULL NULL NULL NULL NULL 1 113-001 P2 NULL GER002
NULL NULL NULL NULL NULL NULL NULL NULL NULL UK001
NULL NULL NULL NULL NULL 2 113-002 P3 P3-10 UK003
3 110-001 F3 NULL GER002 NULL NULL NULL NULL NULL
Any ideas?
Thanks
November 6, 2017 at 3:20 am
Could you please provide your data in a consumable format please? Have a look at the link in my signature on details on how to achieve this. You're much more likely to get an answer to your question if people don't have to do the work of building your data and make assumptions on your data types.
Thanks,.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
November 6, 2017 at 7:26 am
I solved this by using a case to compare the column against a lagged version.
For example: case when B.CPNITMREF_0=LAG(B.CPNITMREF_0,1,NULL) over (order by B.BOMSEQ_0, YSTOFCY_0, PIO_0, I.BPSNUM_0) then NULL else B.CPNITMREF_0 end as CPNITMREF
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy