Oracle: Can I use case inside where clause to compare with a column value-CodePudding

Assume limit_exceeded is a boolean-like column that has 1 if the limit 10 was exceeded and 0 if not. I'm passing a number to get the corresponding risk_code based on if the limit was exceeded or not.

I tried this but gives me an error:

DECLARE
  temp   VARCHAR2(10);
  my_num NUMBER;
BEGIN
  my_num := 80;
  SELECT risk_code
    INTO temp
    FROM risk_table
   WHERE limit_exceeded = (CASE WHEN my_num > 10 THEN 1 ELSE 0) ;
  DBMS_OUTPUT.PUT_LINE(temp);
END;

Is there a better way?

CodePudding user response：

You can use SIGN() function along with DECODE() as another option such as

SELECT risk_code
  INTO temp
  FROM risk_table
 WHERE limit_exceeded = DECODE(SIGN(my_num - 10),1,0);