Himanshu Himanshu - 15 days ago 9
SQL Question

Missing keyword exception Oracle

Why this query returns missing keyword error with Oracle Database:

Note: Case condition is being used to select an appropriate where statement for WHERE condition. This is a mysql query which I am migrating to Oracle.

SELECT OFFICE_TYPE_CD, OFFICE_TYPE_DESC
FROM m_office_types
WHERE CASE
WHEN OFFICE_TYPE_CD IN (7)
THEN
office_level_cd < (SELECT DISTINCT office_level_cd
FROM m_office_types
WHERE office_type_cd = 7
AND RECORD_STATUS <> 'D')
ELSE
office_level_cd <= (SELECT DISTINCT office_level_cd
FROM m_office_types
WHERE office_type_cd = 7
AND RECORD_STATUS <> 'D')
END
AND RECORD_STATUS = 'C'
AND state_cd = 27
AND OFFICE_NAME IS NOT NULL

Answer

You probably want to write something like this:

SELECT OFFICE_TYPE_CD, OFFICE_TYPE_DESC
  FROM m_office_types
 WHERE     RECORD_STATUS = 'C'
       AND state_cd = 27
       AND OFFICE_NAME IS NOT NULL
       AND (   (    OFFICE_TYPE_CD IN (7)
                AND office_level_cd < (SELECT DISTINCT office_level_cd
                                         FROM m_office_types
                                        WHERE     office_type_cd = 7
                                              AND RECORD_STATUS <> 'D'))
            OR (    OFFICE_TYPE_CD NOT IN (7)
                AND office_level_cd <= (SELECT DISTINCT office_level_cd
                                          FROM m_office_types
                                         WHERE     office_type_cd = 7
                                               AND RECORD_STATUS <> 'D')))

Pease notice that it can be simplified, I only wrote like that for clarity