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

Question

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 1

There is no boolean types in sql (at least in oracle).
you can use case:

SELECT CASE when 1 > 2 THEN 1 ELSE 0 END FROM dual

But your solution (decode) is also good, read here

Answer 2

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