oracle ELSE case runs regardless-CodePudding

I have a case statement (myCol is a NUMBER):

select case when myCol < 0 then 0 else round(POWER(10,AVG(LOG(10,myCol))),2) end myCol from myTable where id = 123 group by myCol;

But Oracle seems to run the else clause regardless, and the POWER or LOG functions throw ORA-01428: argument '-2.75' is out of range when myCol is negative (-2.75). I would think the else clause would be ignored if myCol < 0 but maybe Oracle is compiling the entire statement before running?

I also tried using decode(sign(myCol)... but that also fails.

How can I handle this?

CodePudding user response：

I'd say that query you posted isn't complete. Why? Because of this:

SQL> with test (mycol) as
  2    (select  2    from dual union all
  3     select -2.75 from dual
  4    )
  5  select case when myCol < 0 then 0
  6              else round(POWER(10,AVG(LOG(10,myCol))),2)
  7         end myCol
  8  from test;
select case when myCol < 0 then 0
                 *
ERROR at line 5:
ORA-00937: not a single-group group function


SQL>

But, when you add the group by clause, you get error you mentioned:

SQL> with test (mycol) as
  2    (select  2    from dual union all
  3     select -2.75 from dual
  4    )
  5  select case when myCol < 0 then 0
  6              else round(POWER(10,AVG(LOG(10,myCol))),2)
  7         end myCol
  8  from test
  9  group by mycol;
            else round(POWER(10,AVG(LOG(10,myCol))),2)
                                           *
ERROR at line 6:
ORA-01428: argument '-2.75' is out of range


SQL>

What to do? One option is to apply the abs function, because it is GROUP BY that causes your problems - it splits the whole result data set into rows, each mycol on its own, and that's where -2.75 becomes out of range.

SQL> with test (mycol) as
  2    (select  2    from dual union all
  3     select -2.75 from dual
  4    )
  5  select mycol original_value,
  6         case when myCol < 0 then 0
  7              else round(POWER(10,AVG(LOG(10,abs(myCol)))),2)
  8         end myCol                           ---
  9  from test                               -- this
 10  group by mycol;

ORIGINAL_VALUE      MYCOL
-------------- ----------
         -2,75          0
             2          2

SQL>