Results 1 to 3 of 3

Thread: case and MAX

  1. #1
    Join Date
    Jan 2005

    Unanswered: case and MAX

    what is the wrong with this????

    MAX (CASE WHEN MAX (X1)=val1 THEN X2
    ELSE WHEN MAX (Y1)=val2 THEN Y2) AS XXXX  )
    Last edited by ahmedwaseem2000; 05-25-07 at 08:30.

  2. #2
    Join Date
    Feb 2007
    Bratislava, Slovakia
    several tips:
    -- missing END
    -- SHIP-VIOL-D-Q - instead '-' use '_'
    instead max(x1) use subselect

    or paste whole select

  3. #3
    Join Date
    Jan 2007
    Jena, Germany
    There are several semantical and syntactical problems:
    - Have a look at the syntax diagram for CASE expressions, first. The construct "ELSE WHEN" is not right at all.
    - What should the result be if the equality comparisons are both not met or just one is not met?
    - What is the nested MAX(X1) and MAX(Y1) supposed to be doing? They need a table and a column to operate on. Both compute the maximum of all the values in column X1 and Y1, respectively. Then you throw things off because a CASE expression operates on a single row only - not a table.

    Maybe you want to do this:
    WITH t1(max_x1, max_y1) AS
       ( SELECT MAX(x1), MAX(y1)
         FROM ... ),
    t2 (val) AS 
       ( SELECT CASE
                   WHEN max_x1 = val1 THEN x2
                   WHEN max_y1 = val2 THEN y2
                   ELSE NULL
         FROM   t1 )
    SELECT MAX(val)
    FROM   t2
    I think this is much clearer. The rest is the optimizer's job to do something useful with it and come up with the best access plan.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

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