Newer to DB2 - How can I do this?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

MJT - 01 Feb 2006 17:55 GMT

I am newer to db2 and have to use alot of queries that were already in
place. In the following example I have added the subselect statement
because I needed to know the max date of the result set for a report
and it had to come back from the query as the report will only allow
one dataset but that is a different issue. Anyway ... I ran into
another issue because this result set feeds a matrix. I need to use
the subselect field (maxDT) to create a calculated field to be returned
in this same query. I need to create a calculated field called
Latest_Downtime_minutes that is essentially this:

If start_lcl_dt = maxdt Then (((100.0 - Percent)/100)* value_cnt)/60
Else 0'

the field "maxdt" is the result of the subselect in the query so I dont
know if I can use that either ...

(NOTE:The question marks are of course because I have to use parameters
to drive this and the reporting tool only accepts unnamed parms - so
they are there intentionally)

SELECT res_name, client_code, start_lcl_dt, res_grp_name, res_type,
value AS Percent, value_cnt, metric_name, client_name, group_path,
year(start_lcl_dt) AS Year, month(start_lcl_dt) AS Month,
quarter(start_lcl_dt) AS Quarter,
(SELECT MAX(start_lcl_dt)
FROM <tables go here>
WHERE metric_code LIKE ?
AND client_name = ?
AND DATE (start_lcl_dt) >= ?
AND DATE (start_lcl_dt) < ? ) AS maxdt
FROM <tables go here>
WHERE metric_code LIKE ?
AND client_name = ?
AND DATE (start_lcl_dt) >= ?
AND DATE (start_lcl_dt) < ?

Reply to this Message

Serge Rielau - 01 Feb 2006 18:06 GMT

> I am newer to db2 and have to use alot of queries that were already in
> place. In the following example I have added the subselect statement
[quoted text clipped - 11 lines]
> the field "maxdt" is the result of the subselect in the query so I dont
> know if I can use that either ...

<snip>
Take a look at OLAP functions.
http://publib.boulder.ibm.com/infocenter/db2luw/v8//topic/com.ibm.db2.udb.doc/ad
min/r0000736.htm

I'm not entirely clear of all what you are asking, but this should get
you going the right direction:
SELECT res_name, client_code, start_lcl_dt, res_grp_name, res_type,
value AS Percent, value_cnt, metric_name, client_name, group_path,
year(start_lcl_dt) AS Year, month(start_lcl_dt) AS Month,
quarter(start_lcl_dt) AS Quarter,

MAX(start_lcl_dt) OVER() AS maxdt

FROM <tables go here>
WHERE metric_code LIKE ?
AND client_name = ?
AND DATE (start_lcl_dt) >= ?
AND DATE (start_lcl_dt) < ?

Signature

Serge Rielau
DB2 Solutions Development
DB2 UDB for Linux, Unix, Windows
IBM Toronto Lab

Reply to this Message

MJT - 01 Feb 2006 21:14 GMT

I didnt want to post the entire query with the joins and all in this
forum .... I can though if it makes more sense. It seems like you took
out the subselect and replaced it with MAX(start_lcl_dt) OVER() AS
maxdt .... being new ... I dont know what that does. There are lots
of joins and conditions in the FROM statement that I took out for
simplicity. When I tried to just enter a max(start_lcl_dt) in the
select statement then the query complained all over the place about
needing a group by or something due to the aggregate being used. I can
send the query if it helps to get an answer. Especially because what
you provided is not going to address the issue I was have (the sub
select to get the max report date is working) .....I did not know how
to get a calculated field based on the value of max date.... I want a
calculated field to be returned for each row returned. The calculation
should be different based upon if the start_lcl_dt of the current row
is = to the max start_lcl_date of the entire result set (which was why
I did the subselect in the first place ... to get the max date of the
result set). Sorry if I was not clearer in my post.

if start_lcl_dt = maxdt then ((100.0- VALUE)/100)* VALUE_CNT)/60 as
DowntimeMin
Else 0 as DowntimeMin

Reply to this Message

Serge Rielau - 01 Feb 2006 23:12 GMT

> I didnt want to post the entire query with the joins and all in this
> forum .... I can though if it makes more sense. It seems like you took
[quoted text clipped - 17 lines]
> DowntimeMin
> Else 0 as DowntimeMin

MAX() aggregates all rows in a group and thus reduces the result set.
You don't want that.
MAX() OVER() is an OLAP function it find teh MAX within a group WITHOUT
reducing teh resultset.

It appears that what you need is something like this:
CASE WHEN start_lcl_dt = MAX(start_lcl_dt) OVER()
THEN ((100.0- VALUE)/100)* VALUE_CNT)/60
ELSE 0 END AS DowntimeMin

To understand OLAP better I encourag eyou read up on the link I posted
It's a bit hard to explain :-)
Cheers
Serge

Signature

Serge Rielau
DB2 Solutions Development
DB2 UDB for Linux, Unix, Windows
IBM Toronto Lab

Reply to this Message

MJT - 01 Feb 2006 23:52 GMT

thank you and I will read that. The question will be can I use OLAP
functions in the tool I am having to deal with - which is microsoft sql
reporting services ... this is where I have to design and place my
query. I will go off and read the link now. Just to update you .. I
changed the query and have it working to return max(start_lcl_dt) - I
broke down and added the needed group by statement) and I added the
calculated field (((100.0- VALUE)/100)* VALUE_CNT)/60 ) as DowntimeMin
--- all that is working now. However ... the issue is still that I
want the field DowntimeMin to only calculate for the rows where the
start_lcl_dt = max(start_lcl_dt) and otherwise I would like that field
to be set to 0. Does that make sense? I have been playing with a case
statement but of course cant use the max(start_lcl_dt) field that I
just created in the query. Maybe I can get it to work with your
suggestion here and I am going to see if that fixes it :-) Thanks so
much for your ideas!

Reply to this Message

omon77@gmail.com - 05 Feb 2006 03:27 GMT

Serge,
is there a similar function for DB2 for z/OS?

Thanks!
--Mike.

Reply to this Message