Hi All,

I am running a query which retruning max completion data which it is expected to. Could anyone help me on this

db2 "SELECT distinct a.x,vp.y,a.z,o.d,a.z,c when vf.a_c=2 then 'Y' end as Disconnected FROM i.o o,i.o_v ov,i.v_f_d vf,
i.vd_i_p vp,i.o_a_m oam,i.a a WHERE o.d between "1259470800" and "1259938799" and o.s=6 and ov.o_i = o.o_i and vf.o_v_i = ov.o_v_i
and vf.a_c in (1,2) and vp.f_i=vf.f_i and vp.y in ('G9600','G9997','G9601','G9602','G9696','G9698',' Y5151','Y5152','Y5639','Y6322',
'Y6321','Y7680','Y7894','Y7681','Y7895','Y6016','Y 6312','Y5838','Y5858','Y5859','Y5860','Y5861','Y58 62','Y7079','Y7083','Y7084','
Y7085','Y7086','Y7087','Y7088','Y7091','Y7092','Y7 103','Y7113','Y7114','Y7115','Y7117','Y7118','Y711 9','Y7120','Y7121','Y7122','
Y7123','Y7124','Y7125') and oam.o_i = o.o_id and a.z in ('NY','NJ','CT') and a.a_i = oam.a_i and a.x = 121872916 and o.d = (select
max(o2.d) from i.o o2,i.o_a_m oam2 where oam2.o_i=o.o_i and o2.o_i=oam2.o_i group by oam2.a_i) order by a.x,vp.y,Disconnected DESC
FOR READ ONLY WITH UR"

Thanks

Sam

Maybe you could enlighten us to a few details like what the query is supposed to be doing, what the correct result should be, what the result is that you get, what's the schema, which DB2 version you are using on which platform, and what you have already attempted to identify and resolve the problem.

p.s: I hope the schema/table/column names are not the real ones. Otherwise, you should seriously think about this and assign some descriptive names to make the whole thing a bit better to maintain.

I completely agree with Stolze. An off hand guess without the info as described by him would be the subselect to get the o2.d. Is the OAM2 table really needed in there?
Dave

I understand and I am very sorry guys but I am not sure if I can share company's data. The problem with the query is it is returning the duplicate values even if I specify disctinct. Also the query is supposed to return all the rows from all the tables for only the max date and it is returning all the dates between "1259470800" and "1259938799". Any help on this would be greatly appreciated
here is the query

db2 "SELECT distinct a.address_id,vp.field_name,a.state,o.completion_da te,a.zip,case when vf.action_code=2 then
'Y' end as Disconnected FROM db2prod.purchase o,db2prod.purchase_video ov,db2prod.vd_feature_dtls vf,db2prod.vd_params vp,
db2prod.purchase_addr_map oam,db2prodmn.address a WHERE o.completion_date between "1259470800" and "1259938799" and o.status=6
and ov.purchase_id = o.purchase_id and vf.purchase_visual = ov.purchase_visual and vf.action_code in (1,2) and vp.feature_id=vf.feature_id and
vp.field_name in ('G9600','G9997','G9601','G9602','G9696','G9698',' Y5151','Y5152','Y5639','Y6322','Y6321','Y7680','Y7 894','Y7681',
'Y7895','Y6016','Y6312','Y5838','Y5858','Y5859','Y 5860','Y5861','Y5862','Y7079','Y7083','Y7084','Y70 85','Y7086','Y7087','Y7088','Y7091',
'Y7092','Y7103','Y7113','Y7114','Y7115','Y7117','Y 7118','Y7119','Y7120','Y7121','Y7122','Y7123','Y71 24','Y7125') and oam.purchase_id = o.purchase_id
and a.state in ('NY','NJ','CT') and a.address_id = oam.address_id order by a.address_id,vp.field_name,Disconnected DESC FOR READ ONLY WITH UR"

Please ignore the one I pasted above. I hope this helps a little bit

Thanks Sam

Hi,

Could somebody help. Although the distinct should not give me the duplicates, I am still getting it. Not sure where the duplicates is coming from. Not the date I am sure.
The data it is returning is

7184569|Y7895|NJ|01/12/2009|22222||
7184569|Y7895|NJ|01/01/2009|22222|Y|

2147821|Y7895|NJ|12/23/2009|11111||
2147821|Y7895|NJ|12/22/2009|11111|Y|

whereas I expect it to return only the maxdate out of these i.e

2147821|Y7895|NJ|12/23/2009|11111||

this alone

I used the max(finished_date) but it is still returning the duplicates. Also Iam wondering if convering the integer(finished_date) to char might help

Any suggestions please!!!!!!!

These are clearly distinct rows. Where do you see duplicates?

Why do you expect that? There is nothing in your query to ensure that.

There is no "max(finished_date)" (or "finished_date". for that matter) in your query.

Oh I meant completion_date. I posted the original query here coz the one I modified with the max(completion_date) didnot work.

2601891|Y7895|NJ|10/23/2009|77777||
2601891|Y7895|NJ|10/23/2009|77777|Y|

Here I see a duplication.

2147821|Y7895|NJ|12/23/2009|11111||
2147821|Y7895|NJ|12/22/2009|11111|Y|

Out of these I need only the max date which is

2147821|Y7895|NJ|12/23/2009|11111||

Thanks for replying Nick...sorry if I am missing information but I am still stuck with this query and cannot go any further with this. Do you know where I am going wrong

Sam

I think the answer should include a correlated subquery, something like Alternatively, you could use OLAP functions {e.g. row_number() or rank()} - you will find plenty of examples on this forum.