Mitchum Mitchum - 4 months ago 13
SQL Question

SQL CASE exist then value

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.

Answer

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.