IF EXISTS + temp table or CTE

Question

IF EXISTS + temp table or CTE

JeremyU

SSChasing Mays

Points: 637
More actions
February 16, 2023 at 6:42 pm

#4150683
Hello everyone,
I have a question.
I have a code where I use IF exists to compare items that exist in one table namely snapshot but don't exist in the table called udt. For this purpose I use IF EXISTS clause.
It works just fine. It does return ' 'All items from snapshot exist in [udt]' if items exist in both tables, and returns list of items if items from snapshot don't exist in udt.
However, I need an enhancement and take the list and compare against list of items from another table. For this, I wanted to take the ouput of this into CTE or temp table. However, when I do this I get errors:
Error when I try to add CTE at the beginning

When I try to add a temp table . I keep receiving an error.
Pretty much full code is below. Does someone know how I can add the code into CTE or temp table?
```
If Exists (

SELECT distinct UPC,  U_PUBLISHER_TYPE
FROM [snapshot]
LEFT OUTER JOIN [item] 
ON [UPC]=item.[ITEM]
WHERE item.U_PUBLISHER_TYPE IN ('PRPG', 'RMSP', 'RDSP')

EXCEPT

SELECT distinct dep.[ITEM],  U_PUBLISHER_TYPE
FROM [udt] dep
LEFT OUTER JOIN [item] item 
ON dep.[ITEM]=item.[ITEM]
WHERE item.U_PUBLISHER_TYPE IN ('PRPG', 'RMSP', 'RDSP') 
)

 (

SELECT distinct UPC,  U_PUBLISHER_TYPE
FROM [snapshot]
LEFT OUTER JOIN [item] item 
ON [UPC]=item.[ITEM]
WHERE item.U_PUBLISHER_TYPE IN ('PRPG', 'RMSP', 'RDSP')

EXCEPT

SELECT distinct dep.[ITEM],  U_PUBLISHER_TYPE
FROM [udt] dep
LEFT OUTER JOIN [item] item 

ON dep.[ITEM]=item.[ITEM]

WHERE item.U_PUBLISHER_TYPE IN ('PRPG', 'RMSP', 'RDSP') 
)

Else

 SELECT 'All  items from snapshot exist in [udt]' AS 'UPC'
```

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Jonathan AC Roberts SSCoach Points: 18263 More actions · Answer 1

Not sure what you are trying to do but you could put the results of that query into a temp table like this:

DROP TABLE IF EXISTS #temp;
CREATE TABLE #temp(UPC int, U_PUBLISHER_TYPE varchar(20));

;WITH Results AS 
(
    SELECT distinct UPC, U_PUBLISHER_TYPE
      FROM [snapshot]
      LEFT JOIN [item] item 
             ON [UPC]=item.[ITEM]
     WHERE item.U_PUBLISHER_TYPE IN ('PRPG', 'RMSP', 'RDSP')
     EXCEPT
     SELECT distinct dep.[ITEM],  U_PUBLISHER_TYPE
       FROM [udt] dep
       LEFT JOIN [item] item 
              ON dep.[ITEM]=item.[ITEM]
      WHERE item.U_PUBLISHER_TYPE IN ('PRPG', 'RMSP', 'RDSP') 
)
INSERT INTO #temp(UPC, U_PUBLISHER_TYPE)
SELECT UPC, U_PUBLISHER_TYPE
FROM Results
;

drew.allen SSC Guru Points: 76944 More actions · Answer 2

A CTE is part of data manipulation language (DML), specifically an INSERT, UPDATE, SELECT, or DELETE statement. An IF...THEN...ELSE is a workflow statement. You CANNOT have a workflow statement in the middle of a DML statement, because that would violate the ACID conditions (atomicity, consistency, isolation, and durability) of a transaction.

My best guess over what you want is something like the following.

If Exists (

SELECT distinct UPC,  U_PUBLISHER_TYPE
FROM [snapshot]
LEFT OUTER JOIN [item] 
ON [UPC]=item.[ITEM]
WHERE item.U_PUBLISHER_TYPE IN ('PRPG', 'RMSP', 'RDSP')

EXCEPT

SELECT distinct dep.[ITEM],  U_PUBLISHER_TYPE
FROM [udt] dep
LEFT OUTER JOIN [item] item 
ON dep.[ITEM]=item.[ITEM]
WHERE item.U_PUBLISHER_TYPE IN ('PRPG', 'RMSP', 'RDSP') 
)
; WITH results AS
 (

SELECT distinct UPC,  U_PUBLISHER_TYPE
FROM [snapshot]
LEFT OUTER JOIN [item] item 
ON [UPC]=item.[ITEM]
WHERE item.U_PUBLISHER_TYPE IN ('PRPG', 'RMSP', 'RDSP')

EXCEPT

SELECT distinct dep.[ITEM],  U_PUBLISHER_TYPE
FROM [udt] dep
LEFT OUTER JOIN [item] item 

ON dep.[ITEM]=item.[ITEM]

WHERE item.U_PUBLISHER_TYPE IN ('PRPG', 'RMSP', 'RDSP') 
)
/*  optional INSERT, UPDATE, or DELETE  clause here  */
SELECT UPC, U_PUBLISHER_TYPE
FROM results
/*  the rest of you query here  */

