Oracle SQL statements do not support the boolean Type (decode & amp; case)

Source: Internet
Author: User

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.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.