How to match a CONCATENATED value to values in a temp table

Question

The database I’m working with is SQL Server 2022.

I have a table with item numbers like 1010080PC which ends with a C. I also have items like 1010080 or 1010080P (without the C at the end) and I want to add the item 1010080PC to the last column GuaranteeItem if the item number in ItemNr does not end with a C

MATNR	GuaranteeItem
1010080	1010080PC
1010080P	1010080PC
1010080PC	(empty)

As item 1010080PC already ends with a C, I do not want to enter anything in the last column.

The table has around 170,000 rows and of these rows approx. 1,700 have an item that ends with a C. I expect that around 3,400 rows have to be edited

This is what I want the result to be:

MATNR	GuaranteeItem
1010010
1010021
1010031P	1010031PC
1010031PC
1010040
1010061
1010070
1010080	1010080PC
1010080PC
1011010
1011021
1011030
1017511
1017530
1017531
1017592	1017592PC
1017592P	1017592PC
1017592PC
1017611
1017821
1017941
1017951
1017961	1017961PC
1017961P	1017961PC
1017961PC
1017981

I’ve been fighting with this for a while but I’ve not been able to find a working solution. What I’ve done is the following

I’ve made a TempTable with only the ItemNr that end with a ‘C’

SELECT DISTINCT
    prd.MARA.MATNR
    INTO prd.TempTable
FROM 
    prd.MARA
WHERE
prd.MARA.MATNR LIKE '%C'

Next I try to match the ItemNr + ‘%C’ to the ItemNr in the temp table. If the the ItemNr + ‘%C’ exists in the temp table then I add the ItemNr from the temp table to the column GuaranteeItem


SELECT DISTINCT
    prd.MARA.MATNR
    , CASE
        WHEN CONCAT(prd.MARA.MATNR, '%C') IN (SELECT prd.TempTable.MATNR FROM prd.TempTable) THEN prd.TempTable.MATNR
        WHEN prd.MARA.MATNR LIKE '%C' THEN '' 
        ELSE ''
        END AS GuaranteeItem
FROM
    prd.MARA
LEFT JOIN
    prd.TempTable ON prd.TempTable.MATNR = prd.MARA.MATNR

Somehow I can’t match WHEN CONCAT(prd.MARA.MATNR, '%C') IN (SELECT prd.TempTable.MATNR FROM prd.TempTable) THEN

Any advice to solve this would be very much appreciated.

In your results table you have lots of rows where MATNR doesn't end in C but GuaranteeItem is being left null. Can you explain what the logic is because this doesn't seem to be in line with the text of your question? Thanks — NickW, May 30 '23 at 15:42
Dear NickW, if it is not in line, I have not explain it correctly. E.g. itemnr 1017961 and 1017961P both have a correspondent item 1017961PC with end with a C, so for both 1017961 and 1017961P the 1017961PC has to added to the last column. Itemnr 1011010 doesn't have a itemnr wich end with a C. There is no like 1011010C so no value is added to the last column — Peter, May 30 '23 at 18:51

score 1 · Accepted Answer · answered May 30 '23 at 15:49

1

You can use the EXISTS operator within a case expression as the following:

SELECT MATNR,  
  CASE 
    WHEN
      EXISTS
        (
          SELECT 1 FROM tbl_name D
          WHERE D.MATNR = CONCAT(T.MATNR, 'PC') OR
                D.MATNR = CONCAT(T.MATNR, 'C')
        )
    THEN CONCAT(T.MATNR, IIF(RIGHT(T.MATNR, 1) = 'P', 'C','PC')) 
  ELSE '' END AS GuaranteeItem
FROM tbl_name T

Demo

answered May 30 '23 at 15:49

ahmed

9,071
3
9
22

1

Dear Ahmed, thank you for your solution. In the mean time I've come up with an other approach but yours is more elegant (I think) – Peter May 30 '23 at 19:16

gskarthikkn · Answer 2 · 2023-05-30T18:39:31.647

0

SELECT MATNR, 
    IIF(RIGHT(MATNR  , 1) = 'C', 
    '' , 
    IIF(RIGHT(MATNR  , 1)='P', CONCAT(MATNR  ,'C'),CONCAT(MATNR  ,'PC'))) GuaranteeItem FROM prd.MARA

edited May 30 '23 at 18:39

answered May 30 '23 at 18:35

gskarthikkn

89
4

Dear gskarthikkn, Your solution puts the value 1017961PC with the items 1017961 and 1017961P, which is correct. But it also add a value 1017611PC to 1017611 while the value 1017611PC does not exists in the table. – Peter May 30 '23 at 19:23

score 0 · Answer 3 · answered May 30 '23 at 19:26

I've come up with an other approach which also works.

/* Create CTE with all MATNR that are CautieArtikel*/
WITH CautieArtikel AS (
    SELECT
        prd.MARA.MATNR AS MATNR1
    FROM 
        prd.MARA
    WHERE 
        prd.MARA.MATNR LIKE '%C'
)

/* Left join CTE by extracting the first part of the MATNRs */
SELECT
    prd.MARA.MATNR
    , CASE 
        WHEN prd.MARA.MATNR = ca.MATNR1 THEN '' ELSE COALESCE(ca.MATNR1, '')
        END AS GuaranteeItem
FROM
    prd.MARA
LEFT JOIN 
    CautieArtikel AS ca ON prd.MARA.MATNR = LEFT(ca.MATNR1, LEN(prd.MARA.MATNR))

After 3 days f**king around, my daughter came up with (most) of this solution.

I want to thank everyone for the quick response and the effort.

How to match a CONCATENATED value to values in a temp table

3 Answers3