sql - How to convert query to Oracle with select inside select

Question

I need to convert this query from MsSql to Oracle syntax:

SELECT CATEGORY_ID
    ,CASE 
        WHEN EXISTS (
                SELECT *
                FROM DA_CATEGORY C
                WHERE E.CATEGORY_ID = C.CATEGORY_ID
                )
            THEN 'Exist'
        ELSE 'Not Exist'
        END AS [Status]
FROM (
    VALUES ('CG0')
        ,('CG10')
        ,('CG100')
        ,('CG1000')
        ,('CG10000')
        ,('CG1100')
        ,('CG1200')
        ,('CG2600')
        ,('CG2700')
        ,('CG2800')
        ,('CG300')
        ,('CG3000')
        ,('CG500')
        ,('CG600')
        ,('CG6000')
        ,('CG700')
        ,('CG8')
        ,('CG800')
        ,('CG900')
        ,('CG1300')
        ,('CG1400')
        ,('CG1500')
        ,('CG1600')
        ,('CG1700')
        ,('CG1800')
        ,('CG1900')
        ,('CG2100')
        ,('CG2200')
        ,('CG2300')
        ,('CG2400')
        ,('CG2500')
        ,('CG2900')
        ,('CG20')
        ,('CG30')
        ,('CG40')
        ,('CG50')
        ,('CG60')
        ,('CG70')
        ,('CG80')
        ,('CG90')
        ,('CG11')
        ,('CG12')
        ,('CG13')
        ,('CG14')
        ,('CG15')
        ,('CG16')
        ,('CG17')
    ) E(CATEGORY_ID)

How do I do that?

Answer 1

Oracle does not support VALUES. One method is to construct the values using SELECT FROM DUAL:

SELECT CATEGORY_ID,
       (CASE WHEN EXISTS (SELECT 1 FROM DA_CATEGORY C WHERE E.CATEGORY_ID = C.CATEGORY_ID) THEN 'Exist' ELSE 'Not Exist'
        END) AS Status
FROM (SELECT 'CG0' as CATEGORY_ID FROM DUAL UNION ALL
      SELECT 'CG10' FROM DUAL UNION ALL
      . . . 
      SELECT 'CG17' FROM DUAL
     ) E(CATEGORY_ID);

If you already have the values in a table, then it is simpler to just reference the values in a query.