Case Statement Assistance

Boberj · #1 (**permalink**) 06-24-09, 19:28

The below Case Statement works in Postgres.

CASE
WHEN detail_flag = 1 and detail_status NOT IN ('CO', 'DR', 'TR', 'TE', 'AB')
THEN (detail_status_time) - (select max(x.detail_status_time)
from TL_AB_UNION X
where x.call_row_id = c.call_row_id
and x.detail_status = c.detail_status
and x.detail_flag = 0
-- and x.detail_status_time < detail_status_time
)
ELSE null
END) AS jb

I receive this error message in DB2.

SQL0112N The operand of the column function "SYSIBM.MAX " includes a column function, a scalar fullselect, or a subquery.

Explanation:

The operand of a column function cannot include:

o a column function

o a scalar fullselect

o a subquery

o an XMLQUERY or XMLEXISTS expression except as an operand of
an XMLAGG column function.

In a SELECT list, the operand of an arithmetic operator cannot
be a column function that includes the DISTINCT keyword.

The statement cannot be processed.

User Response:

Correct the use of the column function to eliminate the invalid
expression and try again.

sqlcode : -112

sqlstate : 42607

Any suggestions?

Stealth_DBA · #2 (**permalink**) 06-24-09, 19:54

Boberj, In DB2, I believe the item to the right of the THEN has to be a single expression or value. I don't think you can process other SQL statements at this location. Which means the "THEN (detail_status_time) - (anything)" is not valid. Nor is a Select or Subquery. The only times I have seen a CASE expression in SQL used in a WHERE clasue, it was of the form:

(ANYTHING) OPERATOR CASE WHEN check THEN value ELSE other-value END

example:

Code:

WHERE column-name = CASE MONTH(date-col) 
                      WHEN  1 THEN 'JAN'
                      WHEN  2 THEN 'FEB'
                      WHEN  3 THEN 'MAR'
                      (etc.)
                              ELSE ''
                    END

tonkuma · #3 (**permalink**) 06-25-09, 10:32

Quote:

In DB2, I believe the item to the right of the THEN has to be a single expression or value. ...

No, you can use any expressions and scalar-subqueries in the right of the THEN.
For example:

Code:

------------------------------ Commands Entered ------------------------------
SELECT deptno
     , admrdept
     , mgrno
     , CASE
       WHEN admrdept IN ('A00', 'D01')
       THEN INT(mgrno) * 10000.
          - (SELECT MAX(salary)
               FROM employee e
              WHERE workdept = d.deptno
            )
       ELSE NULL
       END  AS dum
  FROM department d
;
------------------------------------------------------------------------------

DEPTNO ADMRDEPT MGRNO  DUM                  
------ -------- ------ ---------------------
A00    A00      000010             -52750.00
B01    A00      000020             105750.00

<snipped>

I22    E01      -                          -
J22    E01      -                          -

  14 record(s) selected.

Boberj, I want to see whole of your query, and DB2 version/release and OS.

Stealth_DBA · #4 (**permalink**) 06-25-09, 10:42

Thanks for the correction, tonkuma. I think I have tried this before on z/OS V8 without success. I may have been doing something wrong or it may not work on z/OS V8. I will try some test.

Boberj · #5 (**permalink**) 06-25-09, 10:49

SELECT call_row_id
, detail_flag
, detail_status
, call_datetime
, call_alltime_sid
, call_direction
, call_severity
, client_row_id
, call_serv_row_id
, detail_agent_row_id
, detail_status_time
,MAX (
CASE
WHEN detail_status IN ('CO', 'AB', 'TE', 'DR', 'TR') THEN detail_status_time
ELSE null
END) AS TimeEndCall
, max(
CASE
WHEN detail_flag = 1 and detail_status NOT IN ('CO', 'DR', 'TR', 'TE', 'AB')
THEN (detail_status_time)- (select max(x.detail_status_time)
from TL_AB_UNION X
where x.call_row_id = c.call_row_id
and x.detail_status = c.detail_status
and x.detail_flag = 0
)
ELSE null
END) AS "2compl-Stat Dur"
FROM TL_AB_UNION C
--where call_row_id in (51, 603)
GROUP BY call_row_id
, detail_flag
, detail_status
, call_datetime
, call_alltime_sid
, call_direction
, call_severity
, client_row_id
, call_serv_row_id
, detail_agent_row_id
, detail_status_time
ORDER BY
detail_status_time desc

