CODEWITHSUNDEEP
sqlsascaseexistsproc-sql
Mitchum
Mitchum

Reputation: 107

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.

Upvotes: 0

Views: 9262

Answers (4)

Aditya Pratama
Aditya Pratama

Reputation: 692

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;

Upvotes: 0

Ionic
Ionic

Reputation: 3935

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;

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269753

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.

Upvotes: 1

Hector
Hector

Reputation: 125

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.

Upvotes: 0

Related Questions