Go down
Posts : 155
Join date : 2017-11-11
Age : 27
View user profile

Is there boolean type for column in Oracle SQL?

on Tue Dec 26, 2017 5:51 pm
I tried to:

select 1>2 from dual;
but got:
ORA-00923: FROM keyword not found where expected
Is there boolean type for column expression in Oracle SQL?

I able to do:
select case when 1>2 then 'T' else 'F' end from dual;
Originally I tried to compare date fields and the quickest way I found was getting difference and look to sign...
UPDATE I tried SIGN function, I don't know if it is vendor specific extension:
select SIGN(1-2) from dual;
select SIGN(DATE '2017-01-02' - DATE '2017-02-12') from dual;
but this trick doesn't work for strings...


No there is not, you can use 0 and 1 just as yes/no.
If you need to get the result 1 if something is true and 0 if it is false, you can use a case expression:
select case when (any_logical_condition_here) then 1 else 0 end as my_col
from  ....
where  ....
For example:

select case when 1 > 2 then 1 else 0 end as bool_result
from  dual;

NOTE though - "Boolean" refers strictly to the TRUE/FALSE logic, it has no place for UNKNOWN. When you deal with null, as you must in SQL, you need three-valued logic. The case expression as written above returns 1 when the logical condition is true and 0 otherwise. Try it with 1 > null - the truth value is UNKNOWN, the case expression will return 0.
Back to top
Permissions in this forum:
You can reply to topics in this forum