Results 1 to 9 of 9
  1. #1
    Join Date
    Jun 2009
    Posts
    4

    Unanswered: Case Statement Assistance

    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?

  2. #2
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    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

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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.

  4. #4
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    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.

  5. #5
    Join Date
    Jun 2009
    Posts
    4

    Here is the full query. DB2 9.1 Suse Linux

    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

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    .....
    .....
    , 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.
    Last edited by tonkuma; 06-25-09 at 12:28.

  7. #7
    Join Date
    Jun 2009
    Posts
    4
    Tonkuma,

    Thank you so much!

  8. #8
    Join Date
    Jun 2009
    Posts
    1
    what is the function of the CASE, as used on the SELECT statement?

  9. #9
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    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
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •