Revious Revious - 2 months ago 6
SQL Question

Oracle: How can I get a value 'TRUE' or 'FALSE' comparing two NUMBERS in a query?

I want to compare two numbers. Let's take i.e. 1 and 2.

I've tried to write the following query but it simply doesn't work as expected (Toad says: ORA-00923: FROM keyword not found where expected):

SELECT 1 > 2 from dual


The DECODE is something like a Switch case, so how can I get the result of an expression evalutation (i.e. a number comparison) putting it in the select list?

I have found a solution using a functions instead of an expression in the SELECT LIST: i.e.

select DECODE(SIGN(actual - target)
, -1, 'NO Bonus for you'
, 0,'Just made it'
, 1, 'Congrats, you are a winner')
from some_table


Is there a more elegant way?

Also how do I compare two dates?

Answer

The SIGN() function is indeed probably the best way of classifying (in)equality that may be of interest to you if you want to test a > b, a = b and a < b, and it will accept date-date or numeric-numeric as an argument.

I'd use a Case statement by preference, rather than a decode.

Select
  case sign(actual-target)
    when -1 then ...
    when  0 then ...
    when  1 then ...
  end