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.
Oracle doesn't provide such IIF Function. Instead, try using one of the following alternatives:
SELECT DECODE(EMP_ID, 1, 'True', 'False') from Employee
SELECT CASE WHEN EMP_ID = 1 THEN 'True' ELSE 'False' END from Employee
Two other alternatives:
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;
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;