SQL - How to replace certain repeated values with null

  • 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

  • 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

  • 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