Getting error: "Missing keyword" when using case in where clause-CodePudding

SELECT *
  FROM corp_action_restriction car1
  LEFT OUTER JOIN investment_account inv
    ON CASE
       WHEN REGEXP_LIKE(REGEXP_SUBSTR(car1.restriction_value,
                                      '[^|] ',
                                      1,
                                      1),
                        '^[[:digit:]]*$') THEN
        TO_NUMBER(REGEXP_SUBSTR(car1.restriction_value, '[^|] ', 1, 1)) =
        inv.investment_account_id
       ELSE
        car1.restriction_value = TO_CHAR(inv.investment_account_id)
       END

I'm getting missing keyword error.

Can anyone tell me what's wrong with this query?

ORA-00905: missing keyword
00905. 00000 - "missing keyword"
*Cause:
*Action:
Error at Line: 4 Column: 61

CodePudding user response：

Because return_expr or else_expr cannot be of a boolean expression type. Moreover the third and fourth arguments(1&1) of REGEXP_SUBSTR() are redundant.

Indeed you don't need a CASE..WHEN expression, and if the DB version is 12cR2 , then consider using

SELECT *
  FROM corp_action_restriction car1
  LEFT JOIN investment_account inv
    ON TO_NUMBER(
                 REGEXP_SUBSTR(
                               car1.restriction_value, 
                               '[^|] '
                              )  
                 DEFAULT NULL ON CONVERSION ERROR
                ) = inv.investment_account_id

this way, you won't get an error due to conversion

Demo

CodePudding user response：

That would be

SELECT *
  FROM CORP_ACTION_RESTRICTION CAR1
       LEFT OUTER JOIN INVESTMENT_ACCOUNT INV
          ON INV.INVESTMENT_ACCOUNT_ID =
             CASE
                WHEN REGEXP_LIKE (REGEXP_SUBSTR (CAR1.RESTRICTION_VALUE,
                                                 '[^|] ',
                                                 1,
                                                 1),
                                  '^[[:digit:]]*$')
                THEN
                   TO_NUMBER (REGEXP_SUBSTR (CAR1.RESTRICTION_VALUE,
                                             '[^|] ',
                                             1,
                                             1))
                ELSE
                   CAR1.RESTRICTION_VALUE
             END;

In other words, put inv.investment_account_id into ON clause and then compare it (with a =) to case expression's result.