Oracle SQL statements do not support the boolean Type (decode & case)
Version:
SQL> select * from v $ version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0-64bit Production
PL/SQL Release 11.1.0.7.0-Production
CORE 11.1.0.7.0 Production
TNS for Linux: Version 11.1.0.7.0-Production
NLSRTL Version 11.1.0.7.0-Production
Suppose we want to know whether 1> 0 is true:
Check it directly. No!
SQL> select 1> 0 from dual;
Select 1> 0 from dual
*
ERROR at line 1:
ORA-00923: FROM keyword not found where expected
I can't use decode for conversion!
SQL> select decode (1> 0, true, 'true', 'false') from dual;
Select decode (1> 0, true, 'true', 'false') from dual
*
ERROR at line 1:
ORA-00907: missing right parenthesis
If you use case, it will still not work!
SQL> select case 1> 0 when true then 'true' else 'false' end from dual;
Select case 1> 0 when true then 'true' else 'false' end from dual
*
ERROR at line 1:
ORA-00923: FROM keyword not found where expected
The reason is that SQL does not support the boolean Type (there is no other database at hand, and you do not know whether mysql and sqlserver support it ):
SQL> create or replace function is_true return boolean
Is
Begin
Return true;
End;
/
Function created.
SQL> select is_true from dual;
Select is_true from dual
*
ERROR at line 1:
ORA-06552: PL/SQL: Statement ignored
ORA-06553: PLS-382: expression is of wrong type
It cannot be put directly in the SQL statement. Try to put it in the fuction:
Decode or error:
SQL> CREATE OR REPLACE FUNCTION is1gt0
RETURN VARCHAR2
IS
BEGIN
Return decode (1> 0, TRUE, 'true', 'false ');
END;
/
Warning: Function created with compilation errors.
SQL> show err;
Errors for FUNCTION IS1GT0:
LINE/COL ERROR
-------------------------------------------------------------------------
5/3 PL/SQL: Statement ignored
5/10 PLS-00306: wrong number or types of arguments in call to 'decode'
SQL>
Case passed perfectly:
SQL> CREATE OR REPLACE FUNCTION is1gt0
RETURN VARCHAR2
IS
BEGIN
Return case 1> 0
WHEN TRUE
THEN 'true'
ELSE 'false'
END;
END;
/
Function created.
SQL> show err;
No errors.
SQL> select is1gt0 from dual;
IS1GT0
--------------------------------------------------------------------------------
True
SQL>
Summary:
1. Oracle SQL statements do not support the boolean type;
2. decode is unique to oracle, while case is standard SQL. mysql and sqlserver can also be used, and case can also convert and Output boolean values.