Recipe12.15.Using Case Expressions to Flag Rows

Recipe 12.15. Using Case Expressions to Flag Rows

Problem

You want to map the values in a column, say, the EMP table's JOB column, into a series of "Boolean" flags. For example, you wish to return the following result set:

 ENAME  IS_CLERK IS_SALES IS_MGR IS_ANALYST IS_PREZ ------ -------- -------- ------ ---------- ------- KING          0        0      0          0       1 SCOTT         0        0      0          1       0 FORD          0        0      0          1       0 JONES         0        0      1          0       0 BLAKE         0        0      1          0       0 CLARK         0        0      1          0       0 ALLEN         0        1      0          0       0 WARD          0        1      0          0       0 MARTIN        0        1      0          0       0 TURNER        0        1      0          0       0 SMITH         1        0      0          0       0 MILLER        1        0      0          0       0 ADAMS         1        0      0          0       0 JAMES         1        0      0          0       0 

Such a result set can be useful for debugging and to provide yourself a view of the data different from what you'd see in a more typical result set.

Solution

Use a CASE expression to evaluate each employee's JOB, and return a 1 or 0 to signify her JOB. You'll need to write one CASE expression, and thus create one column for each possible job:

  1 select ename,  2        case when job = 'CLERK'  3             then 1 else 0  4        end as is_clerk,  5        case when job = 'SALESMAN'  6             then 1 else 0  7        end as is_sales,  8        case when job = 'MANAGER'  9             then 1 else 0 10        end as is_mgr, 11        case when job = 'ANALYST' 12             then 1 else 0 13        end as is_analyst, 14        case when job = 'PRESIDENT' 15             then 1 else 0 16        end as is_prez 17   from emp 18  order by 2,3,4,5,6 

Discussion

The solution code is pretty much self-explanatory. If you are having trouble understanding it, simply add JOB to the SELECT clause:

  select ename,        job,        case when job = 'CLERK'             then 1 else 0        end as is_clerk,        case when job = 'SALESMAN'             then 1 else 0        end as is_sales,        case when job = 'MANAGER'             then 1 else 0        end as is_mgr,        case when job = 'ANALYST'            then 1 else 0        end as is_analyst,        case when job = 'PRESIDENT'            then 1 else 0        end as is_prez   from emp  order by 2 ENAME  JOB       IS_CLERK IS_SALES IS_MGR IS_ANALYST IS_PREZ ------ --------- -------- -------- ------ ---------- ------- SCOTT  ANALYST          0        0      0          1       0 FORD   ANALYST          0        0      0          1       0 SMITH  CLERK            1        0      0          0       0 ADAMS  CLERK            1        0      0          0       0 MILLER CLERK            1        0      0          0       0 JAMES  CLERK            1        0      0          0       0 JONES  MANAGER          0        0      1          0       0 CLARK  MANAGER          0        0      1          0       0 BLAKE  MANAGER          0        0      1          0       0 KING   PRESIDENT        0        0      0          0       1 ALLEN  SALESMAN         0        1      0          0       0 MARTIN SALESMAN         0        1      0          0       0 TURNER SALESMAN         0        1      0          0       0 WARD   SALESMAN         0        1      0          0       0