Else

 SELECT 'All  items from snapshot exist in [udt]' AS 'UPC'

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Ed B Hall of Fame Points: 3616 More actions · Answer 3

This is a variation of the above. I think your left joins are invalidated by the WHERE filter on the item table, so add the filter to the join or change them to inner joins. EXCEPT is already distinct, so it's not needed. You could also use a select into, rather than create the temp table, but if the UPC column is numeric, the second insert will fail unless you create it with a varchar data type. I don't think it is necessary to check whether the except returns data more than once. If it does return data there will be rows in the table, if not there wont.

CREATE TABLE #temp
(   UPC VARCHAR(100),
    U_PUBLISHER_TYPE VARCHAR(100)
)

INSERT #temp (UPC, U_PUBLISHER_TYPE)
SELECT s.UPC,  i.U_PUBLISHER_TYPE
FROM dbo.[snapshot] AS s
LEFT OUTER JOIN dbo.item AS i ON s.UPC =i.ITEM
    AND i.U_PUBLISHER_TYPE IN ('PRPG', 'RMSP', 'RDSP')
EXCEPT
SELECT d.ITEM,  i.U_PUBLISHER_TYPE
FROM dbo.udt AS d 
LEFT OUTER JOIN dbo.item AS i ON d.ITEM = i.ITEM
    AND i.U_PUBLISHER_TYPE IN ('PRPG', 'RMSP', 'RDSP') 


