I get an error while writing the IIF statement, table and the statement given below.

Statement:

SELECT IIF(EMP_ID=1,'True','False') from Employee;

Table:

CREATE TABLE SCOTT.EMPLOYEE
(
   EMP_ID       INTEGER                          NOT NULL,
   EMP_FNAME    VARCHAR2(30 BYTE)                NOT NULL,
   EMP_LNAME    VARCHAR2(30 BYTE)                NOT NULL,
   EMP_ADDRESS  VARCHAR2(50 BYTE)                NOT NULL,
   EMP_PHONE    CHAR(10 BYTE)                    NOT NULL,
   EMP_GENDER   CHAR(1 BYTE)
)

Error:

00907-missing right parantheses

Please provide your inputs.


Solution 1:

Oracle doesn't provide such IIF Function. Instead, try using one of the following alternatives:

DECODE Function:

SELECT DECODE(EMP_ID, 1, 'True', 'False') from Employee

CASE Function:

SELECT CASE WHEN EMP_ID = 1 THEN 'True' ELSE 'False' END from Employee

Solution 2:

Two other alternatives:

  1. a combination of NULLIF and NVL2. You can only use this if emp_id is NOT NULL, which it is in your case:

    select nvl2(nullif(emp_id,1),'False','True') from employee;
    
  2. simple CASE expression (Mt. Schneiders used a so-called searched CASE expression)

    select case emp_id when 1 then 'True' else 'False' end from employee;