CODEWITHSUNDEEP
sqldatabasedb2
Alpha001
Alpha001

Reputation: 371

DB2: How to filter based on the result obtained from CASE condition

I am trying to filter the data based on the results obtained from previous CASE condition in DB2:

Below is the initial query to filter the data:

SELECT EM.NAME,EM.ID,EM.DIV
CASE DEPT
WHEN '1' THEN 'FIN'
WHEN '2' THEN 'MKT'
WHEN '3' THEN 'IT'
WHEN '4' THEN 'HR' END AS DEPT_DESC
FROM EMPLOYEE_RCD EM;

Sample Data:

NAME    ID    DIV   DEPT_DESC
MICHAEL 3334   3    IT
SAMUEL  100922 1    FIN
ORINDA  7363   1    FIN
SARA    8383   2    MKT
RACHAEL 8383   4    HR
MARTIN  33312  
SUZY    993    NA   NA
MIKE    576    NULL NULL

What I tried to filter the results:

SELECT EM.NAME,EM.ID,EM.DIV
CASE DEPT
WHEN '1' THEN 'FIN'
WHEN '2' THEN 'MKT'
WHEN '3' THEN 'IT'
WHEN '4' THEN 'HR' END AS DEPT_DESC
CASE DEPT_DESC
WHEN DEPT_DESC == ''   THEN 'No Dept'
WHEN DEPT_DESC == NA   THEN 'No Dept'
WHEN DEPT_DESC == NULL THEN 'No Dept'
FROM EMPLOYEE_RCD EM;

but not getting the results instead getting errors:

[Code: -104, SQL State: 42601]  An unexpected token "== ''" was found following "WHEN DEPT_DESC".  Expected tokens may include:  "<space>".. SQLCODE=-104, SQLSTATE=42601, DRIVER=4.22.29

Any help in, how to filter the data and get the below result:

Expected 0/P:

NAME    ID    DIV   DEPT_DESC
MARTIN  33312  
SUZY    993    NA   NA
MIKE    576    NULL NULL

Upvotes: 0

Views: 504

Answers (2)

Paul Vernon
Paul Vernon

Reputation: 3901

In the example given, you only need one case statement

SELECT  EM.NAME
,       EM.ID
,       EM.DIV
,       CASE DEPT
            WHEN '1' THEN 'FIN'
            WHEN '2' THEN 'MKT'
            WHEN '3' THEN 'IT'
            WHEN '4' THEN 'HR' 
                     ELSE 'No Dept' END AS DEPT_DESC
FROM
    EMPLOYEE_RCD EM

BTW If you use a SQL editor that can parse your SQL as you write it (e.g. the free IBM Data Studio), it is easier to spot and fix syntax errors.

Upvotes: 2

The Impaler
The Impaler

Reputation: 48770

You are using CASE the wrong way:

  • Each WHEN clause needs to be followed by a [boolean] condition.
  • Use single equal = for equality, not double equal ==.
  • If you want to use a pre-computed column to process it again, you can use a subquery.

Your query should probably look like:

select name, id, div,
    CASE
    WHEN DEPT_DESC = ''   THEN 'No Dept'
    WHEN DEPT_DESC = NA   THEN 'No Dept'
    WHEN DEPT_DESC = NULL THEN 'No Dept'
    END as dept_desc
  from ( -- subquery runs first
    SELECT EM.NAME,EM.ID,EM.DIV
    CASE 
    WHEN DIV = '1' THEN 'FIN'
    WHEN DIV = '2' THEN 'MKT'
    WHEN DIV = '3' THEN 'IT'
    WHEN DIV = '4' THEN 'HR' END AS DEPT_DESC
    FROM EMPLOYEE_RCD EM
  ) x    
;

Upvotes: 2

Related Questions