CODEWITHSUNDEEP
sqloracle-databasesql-server-2008
user2632670
user2632670

Reputation: 21

Oracle, Syntax Error ORA00907

I am using Case statement in the Where condition. When I am trying to use this condition I am getting the results

AND ( XF.SRC_KEY NOT IN (3, 17) 
OR UPPER(PM.TRADE_NAME) NOT IN CASE WHEN XF.SRC_KEY = 3 THEN ('AVONEX') END;

But when I tried to extend the case statement I am getting Syntax error as Missing Right Parenthesis.

AND ( XF.SRC_KEY NOT IN (3, 17) 
OR UPPER(PM.TRADE_NAME) NOT IN CASE WHEN XF.SRC_KEY = 3 THEN ('AVONEX') 
WHEN XF.SRC_KEY = 17 THEN ('AVONEX','TECFIDERA') END;

Please find the Entire Query for reference :

SELECT x.*
  FROM ods_ims_1 x,
       ods_lh_ims_2 xf,
       ods_prod_master pm,
       lh_ods.ods_week_time_dimn t
 WHERE x.ims_spp_key = xf.ims_supplier_id (+)
   AND x.ndc_no = pm.ndc_no (+)
   AND (xf.src_key NOT IN (3, 17)
           OR UPPER(pm.trade_name) NOT IN CASE WHEN XF.SRC_KEY = 3 THEN ('AVONEX') END
           --WHEN XF.SRC_KEY = 17 THEN ('AVONEX','TECFIDERA') END
           OR TRUNC(MONTH_END_DT) >= CASE WHEN XF.SRC_KEY = 3 THEN '31-OCT-14' ELSE '31-JAN-13' END)
   AND t.week_time_dimn_key = x.week_time_dimn_key;

Upvotes: 0

Views: 71

Answers (2)

David Faber
David Faber

Reputation: 12485

I am surprised that the first condition works, but the second doesn't. The first

AND ( XF.SRC_KEY NOT IN (3, 17) 
 OR UPPER(PM.TRADE_NAME) NOT IN CASE WHEN XF.SRC_KEY = 3 THEN ('AVONEX') END;

is clearly missing a right parenthesis, as is the second:

AND ( XF.SRC_KEY NOT IN (3, 17)
 OR UPPER(PM.TRADE_NAME) NOT IN CASE WHEN XF.SRC_KEY = 3 THEN ('AVONEX') 
    WHEN XF.SRC_KEY = 17 THEN ('AVONEX','TECFIDERA') END;

In each case the missing parenthesis could be placed after the END of the CASE.

That said, I'm not sure this syntax would work even with the missing parenthesis. In fact, I am pretty sure it would not. It would be better to write the condition this way, I think:

AND ( XF.SRC_KEY NOT IN (3, 17)
   OR XF.SRC_KEY = 3 AND UPPER(PM.TRADE_NAME) != 'AVONEX'
   OR XF.SRC_KEY = 17 AND UPPER(PM.TRADE_NAME) NOT IN ('AVONEX','TECFIDERA') ); -- note closing parenthesis

If you're wedded to using a CASE statement then you can achieve what you're trying to do using REGEXP_LIKE():

AND ( XF.SRC_KEY NOT IN (3,17)
   OR REGEXP_LIKE(UPPER(PM.TRADE_NAME), CASE WHEN XF.SRC_KEY = 3 THEN '^AVONEX$' WHEN XF.SRC_KEY = 17 THEN '^AVONEX|TECFIDERA$') END );

-- but that's a bit klunky and esoteric, wouldn't you say?

Upvotes: 0

Hart CO
Hart CO

Reputation: 34784

You should avoid using deprecated JOIN syntax, updating to explicit JOIN syntax clears up the WHERE criteria, then you can clean up your WHERE criteria by getting rid of the CASE expression when it requires multiple outputs, as it causes the problem you noticed. I believe this should work:

SELECT X.*
FROM ODS_IMS_1 X
LEFT JOIN ODS_LH_IMS_2 XF
  ON  X.IMS_SPP_KEY = XF.IMS_SUPPLIER_ID
LEFT JOIN ODS_PROD_MASTER PM
  ON  X.NDC_NO = PM.NDC_NO
JOIN LH_ODS.ODS_WEEK_TIME_DIMN T
  ON  T.WEEK_TIME_DIMN_KEY = X.WEEK_TIME_DIMN_KEY
WHERE XF.SRC_KEY NOT IN (3, 17)
   OR (XF.SRC_KEY = 3 AND UPPER(PM.TRADE_NAME) <> 'AVONEX')
   OR (XF.SRC_KEY = 17 AND UPPER(PM.TRADE_NAME) NOT IN  ('AVONEX','TECFIDERA'))
   OR TRUNC(MONTH_END_DT) >= CASE WHEN XF.SRC_KEY = 3 THEN '31-OCT-14' ELSE '31-JAN-13' END

Note: If I messed up the JOIN conversion in some way, you can still get your criteria to work with:

SELECT X.*
FROM ODS_IMS_1 X, ODS_LH_IMS_2 XF, ODS_PROD_MASTER PM, LH_ODS.ODS_WEEK_TIME_DIMN T
WHERE X.IMS_SPP_KEY = XF.IMS_SUPPLIER_ID(+)
AND X.NDC_NO = PM.NDC_NO(+)
AND (       XF.SRC_KEY NOT IN (3, 17)
        OR (XF.SRC_KEY = 3 AND UPPER(PM.TRADE_NAME) NOT IN ('AVONEX'))
        OR (XF.SRC_KEY = 17 AND UPPER(PM.TRADE_NAME) NOT IN ('AVONEX','TECFIDERA'))
        OR TRUNC(MONTH_END_DT) >= CASE WHEN XF.SRC_KEY = 3 THEN '31-OCT-14' ELSE '31-JAN-13' END
        )
AND T.WEEK_TIME_DIMN_KEY = X.WEEK_TIME_DIMN_KEY;

Upvotes: 1

Related Questions