tonkuma · #6 (**permalink**) 06-25-09, 11:23

Quote:

.....
.....
, max(
CASE
WHEN detail_flag = 1 and detail_status NOT IN ('CO', 'DR', 'TR', 'TE', 'AB')
THEN (detail_status_time)- (select max(x.detail_status_time)
from TL_AB_UNION X
where x.call_row_id = c.call_row_id
and x.detail_status = c.detail_status
and x.detail_flag = 0
)
ELSE null
END) AS "2compl-Stat Dur"
.....
.....
GROUP BY call_row_id
, detail_flag
, detail_status
, call_datetime
, call_alltime_sid
, call_direction
, call_severity
, client_row_id
, call_serv_row_id
, detail_agent_row_id
, detail_status_time
.....

All columns used in the CASE expression were included in GROUP BY clause.
So, your query would work by removing max() outside of the CASE expression.

DB2 UDB for z/OS Version 8 supports "(scalar-fullselect)" as one of expressions(new in DB2 z/OS V8 by looking into manual "SQL Reference").

I dont't know the reason why it worked in Postgres.

Boberj · #7 (**permalink**) 06-25-09, 11:41

Tonkuma,

Thank you so much!

JNF2850 · #8 (**permalink**) 06-28-09, 04:52

what is the function of the CASE, as used on the SELECT statement?

Peter.Vanroose · #9 (**permalink**) 06-28-09, 06:45

PostgreSQL is right in the sense that this is indeed logically (and syntactically) correct SQL.
DB2 says it doesn't like the "group by" inside a "group by", which seems to indicate that the correlated subquery inside the CASE is "broken open" by the optimizer.

As was pointed out by Stealth_DBA, in this case you didn't need the outer GROUP BY.
But in situations where you would need it, and again inside a correlated subquery inside a CASE expression, the generic way out (which also makes the query more modular, hence more readable and maintainable) is the use of either nested table expressions or (even better) common table expressions to "save" an intermediate result table into a sort of "view".

In your example this would e.g. be:

Code:

WITH t(cri, ds, dst) AS
(select call_row_id, detail_status, max(detail_status_time)
 from   TL_AB_UNION
 where  detail_flag = 0
 group by call_row_id, detail_status
)
SELECT call_row_id
       , detail_flag
       , detail_status
       , call_datetime
       , call_alltime_sid
       , call_direction
       , call_severity
       , client_row_id
       , call_serv_row_id
       , detail_agent_row_id
       , detail_status_time
       ,MAX(
CASE
WHEN detail_status IN ('CO', 'AB', 'TE', 'DR', 'TR')
THEN detail_status_time
ELSE null
END) AS TimeEndCall
       , MAX(
CASE
WHEN detail_flag = 1 and detail_status NOT IN ('CO', 'DR', 'TR', 'TE', 'AB')
THEN detail_status_time -
      (select dst from t
       where  cri = c.call_row_id
         and  ds = c.detail_status)
ELSE null
END) AS "2compl-Stat Dur"
FROM TL_AB_UNION C
--where call_row_id in (51, 603)
GROUP BY call_row_id
, detail_flag
, detail_status
, call_datetime
, call_alltime_sid
, call_direction
, call_severity
, client_row_id
, call_serv_row_id
, detail_agent_row_id
, detail_status_time
ORDER BY detail_status_time desc

Internet Services

Web Development

Digital Marketing

Consumer Tech