Page 1 of 3 123 LastLast
Results 1 to 15 of 35
  1. #1
    Join Date
    Sep 2011
    Posts
    220

    Unanswered: How to match the year in Date field

    Hi

    Please find the below query,The query should return the matched and unmatched rows and need to matching the IN_DT column to PER_YEAR field having the less than or equal to 2011 and equal to 2012 and >=2013

    Datatypes

    CD_PLT char(6)
    PRE_NAME char(6)
    BASE_NAME char(6)
    SUF_NAME char(6)
    WKLY_CA integer
    IN_DT Date 10bytes
    MAX_IN_DT Date 10bytes not null default is 0001-01-01

    PER_YEAR is not a column in the table,but the PER_YEAR should have the values and should display like 2011,2012 and 2013( that is Current year and two future year) for every row.

    If the rows having IN_DT as less than or equal to 2011 means,that record should display as 2011 rows
    If the rows having IN_DT as less equal to 2012 means,that record should display as 2012 rows
    If the rows having IN_DT as greater than or equal to 2013 means,that record should display as 2013 rows

    If the rows IN_DT column does not having value means the row should repeat for all three years like 2011,2012,2013(See the output of last row)
    Code:
    EXEC SQL                                         
      DECLARE SER_CRR CURSOR WITH RETURN FOR
           SELECT DISTINCT                           
                  SELECT                     
    T1.CD_PLT
    ,T1.PRE_NAME
    ,T1.BASE_NAME
    ,T1.SUF_NAME
    ,T2.WKLY_CA
    ,T2.IN_DT
    ,T2.MAX_IN_DT
    
    FROM TABLE1 T1    
    
                         
     LEFT OUTER JOIN                                    
          TABLE2  T2 
    ON                      
      AND T2.CD_PLT     = T1.CD_PLT       
    AND T2.BASE_NAME    = T1.BASE_NAME      
    AND T2.PRE_NAME  = T1.PRE_NAME    
    AND T2.SUF_NAME = T1.SUF_NAME     
    
    WHERE                                              
                                                       
     
                                                       
                             
      T1.BASE_NAME   = :WS-BASE'          
     AND T1.PRE_NAME= :WS-PRE
    AND T1.SUF_NAME = :WS-SUF
                     
    END-EXEC.
    Code:
    Table1
    
    CD_PLT       BASE_NAME      PRE_NAME     SUF_NAME  
    ----------- ------------ -------------- ---------- 
    ABACC            6065        REWS           AE    
    ABACC            6065        REWS           AE    
    ABACC            6000        ERWV           DE    
    BDDER            R443        THYR           RE    
    BDDER            4565        RTY            RT    
    E1111            1111        TRW            HJ    
    FRGET            ADER        TRR            FR    
    FRGET            ADER        TRR            FR    
    FRGET            7777        GHYU           FF    
    HYYYY            8888        ADDD           WE    
     
    
    
    
    TABLE2
    
    CD_PLT       BASE_NAME      PRE_NAME     SUF_NAME  WKLY_CA      IN_DT           MAX_IN_DT          
    ----------- ------------ -------------- ---------- -------    ---------------- ------------
    ABACC            6065        REWS           AE     21          2009-01-13       2011-12-19
    ABACC            6065        REWS           AE     21          2010-01-12       2011-12-19
    ABACC            6000        ERWV           DE     46          2010-11-11       2011-12-19
    BDDER            R443        THYR           RE     57          2012-12-22       2011-12-19 
    BDDER            4565        RTY            RT     27          2011-01-16       2011-12-19
    BDDER            4565        RTY            RT     27          2014-03-25       2011-12-19
    FRGET            ADER        TRR            FR     26          2011-08-13       2011-12-19
    FRGET            ADER        TRR            FR     25          2011-09-19       2011-12-19
    FRGET            ADER        TRR            FR     29          2015-02-22       2015-03-25
    FRGET            7777        GHYU           FF     61          2012-10-13       2013-12-19
    FRGET            7777        GHYU           FF     61          2012-11-14       2013-12-19
    Code:
    output should be
    
    CD_PLT       BASE_NAME      PRE_NAME     SUF_NAME         WKLY_CA    PER_YEAR     IN_DT           MAX_IN_DT   
    ----------- ------------ -------------- ----------  ------------     -------   -------------------------- ------------   
    ABACC            6065        REWS           AE             21           2011 	2009-01-13       2011-12-19  
    ABACC            6065        REWS           AE             21           2011    2009-01-12       2011-12-19 
    ABACC            6065        REWS           AE             -            2012         -                -
    ABACC            6065        REWS           AE             -            2013         -                - 
    
    ABACC            6000        ERWV           DE             46           2011     2010-11-11       2011-12-19 
    ABACC            6000        ERWV           DE             -            2012         -                -
    ABACC            6000        ERWV           DE             -            2013         -               -
    
    BDDER            R443        THYR           RE             -            2011         -               - 
    BDDER            R443        THYR           RE             57           2012     2012-12-22       2011-12-19
    BDDER            R443        THYR           RE             -            2013         -                 -
    
    BDDER            4565        RTY            RT             27           2011     2011-01-16       2011-12-19
    BDDER            4565        RTY            RT             -            2012         -                -  
    BDDER            4565        RTY            RT             27           2013     2014-03-25       2014-03-25 
    
    FRGET            ADER        TRR            FR             26           2011     2011-08-13       2011-12-19  
    FRGET            ADER        TRR            FR             25           2011     2011-09-19       2011-12-19  
    FRGET            ADER        TRR            FR             -            2012         -                -
    FRGET            ADER        TRR            FR             29           2013     2015-02-22       2015-03-25
    
    FRGET            7777        GHYU           FF             61           2011         -                -
    FRGET            7777        GHYU           FF             61           2012     2012-10-13       2013-12-19  
    FRGET            7777        GHYU           FF             -            2012     2012-11-14       2013-12-19  
    FRGET            7777        GHYU           FF             -            2013         -
    
    HYYYY            8888        ADDD           WE             -            2011          -                -
    HYYYY            8888        ADDD           WE             -            2012          -                -
    HYYYY            8888        ADDD           WE             -            2013          -                -
    Please help

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    I'm sure the answer will include the YEAR() function and the CASE expression somewhere.

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I think there is (at least) an inconsistency in your sample.
    You specified WHERE clause in your query, then BASE_NAME, PER_NAME so on in output should be restricted to one value.
    But, you showed multiple values in your output.


    Anyhow, here are (simplified) examples:
    Note:
    Replace group_col with T1.CD_PLT, T1.PRE_NAME, T1.BASE_NAME and T1.SUF_NAME,
    and replace other_col with T2.WKLY_CA and T2.MAX_IN_DT.

    Example 1: It is neccesary to see bare_output twice.
    Code:
    WITH
     sample_bare_output(group_col , in_dt , other_col) AS (
    SELECT group_col
         , DATE(in_dt)
         , other_col
     FROM  (VALUES
               ('A1' , '2009-01-13' , 21)
             , ('A1' , '2009-01-12' , 21)
             , ('A2' , '2010-11-11' , 46)
             , ('B1' , '2012-12-22' , 57)
             , ('B2' , '2011-01-16' , 27)
             , ('B2' , '2014-03-25' , 27)
             , ('F1' , '2011-08-13' , 26)
             , ('F1' , '2011-09-19' , 25)
             , ('F1' , '2015-02-22' , 29)
             , ('F2' , '2012-10-13' , 61)
             , ('F2' , '2012-11-14' , 61)
           ) s(group_col , in_dt , other_col)
    )
    SELECT 
           q.group_col
         , s.other_col
         , p.per_year
         , s.in_dt
     FROM  (SELECT DISTINCT
                   group_col
             FROM  sample_bare_output
           ) q
     CROSS JOIN
           (VALUES ( YEAR(current_date)     )
                 , ( YEAR(current_date) + 1 )
                 , ( YEAR(current_date) + 2 )
           ) p(per_year)
     LEFT  OUTER JOIN
           (SELECT s.*
                 , YEAR(current_date) + 1
                   + SIGN( YEAR(in_dt) - YEAR(current_date) - 1 ) norm_year
             FROM  sample_bare_output s
           ) s
       ON  s.group_col = q.group_col
       AND s.norm_year = p.per_year
     ORDER BY
           q.group_col
         , p.per_year
    ;
    ------------------------------------------------------------------------------
    
    GROUP_COL OTHER_COL   PER_YEAR    IN_DT     
    --------- ----------- ----------- ----------
    A1                 21        2011 2009-01-13
    A1                 21        2011 2009-01-12
    A1                  -        2012 -         
    A1                  -        2013 -         
    A2                 46        2011 2010-11-11
    A2                  -        2012 -         
    A2                  -        2013 -         
    B1                  -        2011 -         
    B1                 57        2012 2012-12-22
    B1                  -        2013 -         
    B2                 27        2011 2011-01-16
    B2                  -        2012 -         
    B2                 27        2013 2014-03-25
    F1                 26        2011 2011-08-13
    F1                 25        2011 2011-09-19
    F1                  -        2012 -         
    F1                 29        2013 2015-02-22
    F2                  -        2011 -         
    F2                 61        2012 2012-10-13
    F2                 61        2012 2012-11-14
    F2                  -        2013 -         
    
      21 record(s) selected.

    Example 2: Reference to bare_output is once.
    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH
     sample_bare_output(group_col , in_dt , other_col) AS (
    SELECT group_col
         , DATE(in_dt)
         , other_col
     FROM  (VALUES
               ('A1' , '2009-01-13' , 21)
             , ('A1' , '2009-01-12' , 21)
             , ('A2' , '2010-11-11' , 46)
             , ('B1' , '2012-12-22' , 57)
             , ('B2' , '2011-01-16' , 27)
             , ('B2' , '2014-03-25' , 27)
             , ('F1' , '2011-08-13' , 26)
             , ('F1' , '2011-09-19' , 25)
             , ('F1' , '2015-02-22' , 29)
             , ('F2' , '2012-10-13' , 61)
             , ('F2' , '2012-11-14' , 61)
           ) s(group_col , in_dt , other_col)
    )
    SELECT 
           group_col
         , CASE norm_year
           WHEN per_year  THEN
                other_col
           END  AS other_col
         , per_year
         , CASE norm_year
           WHEN per_year  THEN
                in_dt
           END  AS in_dt
     FROM  (SELECT s.*
                 , ROW_NUMBER()
                      OVER( PARTITION BY group_col ) rn
                 , COUNT( NULLIF(
                             LAG(norm_year , 1 , 0)
                                OVER( PARTITION BY group_col
                                          ORDER BY norm_year )
                           , norm_year
                          )
                        )
                      OVER( PARTITION BY group_col ) count_distinct
                 , MIN( norm_year )
                      OVER( PARTITION BY group_col ) min_year
                 , MAX( norm_year )
                      OVER( PARTITION BY group_col ) max_year
             FROM  (SELECT s.*
                         , YEAR(current_date) + 1
                           + SIGN( YEAR(in_dt) - YEAR(current_date) - 1 ) norm_year
                     FROM  sample_bare_output s
                   ) s
           ) s
     INNER JOIN
           (VALUES ( YEAR(current_date)     , 1)
                 , ( YEAR(current_date) + 1 , 2)
                 , ( YEAR(current_date) + 2 , 3)
           ) p(per_year , k)
    
       ON  per_year = norm_year
       OR
      (    k  = 1
       AND per_year < min_year
    
       OR  k  = 2
       AND (   per_year < min_year
            OR per_year > max_year
            OR per_year > min_year AND per_year < max_year AND count_distinct = 2
           )
    
       OR  k  = 3
       AND per_year > max_year
      )
       AND rn = 1
    
     ORDER BY
           group_col
         , per_year
    ;
    ------------------------------------------------------------------------------
    
    GROUP_COL OTHER_COL   PER_YEAR    IN_DT     
    --------- ----------- ----------- ----------
    A1                 21        2011 2009-01-13
    A1                 21        2011 2009-01-12
    A1                  -        2012 -         
    A1                  -        2013 -         
    A2                 46        2011 2010-11-11
    A2                  -        2012 -         
    A2                  -        2013 -         
    B1                  -        2011 -         
    B1                 57        2012 2012-12-22
    B1                  -        2013 -         
    B2                 27        2011 2011-01-16
    B2                  -        2012 -         
    B2                 27        2013 2014-03-25
    F1                 26        2011 2011-08-13
    F1                 25        2011 2011-09-19
    F1                  -        2012 -         
    F1                 29        2013 2015-02-22
    F2                  -        2011 -         
    F2                 61        2012 2012-10-13
    F2                 61        2012 2012-11-14
    F2                  -        2013 -         
    
      21 record(s) selected.
    Last edited by tonkuma; 09-28-11 at 00:34. Reason: Reduce redundancy in ON clause in Example 2. Remove "ORDER BY norm_year" from "ROW_NUMBER() OVER(...)"

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    If your DB2 version/release supports LISTAGG function.

    Example 3:
    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH
     sample_bare_output(group_col , in_dt , other_col) AS (
    SELECT group_col
         , DATE(in_dt)
         , other_col
     FROM  (VALUES
               ('A1' , '2009-01-13' , 21)
             , ('A1' , '2009-01-12' , 21)
             , ('A2' , '2010-11-11' , 46)
             , ('B1' , '2012-12-22' , 57)
             , ('B2' , '2011-01-16' , 27)
             , ('B2' , '2014-03-25' , 27)
             , ('F1' , '2011-08-13' , 26)
             , ('F1' , '2011-09-19' , 25)
             , ('F1' , '2015-02-22' , 29)
             , ('F2' , '2012-10-13' , 61)
             , ('F2' , '2012-11-14' , 61)
           ) s(group_col , in_dt , other_col)
    )
    SELECT 
           group_col
         , CASE norm_year
           WHEN per_year  THEN
                other_col
           END  AS other_col
         , per_year
         , CASE norm_year
           WHEN per_year  THEN
                in_dt
           END  AS in_dt
     FROM  (SELECT s.*
                 , LISTAGG( CHAR(norm_year) )
                      OVER( PARTITION BY group_col ) norm_year_list
                 , ROW_NUMBER()
                      OVER( PARTITION BY group_col ) rn
             FROM  (SELECT s.*
                         , YEAR(current_date) + 1
                           + SIGN( YEAR(in_dt) - YEAR(current_date) - 1 ) norm_year
                     FROM  sample_bare_output s
                   ) s
           ) s
     INNER JOIN
           (VALUES ( YEAR(current_date)     )
                 , ( YEAR(current_date) + 1 )
                 , ( YEAR(current_date) + 2 )
           ) p(per_year)
       ON  per_year = norm_year
       OR  LOCATE( VARCHAR(per_year) , norm_year_list ) = 0
       AND rn = 1
     ORDER BY
           group_col
         , per_year
    ;
    ------------------------------------------------------------------------------
    
    GROUP_COL OTHER_COL   PER_YEAR    IN_DT     
    --------- ----------- ----------- ----------
    A1                 21        2011 2009-01-13
    A1                 21        2011 2009-01-12
    A1                  -        2012 -         
    A1                  -        2013 -         
    A2                 46        2011 2010-11-11
    A2                  -        2012 -         
    A2                  -        2013 -         
    B1                  -        2011 -         
    B1                 57        2012 2012-12-22
    B1                  -        2013 -         
    B2                 27        2011 2011-01-16
    B2                  -        2012 -         
    B2                 27        2013 2014-03-25
    F1                 26        2011 2011-08-13
    F1                 25        2011 2011-09-19
    F1                  -        2012 -         
    F1                 29        2013 2015-02-22
    F2                  -        2011 -         
    F2                 61        2012 2012-10-13
    F2                 61        2012 2012-11-14
    F2                  -        2013 -         
    
      21 record(s) selected.
    Last edited by tonkuma; 09-27-11 at 14:06. Reason: Remove VARCHAR from "VALUES ( VARCHAR(YEAR(current_date) ) ) , ..."

  5. #5
    Join Date
    Sep 2011
    Posts
    220
    Hi

    Thanks for the reply.I just misplet the code, here i pasted the correct code instead of below

    WHERE

    T1.BASE_NAME = :WS-BASE'
    AND T1.PRE_NAME= :WS-PRE
    AND T1.SUF_NAME = :WS-SUF


    Code:
    WHERE  
    
        T1.CD_PLT BETWEEN :WS-PLT-L1 AND WS-PLT-H1
    AND
        T1.PRE_NAME BETWEEN :WS-PRE-L1 AND WS-PRE-H1
    AND
        T1.BASE_NAME BETWEEN :WS-BASE-L1 AND WS-BASE-H1
    AND
        T1.SUF_NAME BETWEEN :WS-SUF-L1 AND WS-SUF-H1
    We can give number of CD_PLT in the screen

    Below code i just modified based on my code.
    Code:
    
    EXEC SQL                                         
      DECLARE SER_CRR CURSOR WITH RETURN FOR
    WITH
     sample_bare_output(
    T1.CD_PLT
    ,T1.PRE_NAME
    ,T1.BASE_NAME
    ,T1.SUF_NAME
    ,T2.WKLY_CA
    ,T2.IN_DT
    ,T2.MAX_IN_DT
    ) AS (
    
    SELECT 
    T1.CD_PLT
    ,T1.PRE_NAME
    ,T1.BASE_NAME
    ,T1.SUF_NAME
    ,T2.WKLY_CA
    ,DATE(T2.IN_DT)
    ,T2.MAX_IN_DT
     
    
    FROM  (VALUES
               ('ABACC','RFWS','6065','AE','2009-01-13',21)
             , ('ABACC','RFWS','6065','AE','2009-01-12',21)
             , ('ABACC','ERWV','6000','DE','2010-11-11',46)
             , ('BDDER','THYR','R443','RE','2012-12-22',57)
             
           ) s(
    T1.CD_PLT
    ,T1.PRE_NAME
    ,T1.BASE_NAME
    ,T1.SUF_NAME
    ,T2.WKLY_CA
    ,T2.IN_DT
    ,T2.MAX_IN_DT
    )
    )
    SELECT 
    
    Q.CD_PLT
    ,Q.PRE_NAME
    ,Q.BASE_NAME 
    ,Q.SUF_NAME
    ,S.WKLY_CA
    ,P.PER_YEAR
    ,S.IN_DT
    
    
     FROM  (SELECT DISTINCT
       
    T1.CD_PLT
    ,T1.PRE_NAME
    ,T1.BASE_NAME
    ,T1.SUF_NAME
             FROM  sample_bare_output
           ) q
     CROSS JOIN
           (VALUES ( YEAR(current_date)     )
                 , ( YEAR(current_date) + 1 )
                 , ( YEAR(current_date) + 2 )
           ) p(per_year)
     LEFT  OUTER JOIN
           (SELECT s.*
                 , YEAR(current_date) + 1
                   + SIGN( YEAR(T2.IN_DT) - YEAR(current_date) - 1 ) norm_year
             FROM  sample_bare_output s
           ) s
      
    on S.CD_PLT        = Q.CD_PLT                            
               AND S.BASE_NAME       = Q.BASE_NAME   
               AND S.PRE_NAME    = Q.PRE_NAME 
               AND S.SUF_NAME    = Q.SUF_NAME 
    
       AND s.norm_year = p.per_year
     ORDER BY
    
    Q.CD_PLT
    ,Q.PRE_NAME
    ,Q.BASE_NAME 
    ,Q.SUF_NAME
         , p.per_year
    ;
    Please let me know whether the above code is correct one or need to add some of the additional commands.

    Here i didnot mentioned the table name and i dont know how to use and where can i do modify in the actual code..

    This is Stored procedure and the result set return to Front end screen(User has to enter the all CD_PLT and BASE_NAME,PRE_NAME,SUF_NAME values)
    we need to fetch the related rows fro the table and display in the screen.

    PER_YEAR is not a column in the Table...It is used for display the year value in the screen,but we need to return the year value along with the query results to the screen

    More over the FROM VALUES( ) should not hard coded...because
    T2.WKLY_CA
    ,T2.IN_DT
    ,T2.MAX_IN_DT
    are editable field and the user can change the value

    Please let me know,if any

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Please let me know whether the above code is correct one or need to add some of the additional commands.
    You can test the query by yourself on your environment.

    I reviews your op again and realized...
    a) You may want to replace sample_bare_output in my example with your TABLE2.
    I thought that the example titled "output should be" in your op can be produced from TABLE2 only.

    Briefly looking into your last code...
    b) the qualifiers in there must be syntax error.
    Code:
     sample_bare_output(
    T1.CD_PLT
    ,T1.PRE_NAME
    ,T1.BASE_NAME
    ,T1.SUF_NAME
    ,T2.WKLY_CA
    ,T2.IN_DT
    ,T2.MAX_IN_DT
    ) AS (
    Code:
    FROM  (VALUES
    ...
           ) s(
    T1.CD_PLT
    ,T1.PRE_NAME
    ,T1.BASE_NAME
    ,T1.SUF_NAME
    ,T2.WKLY_CA
    ,T2.IN_DT
    ,T2.MAX_IN_DT
    )
    c) Number of items in values list is not same as number of column names.


    d) If you are using DB2 on z/OS, some syntax need to be changed.
    For example:
    d-1) "(VALUES ... )" should be replaced by "SELECT ... FROM sysibm.sysdummy1" and "UNION ALL".
    d-2) "CROSS JOIN" should be replaced by ","(traditional join syntax).
    Last edited by tonkuma; 09-28-11 at 10:26. Reason: Replace all note "a)".

  7. #7
    Join Date
    Sep 2011
    Posts
    220
    Hi;

    Thanx for the quick reply
    You can test the query by yourself on your environment.
    I have pasted the whole query in the previous post.I dont konow ,WITH SAMPLE_BARE_OUTPUT will be after declare cursor statement

    a) It is unclear nested level of subselects.
    In other word, it is unclear which subselect is subquery of which subselect.
    i have just modified the with original code whatever you have given in the post

    b) the qualifiers in there must be syntax error.
    I used qualifiers T1 for the Table1 and T2 for the Table2,Please let me know whether i have to use 'S' as qualifier instead T1 and T2

    c) Number of items in values list is not same as number of column names.

    yes,apology for that,,I missed one column(T2.MAX_IN_DT) in the post

    d) If you are using DB2 on z/OS, some syntax need to be changed.
    For example:
    d-1) "(VALUES ... )" should be replaced by "SELECT ... FROM sysibm.sysdummy1" and "UNION ALL".
    d-2) "CROSS JOIN" should be replaced by ","(traditional join syntax).
    we are using Version 9 Rel 1

    Please let me know ,where to use "SELECT ... FROM sysibm.sysdummy1" and "UNION ALL".

    Please help me and need some clarity on that...

  8. #8
    Join Date
    Sep 2011
    Posts
    220
    Hi;

    Here I have pasted the modified code as per your direction,Please check and post the correct one..Please

    Code:
    
    EXEC SQL                                         
      DECLARE SER_CRR CURSOR WITH RETURN FOR
    
    SELECT
    T1.CD_PLT
    ,T1.PRE_NAME
    ,T1.BASE_NAME
    ,T1.SUF_NAME
    ,T2.WKLY_CA
    ,T2.IN_DT
    ,T2.MAX_IN_DT
     AS (
    
    SELECT 
    T1.CD_PLT
    ,T1.PRE_NAME
    ,T1.BASE_NAME
    ,T1.SUF_NAME
    ,T2.WKLY_CA
    ,DATE(T2.IN_DT)
    ,T2.MAX_IN_DT
    
    FROM  (VALUES(SELECT                     
    T1.CD_PLT
    ,T1.PRE_NAME
    ,T1.BASE_NAME
    ,T1.SUF_NAME
    ,T2.WKLY_CA
    ,T2.IN_DT
    ,T2.MAX_IN_DT
    
    FROM TABLE1 T1    
                       
     LEFT OUTER JOIN                                    
          TABLE2  T2 
    ON                      
      AND T2.CD_PLT     = T1.CD_PLT       
    AND T2.BASE_NAME    = T1.BASE_NAME      
    AND T2.PRE_NAME  = T1.PRE_NAME    
    AND T2.SUF_NAME = T1.SUF_NAME     
    
    WHERE  
    
        T1.CD_PLT BETWEEN :WS-PLT-L1 AND WS-PLT-H1
    AND
        T1.PRE_NAME BETWEEN :WS-PRE-L1 AND WS-PRE-H1
    AND
        T1.BASE_NAME BETWEEN :WS-BASE-L1 AND WS-BASE-H1
    AND
        T1.SUF_NAME BETWEEN :WS-SUF-L1 AND WS-SUF-H1)                                                                
                                                                     
             
           ) s(
    T1.CD_PLT
    ,T1.PRE_NAME
    ,T1.BASE_NAME
    ,T1.SUF_NAME
    ,T2.WKLY_CA
    ,T2.IN_DT
    ,T2.MAX_IN_DT
    )
    )
    SELECT 
    
    Q.CD_PLT
    ,Q.PRE_NAME
    ,Q.BASE_NAME 
    ,Q.SUF_NAME
    ,S.WKLY_CA
    ,P.PER_YEAR
    ,S.IN_DT
    
     FROM  (SELECT DISTINCT
       
    T1.CD_PLT
    ,T1.PRE_NAME
    ,T1.BASE_NAME
    ,T1.SUF_NAME
             FROM  
           ) q
     INNER JOIN
           (VALUES ( YEAR(current_date)     )
                 , ( YEAR(current_date) + 1 )
                 , ( YEAR(current_date) + 2 )
           ) p(per_year)
     LEFT  OUTER JOIN
           (SELECT s.*
                 , YEAR(current_date) + 1
                   + SIGN( YEAR(T2.IN_DT) - YEAR(current_date) - 1 ) norm_year
             FROM   s
           ) s
      
    on S.CD_PLT        = Q.CD_PLT                            
               AND S.BASE_NAME       = Q.BASE_NAME   
               AND S.PRE_NAME    = Q.PRE_NAME 
               AND S.SUF_NAME    = Q.SUF_NAME 
    
       AND s.norm_year = p.per_year
     ORDER BY
    
    Q.CD_PLT
    ,Q.PRE_NAME
    ,Q.BASE_NAME 
    ,Q.SUF_NAME
         , p.per_year
    The select statement also repeated multi times..

    Please help me for regarding that...

  9. #9
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Please see the Syntax (and Description) in manuals step by step
    and construct your query conforming to syntax.

    (1) DECLARE CURSOR
    DB2 9 - DB2 SQL - DECLARE CURSOR
    Code:
    >>-DECLARE--cursor-name--+----------------------------------+--->
                             | .-ASENSITIVE------------.        |   
                             '-+-----------------------+-SCROLL-'   
                               +-INSENSITIVE-----------+            
                               |           .-DYNAMIC-. |            
                               '-SENSITIVE-+---------+-'            
                                           '-STATIC--'              
    
               .-----------------------------.   
               V  (1)                        |   
    >--CURSOR---------+--------------------+-+---------------------->
                      +-holdability--------+     
                      +-returnability------+     
                      '-rowset-positioning-'     
    
    >--FOR--+-select-statement-+-----------------------------------><
            '-statement-name---'
    You should write select-statement in CURSOR, if not use dynamicaly prepared statement.

    (2) select-statement
    DB2 9 - DB2 SQL - select-statement
    Code:
    >>-+-----------------------------------+--fullselect------------>
       |       .-,-----------------------. |               
       |       V                         | |               
       '-WITH----common-table-expression-+-'               
    
       .--------------------------.       
       V                          | (2)   
    >----+----------------------+-+--------------------------------><
         +-update-clause--------+         
         |                  (1) |         
         +-read-only-clause-----+         
         +-optimize-clause------+         
         +-isolation-clause-----+         
         +-queryno-clause-------+         
         '-SKIP LOCKED DATA-----'
    select-statement is
    (optional) WITH common-table-expression and fullselect ...

    (3) fullselect
    DB2 9 - DB2 SQL - fullselect
    Code:
    >>-+-subselect----+--------------------------------------------->
       '-(fullselect)-'   
    
       .---------------------------------------------------.   
       V                                                   |   
    >----+-----------------------------------------------+-+-------->
         |                .-DISTINCT-.                   |     
         '-+-UNION-----+--+----------+--+-subselect----+-'     
           +-EXCEPT----+  '-ALL------'  '-(fullselect)-'       
           '-INTERSECT-'                                       
    
    >--+-----------------+--+--------------------+-----------------><
       '-order-by-clause-'  '-fetch-first-clause-'
    You are not using UNION/EXCEPT/INTERSECT.
    So, you should write subselect in your cursor.

    (4) subselect
    DB2 9 - DB2 SQL - subselect
    Code:
    >>-select-clause--from-clause--+--------------+----------------->
                                   '-where-clause-'   
    
    >--+-----------------+--+---------------+----------------------->
       '-group-by-clause-'  '-having-clause-'   
    
    >--+-----------------+--+--------------------+-----------------><
       '-order-by-clause-'  '-fetch-first-clause-'
    First clause is a select-clause

    (5) select-clause
    DB2 9 - DB2 SQL - select-clause
    Code:
               .-ALL------.   
    >>-SELECT--+----------+----------------------------------------->
               '-DISTINCT-'   
    
    >--+-*----------------------------------------------+----------><
       | .-,------------------------------------------. |   
       | V                                            | |   
       '---+-expression-+-------------------------+-+-+-'   
           |            | .-AS-.                  | |       
           |            '-+----+--new-column-name-' |       
           '-+-table-name-------+-.*----------------'       
             +-view-name--------+                           
             '-correlation-name-'
    Repeat expression [AS new-column-name] after SELECT keyword.

    You will find a syntax error, now.
    Because, no parenthesis after AS according to syntax,
    while you wrote "(SELECT" after ",T2.MAX_IN_DT AS"

    Code:
      DECLARE SER_CRR CURSOR WITH RETURN FOR
    
    SELECT
    T1.CD_PLT
    ,T1.PRE_NAME
    ,T1.BASE_NAME
    ,T1.SUF_NAME
    ,T2.WKLY_CA
    ,T2.IN_DT
    ,T2.MAX_IN_DT
     AS (
    
    SELECT 
    T1.CD_PLT
    ...
    ...

    You might realized in step(2) that "WITH sample_bare_output (...) AS (" in my code was a common-table-expression.

    And, you might also find a syntax error(that is qualify column names in parentheses), like
    "(T1.CD_PLT ,T1.PRE_NAME ..." in your code in a post of two days ago,
    according to the syntax of common-table-expression.
    Code:
    EXEC SQL                                         
      DECLARE SER_CRR CURSOR WITH RETURN FOR
    WITH
     sample_bare_output(
    T1.CD_PLT
    ,T1.PRE_NAME
    ,T1.BASE_NAME
    ,T1.SUF_NAME
    ,T2.WKLY_CA
    ,T2.IN_DT
    ,T2.MAX_IN_DT
    ) AS (
    
    ...

    (6) common-table-expression
    DB2 9 - DB2 SQL - common-table-expression
    Code:
    >>-table-identifier--+---------------------------+--AS---------->
                         |    .-,---------------.    |       
                         |    V                 |    |       
                         '-(----+-------------+-+--)-'       
                                '-column-name-'              
    
    >--(fullselect)------------------------------------------------><
    Last edited by tonkuma; 09-29-11 at 18:47.

  10. #10
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Briefly looking into your code,
    you might go right direction.

    But, I felt that you might be not understand the syntax of common-table-expression,
    then you wrote unnecessary (repeated) select and unnecesary (out of syntax) qualifiers, so on.


    Another issue (apart from syntax) might be in join condition of table1 and table2.
    There are duplicated rows in table1 and duplicated matching rows in table2.

    For example:
    Code:
    Table1
    
    CD_PLT       BASE_NAME      PRE_NAME     SUF_NAME  
    ----------- ------------ -------------- ---------- 
    ABACC            6065        REWS           AE    
    ABACC            6065        REWS           AE
    and
    Code:
    TABLE2
    
    CD_PLT       BASE_NAME      PRE_NAME     SUF_NAME  WKLY_CA      IN_DT           MAX_IN_DT          
    ----------- ------------ -------------- ---------- -------    ---------------- ------------
    ABACC            6065        REWS           AE     21          2009-01-13       2011-12-19
    ABACC            6065        REWS           AE     21          2010-01-12       2011-12-19
    If only join conditions were
    Code:
    ON                      
        T2.CD_PLT    = T1.CD_PLT       
    AND T2.BASE_NAME = T1.BASE_NAME      
    AND T2.PRE_NAME  = T1.PRE_NAME    
    AND T2.SUF_NAME  = T1.SUF_NAME
    then matching combinations would be 4(result rows of the join were 4), like
    Code:
    table1 |table2 |
    -------+-------+
    (row1) |(row1) |
    (row1) |(row2) |
    (row2) |(row1) |
    (row2) |(row2) |

  11. #11
    Join Date
    Sep 2011
    Posts
    220
    Hi;

    Please find the code below which i have modified as per recent updates

    Code:
    WITH
     sample_bare_output(
    CD_PLT
    ,PRE_NAME
    ,BASE_NAME
    ,SUF_NAME
    ,WKLY_CA
    ,IN_DT
    ,MAX_IN_DT
    ) AS (
    
    SELECT 
    
    CD_PLT
    ,PRE_NAME
    ,BASE_NAME
    ,SUF_NAME
    ,WKLY_CA
    ,DATE(IN_DT)
    ,MAX_IN_DT
    
    FROM(   
    SELECT                   
     T1.CD_PLT
    ,T1.PRE_NAME
    ,T1.BASE_NAME
    ,T1.SUF_NAME
    ,T2.WKLY_CA
    ,T2.IN_DT
    ,T2.MAX_IN_DT                                                             
                                                       
     FROM            TABLE1    T1                      
                                                       
     LEFT OUTER JOIN                                    
                     TABLE2  T2 
    ON                      
         T2.CD_PLT   = T1.CD_PLT       
    AND T2.BASE_NAME = T1.BASE_NAME      
    AND T2.PRE_NAME  = T1.PRE_NAME    
    AND T2.SUF_NAME = T1.SUF_NAME     
    
    )
           ) s(
    
    CD_PLT
    ,PRE_NAME
    ,BASE_NAME
    ,SUF_NAME
    ,WKLY_CA
    ,IN_DT
    ,MAX_IN_DT
    
    )
    )
    SELECT 
    
    Q.CD_PLT
    ,Q.PRE_NAME
    ,Q.BASE_NAME 
    ,Q.SUF_NAME
    ,S.WKLY_CA
    ,P.PER_YEAR
    ,S.IN_DT
    
     FROM  (SELECT DISTINCT
        
    CD_PLT
    ,PRE_NAME
    ,BASE_NAME
    ,SUF_NAME
             FROM  sample_bare_output
           ) q
     INNER JOIN
           (VALUES ( YEAR(current_date)     )
                 , ( YEAR(current_date) + 1 )
                 , ( YEAR(current_date) + 2 )
           ) p(per_year)
     LEFT  OUTER JOIN
           (SELECT s.*
                 , YEAR(current_date) + 1
                   + SIGN( YEAR(T160A.DT_EFF_IN) - YEAR(current_date) - 1 ) norm_year
             FROM  sample_bare_output s
           ) s
     
                on S.CD_PLT      = Q.CD_PLT                            
               AND S.BASE_NAME   = Q.BASE_NAME   
               AND S.PRE_NAME    = Q.PRE_NAME 
               AND S.SUF_NAME    = Q.SUF_NAME 
    
       AND s.norm_year = p.per_year
     ORDER BY
        
    
    Q.CD_PLT
    ,Q.PRE_NAME
    ,Q.BASE_NAME 
    ,Q.SUF_NAME
    , p.per_year
    ;
    I have compiled that code in the IBM Command Editor tool Version DB2v8.1.9.917 and got the error message like below

    "SQL0104N An unexpected token "<EMPTY>" was found following "". Expected
    tokens may include: "CORRELATION NAME". SQLSTATE=42601

    SQL0104N An unexpected token "<EMPTY>" was found following "". Expected tokens may include: "CORRELATION NAME".
    sqlcode : -104 "

    Why we are using the LEFT OUTER JOIN is We should return the unmatched rows also from the Table1 in the result set..

    If the unmatched rows return in the result set means..the Result set will be like below along with all matched rows in the first day post
    Code:
    XBSDF            6091        GHJH           AE             -            2011         -                -
    XBSDF            6091        GHJH           AE             -            2012         -                - 
    XBSDF            6091        GHJH           AE             -            2013         -                -
    Please giude me,where is the erorr..and let me know where to use SELECT ... FROM sysibm.sysdummy1" and "UNION ALL".
    THANKS IN ADVANCE

  12. #12
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    "Please giude me,where is the erorr..and let me know where to use SELECT"
    some people really think this forum is the nirvana to all problems. they don't even check what they are executing just using cut/paste from what was proposed..
    at least I would like to understand what I am doing and being able to re-use this in the future..
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  13. #13
    Join Date
    Sep 2011
    Posts
    220
    Hi;

    I just modified the previous post code and compiled
    Code:
    WITH
     sample_bare_output(CD_PLT,PRE_NAME,BASE_NAME,SUF_NAME,WKLY_CA,IN_DT,MAX_IN_DT) AS (
     
    SELECT                   
     T1.CD_PLT
    ,T1.PRE_NAME
    ,T1.BASE_NAME
    ,T1.SUF_NAME
    ,T2.WKLY_CA
    ,T2.IN_DT
    ,T2.MAX_IN_DT                                                             
                                                       
     FROM            TABLE1    T1                      
                                                       
     LEFT OUTER JOIN                                    
                     TABLE2  T2 
    ON                      
         T2.CD_PLT   = T1.CD_PLT       
    AND T2.BASE_NAME = T1.BASE_NAME      
    AND T2.PRE_NAME  = T1.PRE_NAME    
    AND T2.SUF_NAME = T1.SUF_NAME     
    
    ),
     s(CD_PLT,PRE_NAME,BASE_NAME,SUF_NAME,WKLY_CA,IN_DT,MAX_IN_DT) as (
    
    SELECT 
    Q.CD_PLT
    ,Q.PRE_NAME
    ,Q.BASE_NAME 
    ,Q.SUF_NAME
    ,S.WKLY_CA
    ,P.PER_YEAR
    ,S.IN_DT
     FROM  (SELECT DISTINCT
        
    CD_PLT
    ,PRE_NAME
    ,BASE_NAME
    ,SUF_NAME
             FROM  sample_bare_output
           ) q
     INNER JOIN
           (VALUES ( YEAR(current_date)     )
                 , ( YEAR(current_date) + 1 )
                 , ( YEAR(current_date) + 2 )
           ) p(per_year)
     LEFT  OUTER JOIN
           (SELECT s.*
                 , YEAR(current_date) + 1
                   + SIGN( YEAR(T160A.DT_EFF_IN) - YEAR(current_date) - 1 ) 
    
    norm_year
             FROM  sample_bare_output s
           ) s
     
                on S.CD_PLT      = Q.CD_PLT                            
               AND S.BASE_NAME   = Q.BASE_NAME   
               AND S.PRE_NAME    = Q.PRE_NAME 
               AND S.SUF_NAME    = Q.SUF_NAME 
    
       AND s.norm_year = p.per_year
     ORDER BY
        
    
    Q.CD_PLT
    ,Q.PRE_NAME
    ,Q.BASE_NAME 
    ,Q.SUF_NAME
    , p.per_year
    ;
    got the error like "SQL0104N An unexpected token "(" was found following "". Expected tokens may include: ", )" "
    But where ever i used the " ) " in that code means it will produce the same error..
    Thnks in advance..

  14. #14
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    but is s(CD_PLT,....... is this correct syntax ??
    some pd/psi might help...
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  15. #15
    Join Date
    Sep 2011
    Posts
    220
    Any updates please..

Posting Permissions

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