Hi,
I am learning SQL (with its Oracle extensions). I ran into a puzzler. I would like to understand why/how something works or doesn't work. I do know how to work around it, so that is NOT my question. I simply want to understand what's going on.
Here is the situation, much simplified. I created a table "TEST" with just two columns , "CODE" is the primary key, and I inserted two rows.
SQL> select * from test;
CODE VALUE
-------- ----------
a 3
b 8
2 rows selected.
Now I want to add a check constraint: if CODE = 'a' then VALUE < 5 but if CODE <> 'a' then VALUE > 7.
This works:
SQL> alter table test
2 add constraint val_ck
3 check ( code = 'a' and value < 5 or code <> 'a' and value > 7)
4 ;
Table altered.
But this doesn't, and I don't understand why:
SQL> alter table test
2 add constraint val2_ck
3 check ( case code when 'a' then value < 5 else value > 7 end)
4 ;
check ( case code when 'a' then value < 5 else value > 7 end)
*
ERROR at line 3:
ORA-00905: missing keyword
I understand that Oracle does not implement the BOOLEAN data type, so I can't think of CASE ... (in my example) as a boolean expression, with a value of TRUE or FALSE (or possibly UNKNOWN/NULL). What I don't understand is what kind of "thing" is the (boolean expression!)
code = 'a' and value < 5 or code <> 'a' and value > 7
This is OK in the check condition; it is not a "boolean expression" (they don't exist in Oracle), but perhaps a "search condition" or "conditional value" or "logical value" which can't be assigned, but can still be checked in a search condition? Fine, but then why can't the CASE construction be used in exactly the same way? A CASE construction like the one I described (which would be a boolean expression if those were implemented) can be unfolded, in an obvious way, to become a logical expression like the one that uses only standard logical operators (=, <>, AND, OR). Why is the latter OK, but not the former?
Is there something similar to the CASE construction that can be used in such situations? Also, if there is some good reading on this that would be awesome.
Thank you!