Is there boolean type for column in Oracle SQL?

Question

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 a boolean type. You can use a char() or small integer. — Gordon Linoff
– Gordon Linoff, Commented Dec 26, 2017 at 15:13
I am looking for a way to express result of comparing of two values. I am not interested in creating column with "boolean" type. — gavenkoa
– gavenkoa, Commented Dec 26, 2017 at 15:15
sign returns -1, 0 or 1 (or null) according to the sign of the operand. You could use case to test string values. Column datatypes are listed here. Avoid char at all costs. — William Robertson
– William Robertson, Commented Dec 26, 2017 at 16:47

Zakaria Charik · Accepted Answer · 2017-12-26 15:15:55Z

4

No there is not, you can use 0 and 1 just as yes/no.

answered Dec 26, 2017 at 15:15

Zakaria Charik

1194 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

user5683823 · Accepted Answer · 2017-12-26 15:25:54Z

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; BOOL_RESULT --------------------------------------- 0

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.

As is well-established in lore, Boolean is indeed trivalent: True, False, FileNotFound (link)

Collectives™ on Stack Overflow

Is there boolean type for column in Oracle SQL?

2 Answers 2

Comments

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Linked

Related