Results 1 to 7 of 7
  1. #1
    Join Date
    Apr 2010
    Posts
    2

    Unanswered: Select multiple max on the same column with different constraint

    Hi everyone,

    First of all, I apologize because I'll not be able to give you details on my DB2 version / configuration since I'm a developer in a bank and I don't have hand or information on this.

    I'm trying to get multiple max values for a specific field based on different constraints, eg I've got these 4 queries:

    select max(pricedatedec)
    from EUREKA_LIVE.UNITPRICE
    where rivacode = nblfundid2
    and pricedatedec <= decimal(
    select min(navdatestr)
    from data
    where rivacode = nblfundid2
    and navdatestr >= char(integer(DATE(to_date('010909', 'DDMMYY'))))
    )

    select max(pricedatedec)
    from EUREKA_LIVE.UNITPRICE
    where rivacode = nblfundid2
    and pricedatedec <= decimal(
    select min(navdatestr)
    from data
    where rivacode = nblfundid2
    and navdatestr >= char(integer(valuationdate - day(valuationdate) - 1) day)
    )

    select max(pricedatedec)
    from EUREKA_LIVE.UNITPRICE
    where rivacode = nblfundid2
    and pricedatedec <= decimal(
    select min(navdatestr)
    from data
    where rivacode = nblfundid2
    and navdatestr > char(year(valuationdate)*10000)
    )

    select max(pricedatedec)
    from EUREKA_LIVE.UNITPRICE
    where rivacode = nblfundid2
    and pricedatedec <= decimal(
    select max(navdatestr)
    from data
    where rivacode = nblfundid2
    and navdatestr < char(integer(valuationdate))
    )

    I want to make a single query out of these and optimize it as much as it is possible. Can you please help me?

    Thank you very much.

    Adrien.

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by n2xt View Post
    I want to make a single query out of these
    How about UNION?

  3. #3
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    don't have the time to sit and figure it out right now, but how bout 4 case statements.
    Dave

  4. #4
    Join Date
    Apr 2010
    Posts
    2
    Hey,

    Thanks for the quick answers!
    I have to admit that I've already thought to both solutions you're talking about, but without being able to figure out how to implement them.
    By the way, this bunch of queries is part of a much larger query which is already in place and which uses a huge amount of "unions" (I am not allowed to modify this part of the project), so what would be the best choice in terms of performance (actually, we have another solution in place which takes more than 9 minutes to run and that's why I'm doing this modification in order to improve performances).

    Thanks in advance. Regards,

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Try this...
    (I changed some conditions marking by bold red.)
    Code:
    SELECT
           MAX(CASE
               WHEN pricedatedec <= navdate_dec_a
               THEN pricedatedec
               END
              )  AS max_pricedatedec_a
         , MAX(CASE
               WHEN pricedatedec <= navdate_dec_b
               THEN pricedatedec
               END
              )  AS max_pricedatedec_b
         , MAX(CASE
               WHEN pricedatedec <= navdate_dec_c
               THEN pricedatedec
               END
              )  AS max_pricedatedec_c
         , MAX(CASE
               WHEN pricedatedec <= navdate_dec_d
               THEN pricedatedec
               END
              )  AS max_pricedatedec_d
      FROM EUREKA_LIVE.UNITPRICE
         , (SELECT DECIMAL( MIN(CASE
                                WHEN navdatestr >= '20090901'
                                                /* char(integer(DATE(to_date('010909', 'DDMMYY')))) */
                                THEN navdatestr
                                END
                               )
                          ) AS navdate_dec_a
                 , DECIMAL( MIN(CASE
                                WHEN navdatestr >= char( integer(valuationdate - (day(valuationdate) - 1) day) )
                                THEN navdatestr
                                END
                               )
                          ) AS navdate_dec_b
                 , DECIMAL( MIN(CASE
                                WHEN navdatestr >  char( year(valuationdate) * 10000 )
                                THEN navdatestr
                                END
                               )
                          ) AS navdate_dec_c
                 , DECIMAL( MAX(CASE
                                WHEN navdatestr <  char( integer(valuationdate) )
                                THEN navdatestr
                                END
                               )
                          ) AS navdate_dec_d
              FROM data
             WHERE rivacode = nblfundid2
           ) AS comp_dates
     WHERE rivacode = nblfundid2
    ;

  6. #6
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    Tonkuma, you code
    Code:
    CASE
    WHEN
    THEN
    END
    can we assume here that the result will be NULL when the "WHEN" condition is false? Is it not advisable to code an "ELSE NULL" branch just for readability.

  7. #7
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    can we assume here that the result will be NULL when the "WHEN" condition is false?
    Yes. It is clearly documented in the manual "DB2 SQL Reference".

    Is it not advisable to code an "ELSE NULL" branch just for readability.
    I think it is a good practice, too.

    Another(my) practice is to eliminate definite default branch(or option) for readability.

Tags for this Thread

Posting Permissions

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