IF NOT EXISTS (SELECT 1 FROM #temp)
  BEGIN
    INSERT #temp (UPC)
    VALUES ('All  items from snapshot exist in [udt]')
  END


SELECT *
FROM #temp
DROP TABLE IF EXISTS #temp

This reply was modified 1 year, 2 months ago by Ed B. Reason: Join fix

kaj SSCommitted Points: 1580 More actions · Answer 4

Or you could dispense with some paranteses, like this:

IF EXISTS (

SELECT DISTINCT UPC,  U_PUBLISHER_TYPE
FROM [snapshot]
LEFT OUTER JOIN [item] 
ON [UPC]=item.[ITEM]
WHERE item.U_PUBLISHER_TYPE IN ('PRPG', 'RMSP', 'RDSP')

EXCEPT

SELECT DISTINCT dep.[ITEM],  U_PUBLISHER_TYPE
FROM [udt] dep
LEFT OUTER JOIN [item] item 
ON dep.[ITEM]=item.[ITEM]
WHERE item.U_PUBLISHER_TYPE IN ('PRPG', 'RMSP', 'RDSP') 
)


SELECT DISTINCT UPC,  U_PUBLISHER_TYPE
FROM [snapshot]
LEFT OUTER JOIN [item] item 
ON [UPC]=item.[ITEM]
WHERE item.U_PUBLISHER_TYPE IN ('PRPG', 'RMSP', 'RDSP')

EXCEPT

SELECT DISTINCT dep.[ITEM],  U_PUBLISHER_TYPE
FROM [udt] dep
LEFT OUTER JOIN [item] item 

ON dep.[ITEM]=item.[ITEM]

WHERE item.U_PUBLISHER_TYPE IN ('PRPG', 'RMSP', 'RDSP') 


ELSE

 SELECT 'All  items from snapshot exist in [udt]' AS 'UPC'

or you could replace the ( with a BEGIN and ) with a END, like so:

IF EXISTS (
    SELECT DISTINCT UPC,  U_PUBLISHER_TYPE
    FROM [snapshot]
    LEFT OUTER JOIN [item] 
    ON [UPC]=item.[ITEM]
    WHERE item.U_PUBLISHER_TYPE IN ('PRPG', 'RMSP', 'RDSP')

    EXCEPT

    SELECT DISTINCT dep.[ITEM],  U_PUBLISHER_TYPE
    FROM [udt] dep
    LEFT OUTER JOIN [item] item 
    ON dep.[ITEM]=item.[ITEM]
    WHERE item.U_PUBLISHER_TYPE IN ('PRPG', 'RMSP', 'RDSP') 
)

BEGIN
    SELECT DISTINCT UPC,  U_PUBLISHER_TYPE
    FROM [snapshot]
    LEFT OUTER JOIN [item] item 
    ON [UPC]=item.[ITEM]
    WHERE item.U_PUBLISHER_TYPE IN ('PRPG', 'RMSP', 'RDSP')

    EXCEPT

    SELECT DISTINCT dep.[ITEM],  U_PUBLISHER_TYPE
    FROM [udt] dep
    LEFT OUTER JOIN [item] item 

    ON dep.[ITEM]=item.[ITEM]

    WHERE item.U_PUBLISHER_TYPE IN ('PRPG', 'RMSP', 'RDSP') 
END

ELSE

 SELECT 'All  items from snapshot exist in [udt]' AS 'UPC'

The BEGIN END isn't strictly necessary, because you only execute one statement on each outcome of the IF condition, but many people like to use it that way, all the same. Either way, indentation is your friend. 🙂

JeremyU SSChasing Mays Points: 637 More actions · Answer 5

drew.alen

It doesn't want to accept the code in the middle with ;WITH clause

issue_5

JeremyU SSChasing Mays Points: 637 More actions · Answer 6

Jonathan AC Roberts I think yours work. Thank you.

Can I ask do you know guys what am I doing wrong?

I am trying to detect if items from the table TEMPLATE match what is in this temp table and if they are there return them.

It is a continuation of Jonathan's code.

select ITEM
      ,[ALTSRCPENALTY]
from [TEMPLATE] a,
     [ITEM] B
where ITEM in ( SELECT UPC
FROM #temp)

Jonathan AC Roberts SSCoach Points: 18263 More actions · Answer 7

JeremyU wrote:

Jonathan AC Roberts I think yours work. Thank you.
Can I ask do you know guys what am I doing wrong?
I am trying to detect if items from the table TEMPLATE match what is in this temp table and if they are there return them.
It is a continuation of Jonathan's code.
select ITEM
      ,[ALTSRCPENALTY]
from [TEMPLATE] a,
     [ITEM] B
where ITEM in ( SELECT UPC
FROM #temp)

I think you can do it all in one statement:

;WITH Results AS 
(
    SELECT distinct UPC, item.U_PUBLISHER_TYPE
      FROM [snapshot]
      LEFT JOIN [item] item 
             ON [UPC]=item.[ITEM]
     WHERE item.U_PUBLISHER_TYPE IN ('PRPG', 'RMSP', 'RDSP')
     EXCEPT
     SELECT distinct dep.[ITEM],  item.U_PUBLISHER_TYPE
       FROM [udt] dep
       LEFT JOIN [item] item 
              ON dep.[ITEM]=item.[ITEM]
      WHERE item.U_PUBLISHER_TYPE IN ('PRPG', 'RMSP', 'RDSP') 
)
SELECT *
  FROM TEMPLATE a
 INNER JOIN [item] item 
         ON a.UPC = item .ITEM
 WHERE EXISTS(SELECT *
                FROM Results r
               WHERE r.UPC = a.ITEM
                 AND r.U_PUBLISHER_TYPE = item.U_PUBLISHER_TYPE)
;

JeremyU SSChasing Mays Points: 637 More actions · Answer 8

Jonathan great. Thanks. I appreciate your help.

One more small question. This code basically compares items between ITEM/LOC and temp and returns if there is a mutual match between temp table that we created and ITEM/LOC tables, am I correct?

SELECT ITEM,LOC,'0' OH, CONVERT (date, GETDATE())  OHPOST
from [ITEM] a, [LOC] b
where b.loc in ('DC08','DCSS')
--and item in ( )
 AND EXISTS(SELECT *
                FROM #temp
               WHERE cast(UPC as varchar)=cast( a.ITEM as varchar)
                )

Jonathan AC Roberts SSCoach Points: 18263 More actions · Answer 9

JeremyU wrote:

Jonathan great. Thanks. I appreciate your help.
One more small question. This code basically compares items between ITEM/LOC and temp and returns if there is a mutual match between temp table that we created and ITEM/LOC tables, am I correct?
SELECT ITEM,LOC,'0' OH, CONVERT (date, GETDATE())  OHPOST
from [ITEM] a, [LOC] b
where b.loc in ('DC08','DCSS')
--and item in ( )
 AND EXISTS(SELECT *
                FROM #temp
               WHERE cast(UPC as varchar)=cast( a.ITEM as varchar)
                )

I only took a guess.

I think your SQL really needs to join [ITEM] a and [LOC] b together.

I think you also need to join to both columns on the temp table.

So maybe something like:

SELECT ITEM,LOC,'0' OH, CONVERT (date, GETDATE())  OHPOST
from [ITEM] a, [LOC] b
where a.SomeCol = b.SomeOtherCol
  and b.loc in ('DC08','DCSS')
--and item in ( )
 AND EXISTS(SELECT *
                FROM #temp t
               WHERE cast(t.UPC as varchar)=cast( a.ITEM as varchar)
                 AND t.U_PUBLISHER_TYPE = a.U_PUBLISHER_TYPE
                )

I'm only guessing as I don't know enough about your tables or data or what you are trying to do.

JeremyU SSChasing Mays Points: 637 More actions · Answer 10

JeremyU

SSChasing Mays

Points: 637

February 18, 2023 at 12:06 am

#4151247

Thank you for your help.