469,360 Members | 1,686 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,360 developers. It's quick & easy.

Newer to DB2 - How can I do this?

MJT
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) < ?

Feb 1 '06 #1
5 3280
MJT wrote:
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 ...

<snip>
Take a look at OLAP functions.
http://publib.boulder.ibm.com/infoce...n/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) < ?

--
Serge Rielau
DB2 Solutions Development
DB2 UDB for Linux, Unix, Windows
IBM Toronto Lab
Feb 1 '06 #2
MJT
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

Feb 1 '06 #3
MJT wrote:
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

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
--
Serge Rielau
DB2 Solutions Development
DB2 UDB for Linux, Unix, Windows
IBM Toronto Lab
Feb 1 '06 #4
MJT
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!

Feb 2 '06 #5
Serge,
is there a similar function for DB2 for z/OS?

Thanks!
--Mike.

Feb 5 '06 #6

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by Erik Cruz | last post: by
1 post views Thread by Glenn M | last post: by
7 posts views Thread by John | last post: by
2 posts views Thread by Frederik | last post: by
43 posts views Thread by James Stewart | last post: by
reply views Thread by zhoujie | last post: by
1 post views Thread by Marylou17 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.