Results 1 to 8 of 8
  1. #1
    Join Date
    Dec 2009
    Posts
    31

    Unanswered: Query not returning the expected result

    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

  2. #2
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    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.
    Last edited by stolze; 01-01-10 at 08:38.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  3. #3
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    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

  4. #4
    Join Date
    Dec 2009
    Posts
    31
    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
    Last edited by dbsam; 01-03-10 at 15:49.

  5. #5
    Join Date
    Dec 2009
    Posts
    31
    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!!!!!!!

  6. #6
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by dbsam View Post
    Although the distinct should not give me the duplicates, I am still getting it. Not sure where the duplicates is coming from.
    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|
    These are clearly distinct rows. Where do you see duplicates?

    Quote Originally Posted by dbsam View Post
    whereas I expect it to return only the maxdate out of these i.e

    2147821|Y7895|NJ|12/23/2009|11111||
    Why do you expect that? There is nothing in your query to ensure that.

    Quote Originally Posted by dbsam View Post
    I used the max(finished_date) but it is still returning the duplicates.
    There is no "max(finished_date)" (or "finished_date". for that matter) in your query.
    ---
    "It does not work" is not a valid problem statement.

  7. #7
    Join Date
    Dec 2009
    Posts
    31
    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

  8. #8
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    I think the answer should include a correlated subquery, something like
    Code:
    ...
    where
    ...
    and o.completion_date = (
     select max(completion_date) 
     from db2prod.purchase 
     where purchase_id = o.purchase_id
    )
    ...
    Alternatively, you could use OLAP functions {e.g. row_number() or rank()} - you will find plenty of examples on this forum.
    ---
    "It does not work" is not a valid problem statement.

Posting Permissions

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