Select multiple max on the same column with different constraint

n2xt · #1 (**permalink**) 04-14-10, 14:57

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.

n_i · #2 (**permalink**) 04-14-10, 15:09

Quote:

Originally Posted by n2xt

I want to make a single query out of these

How about UNION?

dav1mo · #3 (**permalink**) 04-14-10, 15:30

don't have the time to sit and figure it out right now, but how bout 4 case statements.
Dave

n2xt · #4 (**permalink**) 04-14-10, 16:39

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,

tonkuma · #5 (**permalink**) 04-14-10, 22:23

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
;

dr_te_z · #6 (**permalink**) 04-15-10, 01:50

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.