SQL CASE exist then value

Question

I'm working on a SQL query like that :

PROC SQL;
   CREATE TABLE WORK.test AS 
   SELECT 
      ARTICLES.sku,
      ARTICLES.family_set,
      ARTICLES.classe_article,
      CASE 
         WHEN EXISTS (SELECT CATALOG_PAGE2 FROM ODS.ARTICLECOMPANY14 WHERE ODS_ARTICLECOMPANY14.ITEMNUM = ARTICLES.sku)
         THEN "Get the catalog_page2 value"
         ELSE '0'
      END AS pag_cat_mega
   FROM WORK.ARTICLES AS ARTICLES;
QUIT;

And I'm failling in the "Then" statement, I try several thing to replace the "Get the catalog_page2 value" but nothing work (like field name)

I think it's cause "exist" say yes / no but didn't know what to do...

Perhaps there is an other solution ?

I try a join, but I loose value cause there is less value in my table ARTICLECOMPANY14 than into ARTICLES.

My goal is to get the value if exist, else put a "0".

Thanks for your help.

I'm using SAS enterprise guide but i think it's pretty similar to sql server — Mitchum, Jul 13 '15 at 14:14

Gordon Linoff · Accepted Answer · 2015-07-13T14:36:24.753

proc sql supports exists. I suspect the problem might be the double quotes:

PROC SQL;
    CREATE TABLE WORK.test AS
        SELECT a.sku, a.family_set, a.classe_article,
               (CASE WHEN EXISTS (SELECT 1
                                  FROM ODS.ARTICLECOMPANY14 oc
                                  WHERE oc.ITEMNUM = a.sku)
                     THEN 'Get the catalog_page2 value'
                     ELSE '0'
                END) AS pag_cat_mega
        FROM WORK.ARTICLES a ;

EDIT:

I suspect you just want a LEFT JOIN:

PROC SQL;
    CREATE TABLE WORK.test AS
        SELECT a.sku, a.family_set, a.classe_article, oc.catalog_page2
        FROM WORK.ARTICLES a LEFT JOIN
             ODS.ARTICLECOMPANY14 oc
             ON oc.ITEMNUM = a.sku;

If you don't want it to show up as NULL, then use coalesce() in the SELECT, either:

COALESCE(oc.catalog_page2, 0)

or

COALESCE(oc.catalog_page2, '0')

depending on the type of the column.

my exist function work if i put text in then statement but not if i whant to get the value. thanks — Mitchum, Jul 13 '15 at 14:30

Ionic · Answer 2 · 2015-07-13T15:45:15.080

1

Well I would suggest you to reform it to a LEFT JOIN and an ISNULL function:

SELECT ARTICLES.sku,
    ARTICLES.family_set,
    ARTICLES.classe_article,
    COALESCE(ACompany.CATALOG_PAGE2,0) AS pag_cat_mega
FROM WORK.ARTICLES AS ARTICLES
LEFT JOIN  ODS.ARTICLECOMPANY14 AS ACompany
        ON ACompany.ITEMNUM = ARTICLES.sku;

edited Jul 13 '15 at 15:45

answered Jul 13 '15 at 14:22

Ionic

3,884
1
12
33

yes that's it, the "isnull" function didn't work in SAS but dit it with a join. thanks – Mitchum Jul 13 '15 at 14:44
Ah ok, your using SAS. Didn't seen that. Well `COALESCE` is supported, right? In this case we can use `COALESCE`. :-) – Ionic Jul 13 '15 at 15:45

Hector · Answer 3 · 2015-07-13T14:27:56.193

0

PROC SQL;
CREATE TABLE WORK.test 
AS SELECT ARTICLES.sku
, ARTICLES.family_set
, ARTICLES.classe_article
, CASE WHEN EXISTS
            BEGIN 
            (
            SELECT CATALOG_PAGE2 
            FROM ODS.ARTICLECOMPANY14 
            WHERE ODS_ARTICLECOMPANY14.ITEMNUM = ARTICLES.sku
            )
            THEN PRINT 'Get the catalog_page2 value' 
            END
       ELSE '0'
 END AS pag_cat_mega
FROM WORK.ARTICLES AS ARTICLES ;
QUIT;

Also, for your THEN clause, are you looking for it to ECHO/PRINT "Get the Catalog_page2" Value? If not, you need to create a variable to pull that Catalog_2 value and dont forget your single quotes.

I hope this at least helps point you in the right direction.

edited Jul 13 '15 at 14:27

answered Jul 13 '15 at 14:20

Hector

125
6

yes i'm looking to echo / print the "catalog_page2" fied value in my then cause and not that string... Thanks – Mitchum Jul 13 '15 at 14:24
Ok great, if you are looking to print you need to use T-SQL print command – Hector Jul 13 '15 at 14:26
PRINT 'INSERT MESSAGE HERE' or PRINT @Local_Variable – Hector Jul 13 '15 at 14:27

score 0 · Answer 4 · answered Aug 06 '21 at 09:39

I might be a little too late but here's the solution:

PROC SQL;
   CREATE TABLE WORK.test AS 
   SELECT 
      ARTICLES.sku,
      ARTICLES.family_set,
      ARTICLES.classe_article,
      CASE 
         WHEN EXISTS (SELECT CATALOG_PAGE2 FROM ODS.ARTICLECOMPANY14 WHERE ODS_ARTICLECOMPANY14.ITEMNUM = ARTICLES.sku)
         THEN (SELECT CATALOG_PAGE2 FROM ODS.ARTICLECOMPANY14 WHERE ODS_ARTICLECOMPANY14.ITEMNUM = ARTICLES.sku)
         ELSE '0'
      END AS pag_cat_mega
   FROM WORK.ARTICLES AS ARTICLES;
QUIT;

SQL CASE exist then value

4 Answers4