Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2012
    Posts
    1

    Post Unanswered: DB2 query help needed

    Hi all,

    I posting this post to find out a way to build a DB2 query. Here please find my requirements below.

    Please consider the below table structure

    Part field1 field2 field3 field4 field5 field6 Actn_ind Timestamp
    1 100 ABCD 111 200 300 400 null 2012-12-01-23.10.57.615600
    1 101 ABCD 111 202 301 402 null 2012-12-04-23.10.57.615600
    1 103 ABCD 111 203 303 403 null 2012-12-05-23.10.57.615600
    3 100 ABCD 111 200 300 400 'D' 2012-12-02-23.10.57.615600
    3 101 ABCD 111 202 301 402 null 2012-12-03-23.10.57.615600
    3 102 ABCD 111 202 301 402 null 2012-12-05-23.10.57.615600


    in the above mentioned table structure table unique index is Part,field1,field2,field3,field4,field5,field6.

    Please find my requirement below

    i am getting field1,field2,field3 in input using this i need to select field 1 to 6 as a single result set cursor with the below conditions

    If the record present in both the part say ‘1’ and ‘3’, we need to make sure in part ‘3’ the record is not with “Action_IND” as ‘D’, if ‘D’ I should not give the record in output from both 1 and 3.

    If the record present in both the part say ‘1’ and ‘3’, and if the is “Action_IND” is not ‘D’ in partition 3, then we need to make sure we give only one record which has got max (timestamp).

    If the record present only in part 1 then i have to retrieve that record alone

    If the record is present only in part 3 without Action_ind 'D' then i have to retrieve that record alone

    If the record is present only in part 3 with Action_ind 'D' then i should not retrieve that record

    If the record present in both part 1 and 3 and if the Action_ind for part3 record is not 'D' then i have to retrieve only 1 record with max timestamp)

    Any helps are welcome using INNER JOIN or UNION or TEMPORARY DB2 tabes.

    Here whatever i have mentioned as only 1 record is related to output 6 fields not related to only 3 inputs
    Thanks in advance.....

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    What have you tried so far?

    We are willing to help you, but we will not do your work for you. Post what you have come up with so far.

    Andy

  3. #3
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963
    Use (UNION ALL)s (or OR), Actn_ind = 'D' means "Deleted".

  4. #4
    Join Date
    Nov 2011
    Posts
    334
    Hi,
    at first ,You d better to show the expected result。
    And there are a few questions about your requirement:
    1、 Does the part column always only have the value 1 and 3, or any other value : eg 2,4..
    2、Are there any rows which could be with part = 1 and actn_ind = 'D'。If there are, how
    to deal with these rows?
    3、Does the actn_ind column always only have the values 'null' and 'D'?
    4、Does each group only have at most 2 rows ?

    I think your rules can be simplified to this :
    1、pick up one max timestamp row from each partition group
    2、if the group contains the row with part = 3 and actn_ind = 'D' then discard the whole group
    3、if the group only contains the row with part = 1 ,then pick up one using rule 1。

    I will give you a test sql, you can try this:
    Code:
    select Part, field1, field2, field3, field4, field5, field6, Actn_ind ,TS
    from (
         select t.*
                ,max(part) over (partition by field1,field2,field3) as max_part
                ,max(actn_ind) over (partition by field1,field2,field3) as max_actn_ind
                ,row_number()over (partition by field1,field2,field3 order by ts desc) as rn
                from t
      ) where rn =1 and (( max_actn_ind <> 'D' or max_actn_ind is null )
              or ( max_part = 1 ))

Posting Permissions

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