Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Nov 2008
    Posts
    33

    Unanswered: Help to write a select and update Queries

    Hi,
    I have a team member table and it has some wrong records which I have to find them in the below scenario and update them using correct query.

    Table = TMMBR_DIM_H
    It has TMMBR_I,Eff_D ,EXPR_D ,ACTV_F and DEL_F

    here this table will have only one active record such as ACTV_F='Y' and DEL_F='N' , below one is the valid scenario and it is a valid recor
    Code:
    TMMBR_I	 EFF_D	      ACTV_F	EXPR_D	       DEL_F
    1603471	2009-08-19	N	2009-08-31	N
    1603471	2009-09-01	N	2009-09-05	N
    1603471	2009-09-06	N	2011-09-17	N
    1603471	2011-09-18	N	2011-11-27	N
    1603471	2011-11-28	Y	9999-12-31	N
    1603471	2012-05-10	N	2012-05-18	Y
    But the below example is not a valid a scenario and here I have to find all the records ,here you can see that expiry date supposed to be 2011-04-29 ,but it is showing another date , I have to find all these records from the table first and write a query to adjust them to the right date.

    The logic is that all team member records should follow a sequencing of Eff data and Expr date which has Actv_F='N' and DEL_F='N' with active record.

    Code:
    TMMBR_I	   EFF_D    ACTV_F	EXPR_D	     DEL_F
    437238	2009-05-24	N	2009-08-31	N
    437238	2009-09-01	N	2010-04-03	N
    437238	2010-04-04	N	2011-04-02	N
    437238	2011-04-03	N	2011-05-15	N
    437238	2011-04-30	Y	9999-12-31	N
    437238	2011-05-16	N	2011-05-18	Y
    I'm pasting two valid examples for more understanding.

    Code:
    TMMBR_I	EFF_D	      EXPR_D	      ACTV_F	   DEL_F
    2279189	2012-01-01	2012-02-15	N	Y
    2279189	2011-10-23	2011-12-31	N	Y
    2279189	2011-08-28	2011-10-22	N	Y
    2279189	2011-08-03	9999-12-31	Y	N

    Code:
    TMMBR_I	EFF_D	         EXPR_D	     ACTV_F	  DEL_F
    2033224	2010-06-01	2010-07-28	N	N
    2033224	2010-07-29	2010-08-03	N	Y
    2033224	2010-07-28	9999-12-31	Y	N

    Please help me in this regards.

    Thanks in Advance. Anjan.

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Nov 2008
    Posts
    33
    Hi , You mean you want more clarity on the problem statement?

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    No, I meant to show you how this problem can be solved.
    ---
    "It does not work" is not a valid problem statement.

  5. #5
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Is this what you need?
    Code:
    WITH a AS
    (SELECT TMMBR_I, EXPR_D,
                LEAD(EFF_D) over (order by EFF_D partition by TMMBR_I) AS next_eff_d
     FROM TMMBR_DIM_H
     WHERE DEL_F='N')
    SELECT TMMBR_I, EXPR_D
    FROM   a
    WHERE expr_d + 1 day < next_eff_d
      AND ACTV_F = 'N'
    (or something to this end).
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  6. #6
    Join Date
    Nov 2008
    Posts
    33
    Peter Thanks a lot for your reply . I tried executing the same query but getting the below error.
    Code:
     42601(-104)[IBM][CLI Driver][DB2/AIX64] SQL0104N  
    An unexpected token "order by EFF_D" was 
    found following "LEAD(EFF_D) over 
    (".  Expected tokens may include:  "<space>".  SQLSTATE=42601
     (0.02 secs)
    but I tried the same with the Comma separation between "order by EFF_D" "partition by TMMBR_I" like this

    Code:
    WITH a AS
    (SELECT TMMBR_I, EXPR_D,
     LEAD(EFF_D) over (order by EFF_D , partition by TMMBR_I ) AS next_eff_d,Actv_f
     FROM HREDM.TMMBR_DIM_H
     WHERE DEL_F='N' with ur)
    SELECT TMMBR_I, EXPR_D
    FROM   a
    WHERE expr_d + 1 day < next_eff_d  AND 
    ACTV_F = 'N' with ur
    getting below error
    Code:
     42601(-104)[IBM][CLI Driver][DB2/AIX64] SQL0104N 
    An unexpected token "by" was found following 
    "by EFF_D , partition".  
    Expected tokens may include:  "CONCAT".  SQLSTATE=42601
     (0.02 secs)
    please help me in this regards..

  7. #7
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    OK, I see; my mistake: "order by" and "partition by" should be interchanged:
    Code:
    WITH a AS
    (SELECT TMMBR_I, EXPR_D,
                LEAD(EFF_D) over (partition by TMMBR_I order by EFF_D) AS next_eff_d
     FROM TMMBR_DIM_H
     WHERE DEL_F='N')
    SELECT TMMBR_I, EXPR_D
    FROM   a
    WHERE expr_d + 1 day < next_eff_d
      AND ACTV_F = 'N'
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  8. #8
    Join Date
    Nov 2008
    Posts
    33
    thanks Peter , I think we need to add ACTV_F in the select of A table , also but it is not pulling any records .


    Below is the query written by friend
    Code:
    with A as
    (select eff_d -1 day as eff_d,tmmbr_i 
    from 
    ebi.tmmbr_dim_h where  actv_f='Y') ,
    B as
    (select tmmbr_i,expr_d 
    from ebi.tmmbr_dim_h 
    where actv_f <> 'Y'  group by tmmbr_i,expr_d),
    C as(
    select A.tmmbr_i,a.eff_d,b.expr_d from A,b
    where A.tmmbr_i=b.tmmbr_i and a.eff_D=b.expr_d)
    select distinct tmmbr_i from ebi.tmmbr_dim_h where actv_f <>  'Y' 
    minus
    select C.tmmbr_i from C with ur
    Can you plz let me know if we can re write this logic more understandable manner or in other words with a simple logic?

    Thanks for prompt responses.

  9. #9
    Join Date
    Nov 2008
    Posts
    33
    why I want to rewrite the logic is that , here we are just identifying the team member id which has wrong data, but I just want to find exact record so that we can write a update query on that.

  10. #10
    Join Date
    Mar 2003
    Posts
    280
    Quote Originally Posted by anjan.k View Post
    why I want to rewrite the logic is that , here we are just identifying the team member id which has wrong data, but I just want to find exact record so that we can write a update query on that.
    Why not update it directly? Something like (I did not try, since I didn't have any sample data to try with nor any information on the key):

    Code:
    MERGE INTO tmmbr_dim_h x
    USING (
       select TMMBR_I, EFF_D, ACTV_F
         , EXPR_D
         , LEAD(EFF_D) OVER (PARTITION BY TMMBR_I
                              ORDER BY EFF_D)  - 1 DAY AS CALC_EXPR_D
         , DEL_F
       from tmmbr_dim_h
    ) y
    on x.key_1 = y.key_1
    and ...
    and x.key_n = y.key_n
    when matched and y.EXPR_D <> y.CALC_EXPR_D and y.ACTV_F = 'N' then
        update set x.EXPR_D = y.CALC_EXPR_D;
    --
    Lennart

  11. #11
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by anjan.k View Post
    I think we need to add ACTV_F in the select of A table
    Yes, of course. Sorry for that.

    Quote Originally Posted by anjan.k View Post
    but it is not pulling any records
    That's good news, I would say: no erroneous EXPR_D fields!

    B.t.w., I could have misunderstood the problem: my query uses "expr_d + 1 day < next_eff_d" which means "there is a gap in the covered intervals".
    Make that "<" and you get "there is an overlap of the intervals". Or make that "<>" and you get both inconsistencies...
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  12. #12
    Join Date
    Nov 2011
    Posts
    334
    I have a few questions:
    1、is the EXPR_D of the active recorde always "9999-12-31"?
    2、which column you want to correct, EFF_D or EXPR_D?
    that means , for these two records :
    Code:
    437238	2011-04-03	N	2011-05-15	N
    437238	2011-04-30	Y	9999-12-31	N
    you want to correct to this :
    Code:
    437238	2011-04-03	N	2011-04-29	N
    437238	2011-04-30	Y	9999-12-31	N
    or this:
    Code:
    437238	2011-04-03	N	2011-05-15	N
    437238	2011-05-16	Y	9999-12-31	N
    3、how to do with the record : DEL_F = 'Y'? just ignore them?

  13. #13
    Join Date
    Nov 2008
    Posts
    33
    Appreciate your help Lennart,Peter and Fengsun ..you are so helpful..

    Hi Lennart,
    Your update query seems to be working when I add DEL_F='N" too as shown below. But this is huge Production table and I just want to make sure I got the records handy before I run a update query on the table . So request you help in finding the exact records. Thanks much and sorry to bother u much on this.
    Code:
    MERGE INTO tmmbr_dim_h x
    USING (
       select TMMBR_I, EFF_D, ACTV_F
         , EXPR_D
         , LEAD(EFF_D) OVER (PARTITION BY TMMBR_I
                              ORDER BY EFF_D)  - 1 DAY AS CALC_EXPR_D
         , DEL_F
       from tmmbr_dim_h
    ) y
    on x.tmmbr_i = y.tmmbr_i
    and x.eff_d = y.eff_d
    when matched and y.EXPR_D <> y.CALC_EXPR_D 
    and y.ACTV_F = 'N' and y.DEL_F='N' then
        update set x.EXPR_D = y.CALC_EXPR_D;
    Hi Fengen,
    Please find answers below for your questions.
    1. Correct, Expiry date of active records is always "9999-12-31"
    2.I want to Correct EXPR_D not the Eff_d
    3.Yes we should ignore the DEL_F='Y' records.

    Here main idea is to correct the records which are in overlap period of Expiry and Effective dates for the team member ids.

    Could you/Lennart help me in finding those records or the exact record didn't follow the sequencing of the dates.

  14. #14
    Join Date
    Mar 2003
    Posts
    280
    Quote Originally Posted by anjan.k View Post
    Appreciate your help Lennart,Peter and Fengsun ..you are so helpful..

    Hi Lennart,
    Your update query seems to be working when I add DEL_F='N" too as shown below. But this is huge Production table and I just want to make sure I got the records handy before I run a update query on the table . So request you help in finding the exact records. Thanks much and sorry to bother u much on this.
    Code:
    MERGE INTO tmmbr_dim_h x
    USING (
       select TMMBR_I, EFF_D, ACTV_F
         , EXPR_D
         , LEAD(EFF_D) OVER (PARTITION BY TMMBR_I
                              ORDER BY EFF_D)  - 1 DAY AS CALC_EXPR_D
         , DEL_F
       from tmmbr_dim_h
    ) y
    on x.tmmbr_i = y.tmmbr_i
    and x.eff_d = y.eff_d
    when matched and y.EXPR_D <> y.CALC_EXPR_D 
    and y.ACTV_F = 'N' and y.DEL_F='N' then
        update set x.EXPR_D = y.CALC_EXPR_D;
    Hi Fengen,
    Please find answers below for your questions.
    1. Correct, Expiry date of active records is always "9999-12-31"
    2.I want to Correct EXPR_D not the Eff_d
    3.Yes we should ignore the DEL_F='Y' records.

    Here main idea is to correct the records which are in overlap period of Expiry and Effective dates for the team member ids.

    Could you/Lennart help me in finding those records or the exact record didn't follow the sequencing of the dates.
    Try

    Code:
    With y (
       select TMMBR_I, EFF_D, ACTV_F
         , EXPR_D
         , LEAD(EFF_D) OVER (PARTITION BY TMMBR_I
                              ORDER BY EFF_D)  - 1 DAY AS CALC_EXPR_D
         , DEL_F
       from tmmbr_dim_h
    ) 
    Select * from Y
    Where y.EXPR_D <> y.CALC_EXPR_D 
    and y.ACTV_F = 'N' and y.DEL_F='N'
    This can of course be simplified, but I'm using an IPad right now

  15. #15
    Join Date
    Nov 2008
    Posts
    33
    Hi Lennart,
    I tried the query , it seems it is pulling some valid team members also, but the records in the table are like this shown below.

    Code:
    TMMBR_I	EFF_D	         EXPR_D      ACTV_F	DEL_F
    80807	1/23/2011	3/26/2011	N	N
    80807	2/27/2011	3/18/2011	N	Y
    80807	3/27/2011	8/13/2011	N	N
    80807	8/14/2011	12/17/2011	N	N
    80807	12/18/2011	3/24/2012	N	N
    80807	3/25/2012	6/14/2012	N	N
    80807	6/15/2012	8/2/2012	N	N
    80807	8/3/2012	9/1/2012	N	N
    80807	9/2/2012	12/31/9999	Y	N
    Code:
    TMMBR_I	EFF_D	              EXPR_D	ACTV_F	DEL_F
    89334	9/1/2009	12/10/2009	N	N
    89334	12/11/2009	3/7/2010	N	N
    89334	3/8/2010	3/14/2010	N	Y
    89334	3/15/2010	4/2/2010	N	N
    89334	4/3/2010	5/2/2010	N	N
    89334	5/3/2010	5/31/2010	N	N
    89334	6/1/2010	10/29/2010	N	N
    89334	10/30/2010	1/22/2011	N	N
    89334	1/23/2011	3/7/2011	N	Y
    89334	3/8/2011	12/31/9999	Y	N
    Here in the above two cases these records are valid , since the Expiry date of active record is greater than expiry date of the previous ones and basically we should not have over lap in the dates and it can overlap in the dates that records is with the DEL_F = 'Y' . I hope I make understand little better with this example. Thanks again.

Posting Permissions

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