Page 1 of 2 12 LastLast
Results 1 to 15 of 24
  1. #1
    Join Date
    Sep 2011
    Posts
    220

    Unanswered: select query should return latest one past row

    Hi;
    Please find the below select query.


    The latest one Past IN_DT record should comes under the 2012(current year) year row..(if the 2012 year row have non-zero value in ITEM_NO in TABLE3,if not that rows are not fetched )

    if the 2012 year row have the ZERo value ITEM_NO means that related 2012 and rows will not fetch from TABLE2

    for that case,It should come under the next year rows like 2013 (If 2013 row will have non-zero ITEM_NO)

    Code:
    WITH
     TABLE2
    ( CD_PLT , BASE_NAME , PRE_NAME , SUF_NAME , WKLY_CA , IN_DT ) AS (
    VALUES
      ( 'ABACC' , '6065' , 'REWS' , 'AE' , 21 , '2009-01-13' )
    , ( 'ABACC' , '6065' , 'REWS' , 'AE' , 21 , '2010-01-12' )
    , ( 'ABACC' , '6000' , 'ERWV' , 'DE' , 26 , '2010-10-11' )
    , ( 'ABACC' , '6000' , 'ERWV' , 'DE' , 46 , '2011-11-12' )
    , ( 'ABACC' , '6000' , 'ERWV' , 'DE' , 56 , '2012-11-11' )
    , ( 'ABADD' , '9011' , 'WEST' , 'DR' , 77 , '2011-03-01' )
    )
    , TABLE3
    ( CD_PLT , BASE_NAME , PRE_NAME , SUF_NAME , C_YEAR , ITEM_NO ) AS (
    VALUES
      ( 'ABACC' , '6065' , 'REWS' , 'AE' , '2010' ,  200 )
    , ( 'ABACC' , '6065' , 'REWS' , 'AE' , '2012' ,  209 )
    , ( 'ABACC' , '6065' , 'REWS' , 'AE' , '2013' ,  310 )
    , ( 'ABACC' , '6065' , 'REWS' , 'AE' , '2014' ,  100 )
    , ( 'ABACC' , '6000' , 'ERWV' , 'DE' , '2011' ,    0 )
    , ( 'ABACC' , '6000' , 'ERWV' , 'DE' , '2012' ,  500 )
    , ( 'ABACC' , '6000' , 'ERWV' , 'DE' , '2013' ,  600 )
    , ( 'ABACC' , '6000' , 'ERWV' , 'DE' , '2014' ,  700 )
    , ( 'ABADD' , '9011' , 'WEST' , 'DR' , '2011' ,    0 )
    , ( 'ABADD' , '9011' , 'WEST' , 'DR' , '2013' , 1200 )
    )
    SELECT t3.CD_PLT
         , t3.BASE_NAME
         , t3.PRE_NAME
         , t3.SUF_NAME
         , t2.WKLY_CA
         , t3.C_YEAR  AS per_year
         , t3.ITEM_NO
         , t2.IN_DT
     FROM  (SELECT t3.*
                 , ROW_NUMBER()
                      OVER( PARTITION BY CD_PLT , BASE_NAME
                                ORDER BY C_YEAR
                          ) AS rnum
             FROM  TABLE3 t3
             WHERE t3.C_YEAR  >= CHAR( YEAR(current_date) )
               AND t3.ITEM_NO >  0
           ) t3
     LEFT  OUTER JOIN
           TABLE2 t2
       ON  t2.CD_PLT    = t3.CD_PLT
       AND t2.BASE_NAME = t3.BASE_NAME
       AND
         (     CHAR( YEAR(t2.IN_DT) ) = t3.C_YEAR
           OR  t3.rnum                = 1
           AND CHAR( YEAR(t2.IN_DT) ) < t3.C_YEAR
         )
     ORDER BY
           CD_PLT
         , BASE_NAME DESC
         , per_year
    The above query returns all the past year rows,but we need only the latest one past rows

    Expected Result set
    Code:
    CD_PLT       BASE_NAME      PRE_NAME     SUF_NAME         WKLY_CA    PER_YEAR   ITEM_NO          IN_DT             
    ----------- ------------ -------------- ----------  ------------     -------   -----------     --------------    
    ABACC            6065        REWS           AE             21           2012 	209              2010-01-12        
    ABACC            6065        REWS           AE             -            2013    310                -                
    ABACC            6065        REWS           AE             -            2014    100                - 
      
    ABACC            6000        ERWV           DE             46           2012    500             2011-11-12 
    ABACC            6000        ERWV           DE             56           2012    500             2012-11-11 
    ABACC            6000        ERWV           DE             -            2013    600                -       
    ABACC            6000        ERWV           DE             -            2014    700                -
    
    ABADD            9011        WEST           DR             77           2013   1200             2011-03-01
    Please help

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I tought that the problem would be solved by applying some techniques used in
    Quote Originally Posted by tonkuma View Post
    ...

    Example 5:
    or
    Quote Originally Posted by tonkuma View Post
    ...

    Example 5a:
    Anyhow, I'll consider more...

    I had some issues on table design.

    (1) Why PRE_NAME and SUF_NAME were duplicated in TABLE2 and TABLE3.
    I thought that they are not normalized, then you ignored TABLE2.SUF_NAME and TABLE2.PRE_NAME.
    Is it right?

    (2) I thought all PRE_NAME and SUF_NAME were same for a combination of (CD_PLT , BASE_NAME) in your data.
    If so, why PRE_NAME and SUF_NAME were not put in another table(normalized)?

    (3) TABLE3.C_YEAR looks to contain 4 digits only, no alphabetic nor the length other than 4.
    So, why wasn't the datatype SMALLINT(or INTEGER)?

    (4) It might be better to use DATE datatype for IN_DT.
    Even if most functions taking DATE datatype parameter also accept character string having string representation of DATE datatype.


    For the moment put aside those issues,
    I added some more data to clarify and understand the requirements more strictly.
    Please publish expected results from them.

    Note(1): Some data might be useless, because of never exists or redundant.
    If so, please remove or ignore them.
    Note(2): Although, some data have different PRE_NAME and/or SUF_NAME for a combination of (CD_PLT , BASE_NAME),
    it wouldn't affect on queries.

    Code:
    WITH
     TABLE2
    ( CD_PLT , BASE_NAME , PRE_NAME , SUF_NAME , WKLY_CA , IN_DT ) AS (
    VALUES
      ( 'ABACC' , '6065' , 'REWS' , 'AE' , 21 , '2009-01-13' )
    , ( 'ABACC' , '6065' , 'REWS' , 'AE' , 21 , '2010-01-12' )
    , ( 'ABACC' , '6000' , 'ERWV' , 'DE' , 26 , '2010-10-11' )
    , ( 'ABACC' , '6000' , 'ERWV' , 'DE' , 46 , '2011-11-12' )
    , ( 'ABACC' , '6000' , 'ERWV' , 'DE' , 56 , '2012-11-11' )
    , ( 'ABADD' , '9011' , 'WEST' , 'DR' , 77 , '2011-03-01' )
    , ( 'ABADD' , '1000' , 'REWS' , 'XZ' , 21 , '2010-02-12' ) /* Added. */
    , ( 'ABADD' , '1000' , 'REWS' , 'XZ' , 21 , '2010-09-12' ) /* Added. */
    , ( 'ABADD' , '1032' , 'REWS' , 'NR' , 32 , '2012-07-12' ) /* Added. */
    , ( 'ABADD' , '1032' , 'ERWV' , 'NR' , 32 , '2012-09-12' ) /* Added. */
    , ( 'ABAEE' , '1000' , 'REWS' , 'XZ' , 21 , '2010-02-12' ) /* Added. */
    , ( 'ABAEE' , '1000' , 'REWS' , 'XZ' , 21 , '2010-09-12' ) /* Added. */
    , ( 'ABAEE' , '6000' , 'REWS' , 'AB' , 32 , '2012-07-12' ) /* Added. */
    , ( 'ABAEE' , '6000' , 'ERWV' , 'CD' , 33 , '2012-09-12' ) /* Added. */
    , ( 'ABAEE' , '6000' , 'WEST' , 'EF' , 34 , '2013-08-12' ) /* Added. */
    , ( 'ABAEE' , '6000' , 'REWS' , 'GH' , 35 , '2014-01-12' ) /* Added. */
    , ( 'ABAEE' , '1056' , 'REWS' , 'IJ' , 32 , '2013-07-12' ) /* Added. */
    , ( 'ABAEE' , '1056' , 'REWS' , 'KL' , 32 , '2013-09-12' ) /* Added. */
    , ( 'ABAFF' , '6000' , 'WEST' , 'MN' , 32 , '2012-07-12' ) /* Added. */
    , ( 'ABAFF' , '6000' , 'REWS' , 'OP' , 33 , '2012-09-12' ) /* Added. */
    , ( 'ABAFF' , '6000' , 'ERWV' , 'QR' , 34 , '2013-08-12' ) /* Added. */
    , ( 'ABAFF' , '6000' , 'REWS' , 'ST' , 35 , '2014-01-12' ) /* Added. */
    )
    , TABLE3
    ( CD_PLT , BASE_NAME , PRE_NAME , SUF_NAME , C_YEAR , ITEM_NO ) AS (
    VALUES
      ( 'ABACC' , '6065' , 'REWS' , 'AE' , '2010' ,  200 )
    , ( 'ABACC' , '6065' , 'REWS' , 'AE' , '2012' ,  209 )
    , ( 'ABACC' , '6065' , 'REWS' , 'AE' , '2013' ,  310 )
    , ( 'ABACC' , '6065' , 'REWS' , 'AE' , '2014' ,  100 )
    , ( 'ABACC' , '6000' , 'ERWV' , 'DE' , '2011' ,    0 )
    , ( 'ABACC' , '6000' , 'ERWV' , 'DE' , '2012' ,  500 )
    , ( 'ABACC' , '6000' , 'ERWV' , 'DE' , '2013' ,  600 )
    , ( 'ABACC' , '6000' , 'ERWV' , 'DE' , '2014' ,  700 )
    , ( 'ABADD' , '9011' , 'WEST' , 'DR' , '2011' ,    0 )
    , ( 'ABADD' , '9011' , 'WEST' , 'DR' , '2013' , 1200 )
    , ( 'ABADD' , '1000' , 'REWS' , 'AA' , '2012'      0 ) /* Added. */
    , ( 'ABADD' , '1032' , 'REWS' , 'BA' , '2012'      0 ) /* Added. */
    , ( 'ABADD' , '1033' , 'REWS' , 'CA' , '2013'      0 ) /* Added. */
    , ( 'ABAEE' , '1000' , 'REWS' , 'AA' , '2014'    800 ) /* Added. */
    , ( 'ABAEE' , '6000' , 'REWS' , 'AA' , '2012'    900 ) /* Added. */
    , ( 'ABAEE' , '6000' , 'ERWV' , 'AB' , '2013'      0 ) /* Added. */
    , ( 'ABAEE' , '6000' , 'REWS' , 'AC' , '2014'    900 ) /* Added. */
    , ( 'ABAEE' , '1056' , 'REWS' , 'AA' , '2014'   1000 ) /* Added. */
    , ( 'ABAFF' , '6000' , 'REWS' , 'AA' , '2012'      0 ) /* Added. */
    , ( 'ABAFF' , '6000' , 'WEST' , 'AA' , '2014'    990 ) /* Added. */
    )

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

    Thanks for the reply..I have added more sample data for both tables..
    Code:
    WITH
    TABLE1
    ( CD_PLT , BASE_NAME , PRE_NAME , SUF_NAME , C_YEAR , ITEM_NO ) AS (
    VALUES
    
      ( 'ABACC' , '6065' , 'REWS' , 'AE' , '2012' ,  209 )
    , ( 'ABACC' , '6065' , 'REWS' , 'AE' , '2013' ,  310 )
    , ( 'ABACC' , '6065' , 'REWS' , 'AE' , '2014' ,  100 )
    , ( 'ABACC' , '6000' , 'ERWV' , 'DE' , '2012' ,  500 )
    , ( 'ABACC' , '6000' , 'ERWV' , 'DE' , '2013' ,  600 )
    , ( 'ABACC' , '6000' , 'ERWV' , 'DE' , '2014' ,  700 )
    , ( 'ABADD' , '9011' , 'WEST' , 'DR' , '2013' , 1200 )
    , ( 'ABADD' , '1000' , 'REWS' , 'AA' , '2012'      0 ) 
    , ( 'ABADD' , '1032' , 'REWS' , 'BA' , '2012'      0 ) 
    , ( 'ABADD' , '1033' , 'REWS' , 'CA' , '2013'      0 ) 
    , ( 'ABAEE' , '1000' , 'REWS' , 'AA' , '2014'    800 ) 
    , ( 'ABAEE' , '6000' , 'REWS' , 'AA' , '2012'    900 ) 
    , ( 'ABAEE' , '6000' , 'ERWV' , 'AB' , '2013'      0 ) 
    , ( 'ABAEE' , '6000' , 'REWS' , 'AC' , '2014'    900 ) 
    , ( 'ABAEE' , '1056' , 'REWS' , 'AA' , '2014'   1000 ) 
    , ( 'ABAFF' , '6000' , 'REWS' , 'AA' , '2012'      0 ) 
    , ( 'ABAFF' , '6000' , 'WEST' , 'AA' , '2014'    990 )
    , ( 'BBBAA' , '1111' , 'RRRR' , 'AA' , '2013'   1990 )
    , ( 'BBBBB' , '2222' , 'RRRR' , 'AA' , '2012'    310 )
    , ( 'BBBZZ' , '1112' , 'PPRR' , 'PA' , '2012'    720 ) 
    , ( 'BBBZZ' , '1112' , 'PPRR' , 'PA' , '2013'    210 ) 
    , ( 'BBBZZ' , '1112' , 'PPRR' , 'PA' , '2014'    910 )
    )
    , TABLE2
    ( CD_PLT , BASE_NAME , PRE_NAME , SUF_NAME , WKLY_CA , IN_DT ) AS (
    VALUES
      ( 'ABACC' , '6065' , 'REWS' , 'AE' , 21 , '2009-01-13' )
    , ( 'ABACC' , '6065' , 'REWS' , 'AE' , 31 , '2010-01-12' )
    , ( 'ABACC' , '6000' , 'ERWV' , 'DE' , 26 , '2010-10-11' )
    , ( 'ABACC' , '6000' , 'ERWV' , 'DE' , 46 , '2011-11-12' )
    , ( 'ABACC' , '6000' , 'ERWV' , 'DE' , 56 , '2012-11-11' )
    , ( 'ABADD' , '9011' , 'WEST' , 'DR' , 77 , '2010-01-31' )
    , ( 'ABADD' , '9011' , 'WEST' , 'DR' , 87 , '2011-03-01' )
    , ( 'ABADD' , '9011' , 'WEST' , 'DR' , 99 , '2013-10-09' )
    , ( 'ABADD' , '1000' , 'REWS' , 'XZ' , 21 , '2010-02-12' )
    , ( 'ABADD' , '1000' , 'REWS' , 'XZ' , 21 , '2010-09-12' )
    , ( 'ABADD' , '1032' , 'REWS' , 'NR' , 32 , '2012-07-12' )
    , ( 'ABADD' , '1032' , 'ERWV' , 'NR' , 32 , '2012-09-12' )
    , ( 'ABAEE' , '1000' , 'REWS' , 'XZ' , 21 , '2010-02-12' )
    , ( 'ABAEE' , '1000' , 'REWS' , 'XZ' , 21 , '2010-09-12' )
    , ( 'ABAEE' , '6000' , 'REWS' , 'AB' , 32 , '2012-07-12' )
    , ( 'ABAEE' , '6000' , 'ERWV' , 'CD' , 33 , '2012-09-12' )
    , ( 'ABAEE' , '6000' , 'WEST' , 'EF' , 34 , '2013-08-12' )
    , ( 'ABAEE' , '6000' , 'REWS' , 'GH' , 35 , '2014-01-12' )
    , ( 'ABAEE' , '1056' , 'REWS' , 'IJ' , 32 , '2013-07-12' )
    , ( 'ABAEE' , '1056' , 'REWS' , 'KL' , 32 , '2013-09-12' )
    , ( 'ABAFF' , '6000' , 'WEST' , 'MN' , 32 , '2012-07-12' )
    , ( 'ABAFF' , '6000' , 'REWS' , 'OP' , 33 , '2012-09-12' )
    , ( 'ABAFF' , '6000' , 'ERWV' , 'QR' , 34 , '2013-08-12' )
    , ( 'ABAFF' , '6000' , 'REWS' , 'ST' , 35 , '2014-01-12' )
    , ( 'BBBAA' , '1111' , 'RRRR' , 'AA' , 11 , '2011-11-19' )
    , ( 'BBBAA' , '1111' , 'RRRR' , 'AA' , 12 , '2012-01-11' )
    , ( 'BBBAA' , '1111' , 'RRRR' , 'AA' , 22 , '2013-06-10' )
    , ( 'BBBBB' , '2222' , 'RRRR' , 'AA' , 13 , '2012-09-12' )
    , ( 'BBBZZ' , '1112' , 'PPRR' , 'PA' , 34 , '2012-01-12' )
    , ( 'BBBZZ' , '1112' , 'PPRR' , 'PA' , 44 , '2012-11-02' )
    , ( 'BBBZZ' , '1112' , 'PPRR' , 'PA' , 36 , '2014-01-12' )
    )
    
    SELECT t1.CD_PLT
         , t1.BASE_NAME
         , t1.PRE_NAME
         , t1.SUF_NAME
         , t2.WKLY_CA
         , t1.C_YEAR  AS per_year
         , t1.ITEM_NO
         , t2.IN_DT
     FROM  (SELECT t1.*
                 , ROW_NUMBER()
                      OVER( PARTITION BY CD_PLT , BASE_NAME,PRE_NAME,SUF_NAME
                                ORDER BY C_YEAR
                          ) AS rnum
             FROM  TABLE1 t1
             WHERE t1.C_YEAR  >= CHAR( YEAR(current_date) )
               AND t1.ITEM_NO >  0
           ) 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
       AND
         (     CHAR( YEAR(t2.IN_DT) ) = t1.C_YEAR
           OR  t1.rnum                = 1
           AND CHAR( YEAR(t2.IN_DT) ) < t1.C_YEAR
         )
     ORDER BY
           CD_PLT
         ,PRE_NAME
         , BASE_NAME DESC
         ,SUF_NAME
         , per_year
    Expected Result set
    Code:
    CD_PLT       BASE_NAME      PRE_NAME     SUF_NAME         WKLY_CA    PER_YEAR   ITEM_NO          IN_DT             
    ----------- ------------ -------------- ----------  ------------     -------   -----------     --------------    
    ABACC            6065        REWS           AE             31           2012 	209              2010-01-12        
    ABACC            6065        REWS           AE             -            2013    310                -                
    ABACC            6065        REWS           AE             -            2014    100                - 
      
    ABACC            6000        ERWV           DE             46           2012    500             2011-11-12 
    ABACC            6000        ERWV           DE             56           2012    500             2012-11-11 
    ABACC            6000        ERWV           DE             -            2013    600                -       
    ABACC            6000        ERWV           DE             -            2014    700                -
    
    ABADD            9011        WEST           DR             87           2013   1200             2011-03-01
    ABADD            9011        WEST           DR             99           2013   1200             2013-10-09
    
    ABAEE            1000        REWS           AA             -            2014    800                -
    ABAEE            6000        REWS           AA             -            2012    900                -
    ABAEE            6000        REWS           AC             -            2014    900                -
    ABAEE            1056        REWS           AA             -            2014   1000                - 
    ABAFF            6000        WEST           AA             -            2014    990                - 
    
    BBBAA            1111        RRRR           AA             12           2013   1990             2012-01-11
    BBBAA            1111        RRRR           AA             22           2013   1990             2013-06-10
    BBBBB            2222        RRRR           AA             13           2012    310             2012-09-12
    BBBZZ            1112        PPRR           PA             34           2012    720             2012-01-12
    BBBZZ            1112        PPRR           PA             44           2012    720             2012-11-02
    BBBZZ            1112        PPRR           PA             -            2013    210                 -
    BBBZZ            1112        PPRR           PA             36           2014    910             2014-01-12

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    My understandings of your requirements are...

    (1) Select of TABLE1 and candidate rows of TABLE2 which matched (CD_PLT, BASE_NAME, PRE_NAME, SUF_NAME)
    (2) Select a latest row of T2 which satisfied YEAR(IN_DT) = C_YEAR, for each row of T1.
    (3) Select(and add) latest row of T2 within rows which were equal to or older than first quarter of C_YEAR
    for oldest row of T1 in each set of (CD_PLT, BASE_NAME, PRE_NAME, SUF_NAME),
    if matched T2.IN_DT in (2) was not first quarter or no matched row in (2).

    Note: I'm not so sure on (3). Some conditions might be different.

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Based on the previous assumptions,
    I made an example by applying "Example 5a" for TABLE2 and adding some more codes.
    Quote Originally Posted by tonkuma View Post
    ...

    Example 5a:
    Example 1-1: Test data(added some more test data).
    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH
    TABLE1
    ( CD_PLT , BASE_NAME , PRE_NAME , SUF_NAME , C_YEAR , ITEM_NO ) AS (
    VALUES
      ( 'ABACC' , '6065' , 'REWS' , 'AE' , '2012' ,  209 )
    , ( 'ABACC' , '6065' , 'REWS' , 'AE' , '2013' ,  310 )
    , ( 'ABACC' , '6065' , 'REWS' , 'AE' , '2014' ,  100 )
    , ( 'ABACC' , '6000' , 'ERWV' , 'DE' , '2012' ,  500 )
    , ( 'ABACC' , '6000' , 'ERWV' , 'DE' , '2013' ,  600 )
    , ( 'ABACC' , '6000' , 'ERWV' , 'DE' , '2014' ,  700 )
    , ( 'ABADD' , '9011' , 'WEST' , 'DR' , '2013' , 1200 )
    , ( 'ABADD' , '1000' , 'REWS' , 'AA' , '2012' ,    0 )
    , ( 'ABADD' , '1032' , 'REWS' , 'BA' , '2012' ,    0 )
    , ( 'ABADD' , '1033' , 'REWS' , 'CA' , '2013' ,    0 )
    , ( 'ABAEE' , '1000' , 'REWS' , 'AA' , '2014' ,  800 )
    , ( 'ABAEE' , '6000' , 'REWS' , 'AA' , '2012' ,  900 )
    , ( 'ABAEE' , '6000' , 'ERWV' , 'AB' , '2013' ,    0 )
    , ( 'ABAEE' , '6000' , 'REWS' , 'AC' , '2014' ,  900 )
    , ( 'ABAEE' , '1056' , 'REWS' , 'AA' , '2014' , 1000 )
    , ( 'ABAFF' , '6000' , 'REWS' , 'AA' , '2012' ,    0 )
    , ( 'ABAFF' , '6000' , 'WEST' , 'AA' , '2014' ,  990 )
    , ( 'BBBAA' , '1111' , 'RRRR' , 'AA' , '2013' , 1990 )
    , ( 'BBBBB' , '2222' , 'RRRR' , 'AA' , '2012' ,  310 )
    , ( 'BBBZZ' , '1112' , 'PPRR' , 'PA' , '2012' ,  720 )
    , ( 'BBBZZ' , '1112' , 'PPRR' , 'PA' , '2013' ,  210 )
    , ( 'BBBZZ' , '1112' , 'PPRR' , 'PA' , '2014' ,  910 )
    
    , ( 'BBBZZ' , '1122' , 'PPRR' , 'PA' , '2012' ,  720 ) /* Added */
    , ( 'BBBZZ' , '1122' , 'PPRR' , 'PA' , '2013' ,  210 ) /* Added */
    , ( 'BBBZZ' , '1122' , 'PPRR' , 'PA' , '2014' ,  910 ) /* Added */
    
    , ( 'BBBZZ' , '1124' , 'PPRR' , 'PA' , '2012' ,  720 ) /* Added */
    , ( 'BBBZZ' , '1124' , 'PPRR' , 'PA' , '2013' ,  210 ) /* Added */
    , ( 'BBBZZ' , '1124' , 'PPRR' , 'PA' , '2014' ,  910 ) /* Added */
    )
    , TABLE2
    ( CD_PLT , BASE_NAME , PRE_NAME , SUF_NAME , WKLY_CA , IN_DT ) AS (
    VALUES
      ( 'ABACC' , '6065' , 'REWS' , 'AE' , 21 , '2009-01-13' )
    , ( 'ABACC' , '6065' , 'REWS' , 'AE' , 31 , '2010-01-12' )
    , ( 'ABACC' , '6000' , 'ERWV' , 'DE' , 26 , '2010-10-11' )
    , ( 'ABACC' , '6000' , 'ERWV' , 'DE' , 46 , '2011-11-12' )
    , ( 'ABACC' , '6000' , 'ERWV' , 'DE' , 56 , '2012-11-11' )
    , ( 'ABADD' , '9011' , 'WEST' , 'DR' , 77 , '2010-01-31' )
    , ( 'ABADD' , '9011' , 'WEST' , 'DR' , 87 , '2011-03-01' )
    , ( 'ABADD' , '9011' , 'WEST' , 'DR' , 99 , '2013-10-09' )
    , ( 'ABADD' , '1000' , 'REWS' , 'XZ' , 21 , '2010-02-12' )
    , ( 'ABADD' , '1000' , 'REWS' , 'XZ' , 21 , '2010-09-12' )
    , ( 'ABADD' , '1032' , 'REWS' , 'NR' , 32 , '2012-07-12' )
    , ( 'ABADD' , '1032' , 'ERWV' , 'NR' , 32 , '2012-09-12' )
    , ( 'ABAEE' , '1000' , 'REWS' , 'XZ' , 21 , '2010-02-12' )
    , ( 'ABAEE' , '1000' , 'REWS' , 'XZ' , 21 , '2010-09-12' )
    , ( 'ABAEE' , '6000' , 'REWS' , 'AB' , 32 , '2012-07-12' )
    , ( 'ABAEE' , '6000' , 'ERWV' , 'CD' , 33 , '2012-09-12' )
    , ( 'ABAEE' , '6000' , 'WEST' , 'EF' , 34 , '2013-08-12' )
    , ( 'ABAEE' , '6000' , 'REWS' , 'GH' , 35 , '2014-01-12' )
    , ( 'ABAEE' , '1056' , 'REWS' , 'IJ' , 32 , '2013-07-12' )
    , ( 'ABAEE' , '1056' , 'REWS' , 'KL' , 32 , '2013-09-12' )
    , ( 'ABAFF' , '6000' , 'WEST' , 'MN' , 32 , '2012-07-12' )
    , ( 'ABAFF' , '6000' , 'REWS' , 'OP' , 33 , '2012-09-12' )
    , ( 'ABAFF' , '6000' , 'ERWV' , 'QR' , 34 , '2013-08-12' )
    , ( 'ABAFF' , '6000' , 'REWS' , 'ST' , 35 , '2014-01-12' )
    , ( 'BBBAA' , '1111' , 'RRRR' , 'AA' , 11 , '2011-11-19' )
    , ( 'BBBAA' , '1111' , 'RRRR' , 'AA' , 12 , '2012-01-11' )
    , ( 'BBBAA' , '1111' , 'RRRR' , 'AA' , 22 , '2013-06-10' )
    , ( 'BBBBB' , '2222' , 'RRRR' , 'AA' , 13 , '2012-09-12' )
    
    , ( 'BBBZZ' , '1112' , 'PPRR' , 'PA' , 34 , '2012-01-05' ) /* 2012 1Q Added */
    , ( 'BBBZZ' , '1112' , 'PPRR' , 'PA' , 34 , '2012-01-12' ) /* 2012 1Q */
    , ( 'BBBZZ' , '1112' , 'PPRR' , 'PA' , 44 , '2012-11-02' ) /* 2012 4Q */
    , ( 'BBBZZ' , '1112' , 'PPRR' , 'PA' , 36 , '2014-01-12' ) /* 2014 1Q */
    
    , ( 'BBBZZ' , '1122' , 'PPRR' , 'PA' , 44 , '2011-11-02' ) /* 2011 4Q Added */
    , ( 'BBBZZ' , '1122' , 'PPRR' , 'PA' , 34 , '2012-01-12' ) /* 2012 1Q Added */
    , ( 'BBBZZ' , '1122' , 'PPRR' , 'PA' , 36 , '2014-01-12' ) /* 2014 1Q Added */
    
    , ( 'BBBZZ' , '1124' , 'PPRR' , 'PA' , 34 , '2010-03-05' ) /* 2010 1Q Added */
    , ( 'BBBZZ' , '1124' , 'PPRR' , 'PA' , 34 , '2012-01-05' ) /* 2012 1Q Added */
    , ( 'BBBZZ' , '1124' , 'PPRR' , 'PA' , 34 , '2012-02-27' ) /* 2012 1Q Added */
    , ( 'BBBZZ' , '1124' , 'PPRR' , 'PA' , 34 , '2012-05-12' ) /* 2012 2Q Added */
    , ( 'BBBZZ' , '1124' , 'PPRR' , 'PA' , 44 , '2012-11-02' ) /* 2012 4Q Added */
    , ( 'BBBZZ' , '1124' , 'PPRR' , 'PA' , 36 , '2014-01-12' ) /* 2014 1Q Added */
    )
    Example 1-2: Result.
    Code:
    CD_PLT BASE_NAME PRE_NAME SUF_NAME WKLY_CA     PER_YEAR ITEM_NO     IN_DT     
    ------ --------- -------- -------- ----------- -------- ----------- ----------
    ABACC  6065      REWS     AE                31 2012             209 2010-01-12
    ABACC  6065      REWS     AE                 - 2013             310 -         
    ABACC  6065      REWS     AE                 - 2014             100 -         
    ABACC  6000      ERWV     DE                46 2012             500 2011-11-12
    ABACC  6000      ERWV     DE                56 2012             500 2012-11-11
    ABACC  6000      ERWV     DE                 - 2013             600 -         
    ABACC  6000      ERWV     DE                 - 2014             700 -         
    ABADD  9011      WEST     DR                87 2013            1200 2011-03-01
    ABADD  9011      WEST     DR                99 2013            1200 2013-10-09
    ABAEE  1000      REWS     AA                 - 2014             800 -         
    ABAEE  1056      REWS     AA                 - 2014            1000 -         
    ABAEE  6000      REWS     AA                 - 2012             900 -         
    ABAEE  6000      REWS     AC                 - 2014             900 -         
    ABAFF  6000      WEST     AA                 - 2014             990 -         
    BBBAA  1111      RRRR     AA                12 2013            1990 2012-01-11
    BBBAA  1111      RRRR     AA                22 2013            1990 2013-06-10
    BBBBB  2222      RRRR     AA                13 2012             310 2012-09-12
    BBBZZ  1112      PPRR     PA                34 2012             720 2012-01-12
    BBBZZ  1112      PPRR     PA                44 2012             720 2012-11-02
    BBBZZ  1112      PPRR     PA                 - 2013             210 -         
    BBBZZ  1112      PPRR     PA                36 2014             910 2014-01-12
    BBBZZ  1122      PPRR     PA                34 2012             720 2012-01-12
    BBBZZ  1122      PPRR     PA                 - 2013             210 -         
    BBBZZ  1122      PPRR     PA                36 2014             910 2014-01-12
    BBBZZ  1124      PPRR     PA                34 2012             720 2012-02-27
    BBBZZ  1124      PPRR     PA                44 2012             720 2012-11-02
    BBBZZ  1124      PPRR     PA                 - 2013             210 -         
    BBBZZ  1124      PPRR     PA                36 2014             910 2014-01-12
    
      28 record(s) selected.

    Example 1: Query.
    Code:
    SELECT t1.CD_PLT
         , t1.BASE_NAME
         , t1.PRE_NAME
         , t1.SUF_NAME
         , t2.WKLY_CA
         , t1.C_YEAR  AS per_year
         , t1.ITEM_NO
         , t2.IN_DT
     FROM  (SELECT t1.*
                 , ROW_NUMBER()
                      OVER( PARTITION BY CD_PLT , BASE_NAME , PRE_NAME , SUF_NAME
                                ORDER BY C_YEAR
                          ) AS rnum
             FROM  TABLE1 t1
             WHERE t1.C_YEAR  >= CHAR( YEAR(current_date) )
               AND t1.ITEM_NO >  0
           ) t1
     LEFT  OUTER JOIN
           TABLE
          (SELECT t2.*
                , ROW_NUMBER()
                     OVER( ORDER BY NULLIF( c_year || '1' , in_year_quarter ) DESC
                                  , NULLIF( c_year        , in_year )         ASC
                                  , in_dt                                     DESC
                         ) AS r_num_4
            FROM  TABLE
                 (SELECT t2.* , t1.c_year
                       , ROW_NUMBER()
                            OVER( PARTITION BY in_year
                                      ORDER BY in_dt DESC ) AS rnum_y
                       , ROW_NUMBER()
                            OVER( PARTITION BY in_year_quarter
                                      ORDER BY in_dt DESC ) AS rnum_yq
                   FROM (SELECT t2.*
                              , CHAR(  YEAR(IN_DT)      ) AS in_year
                              , CHAR(  YEAR(IN_DT) * 10
                                     + QUARTER(IN_DT)   ) AS in_year_quarter
                          FROM  TABLE2 t2
                        ) t2
                   WHERE 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
                     AND
                    (    t2.in_year = t1.C_YEAR
                     OR  t1.rnum    = 1
                     AND t2.in_year < t1.C_YEAR
                    )
                 ) T2
          ) t2
      ON
      (    t2.in_year = T1.C_YEAR
       AND t2.rnum_y  = 1
       OR
           t1.rnum    = 1
       AND t2.in_year_quarter  = t1.c_year || '1'
       AND t2.rnum_yq = 1
       OR
           t2.in_year < T1.C_YEAR
       AND t2.r_num_4 = 1
      )
     ORDER BY
           CD_PLT
         , PRE_NAME  DESC
         , BASE_NAME
         , SUF_NAME
         , SUF_NAME
         , per_year
         , t2.in_dt
    ;

  6. #6
    Join Date
    Sep 2011
    Posts
    220
    Thanks for the reply!
    I have executed Example1 query..got error like
    Code:
    SQL0203N  A reference to column "C_YEAR" is ambiguous.  
    SQLSTATE=42702
    So I removed t1.c_year (red color) in the part of code
    FROM TABLE
    (SELECT t2.* , t1.c_year , ROW_NUMBER()
    OVER( PARTITION BY in_year
    ORDER BY in_dt DESC ) AS rnum_y
    , ROW_NUMBER()
    OVER( PARTITION BY in_year_quarter
    ORDER BY in_dt DESC ) AS rnum_yq
    and got like below
    Code:
    SQL0965W  There is no message text corresponding to SQL warning "203" in the 
    message file on this workstation.  The warning was returned from module 
    "DSNXORSO" with original tokens "T2.*".  SQLSTATE=01552
    
    
      0 record(s) selected with 1 warning messages printed.

  7. #7
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Try by adding qualifiers.

    Code:
    ...
     LEFT  OUTER JOIN
           TABLE
          (SELECT t2.*
                , ROW_NUMBER()
                     OVER( ORDER BY NULLIF( t2.c_year || '1' , in_year_quarter ) DESC
                                  , NULLIF( t2.c_year        , in_year )         ASC
                                  , in_dt                                        DESC
                         ) AS r_num_4
            FROM  TABLE
                 (SELECT t2.* , t1.c_year
    ...

  8. #8
    Join Date
    Sep 2011
    Posts
    220
    THANKS,,
    the same error is getting like AMBIGUOUS error and tried after removing of C_YEAR and got same error like


    SQL0965W There is no message text corresponding to SQL warning "203" in the
    message file on this workstation. The warning was returned from module
    "DSNXORSO" with original tokens "T2.*". SQLSTATE=01552


    0 record(s) selected with 1 warning messages printed.


    I have a question
    "LEFT OUTER JOIN
    TABLE
    (SELECT t2.*
    , ROW_NUMBER()
    OVER( ORDER BY NULLIF( t2.c_year || '1' , in_year_quarter ) DESC
    , NULLIF( t2.c_year , in_year ) ASC
    , in_dt DESC
    ) AS r_num_4
    FROM TABLE
    (SELECT t2.* , t1.c_year
    "
    whether i put t2.c_year OR t1.c_year as the red clor marked..
    i tried both but receiving the same error

  9. #9
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Please provide full query which you got error and error message itself again.

    Because, token in error was "T2.*" at this time, not t2.c_year nor t1.c_year.
    SQL0965W There is no message text corresponding to SQL warning "203" in the
    message file on this workstation. The warning was returned from module
    "DSNXORSO" with original tokens "T2.*". SQLSTATE=01552
    Last edited by tonkuma; 04-26-12 at 08:59. Reason: Add reason "Because, token in error ..."

  10. #10
    Join Date
    Sep 2011
    Posts
    220
    I excuted below query

    DB2 9.1 z/os

    Code:
    SELECT t1.CD_PLT
         , t1.BASE_NAME
         , t1.PRE_NAME
         , t1.SUF_NAME
         , t2.WKLY_CA
         , t1.C_YEAR  AS per_year
         , t1.ITEM_NO
         , t2.IN_DT
     FROM  (SELECT t1.*
                 , ROW_NUMBER()
                      OVER( PARTITION BY CD_PLT , BASE_NAME , PRE_NAME , SUF_NAME
                                ORDER BY C_YEAR
                          ) AS rnum
             FROM  TABLE1 t1
             WHERE t1.C_YEAR  >= CHAR( YEAR(current_date) )
               AND t1.ITEM_NO >  0
           ) t1
     LEFT  OUTER JOIN
           TABLE
          (SELECT t2.*
                , ROW_NUMBER()
                     OVER( ORDER BY NULLIF( c_year || '1' , in_year_quarter ) DESC
                                  , NULLIF( c_year        , in_year )         ASC
                                  , in_dt                                     DESC
                         ) AS r_num_4
            FROM  TABLE
                 (SELECT t2.* , t1.c_year
                       , ROW_NUMBER()
                            OVER( PARTITION BY in_year
                                      ORDER BY in_dt DESC ) AS rnum_y
                       , ROW_NUMBER()
                            OVER( PARTITION BY in_year_quarter
                                      ORDER BY in_dt DESC ) AS rnum_yq
                   FROM (SELECT t2.*
                              , CHAR(  YEAR(IN_DT)      ) AS in_year
                              , CHAR(  YEAR(IN_DT) * 10
                                     + QUARTER(IN_DT)   ) AS in_year_quarter
                          FROM  TABLE2 t2
                        ) t2
                   WHERE 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
                     AND
                    (    t2.in_year = t1.C_YEAR
                     OR  t1.rnum    = 1
                     AND t2.in_year < t1.C_YEAR
                    )
                 ) T2
          ) t2
      ON
      (    t2.in_year = T1.C_YEAR
       AND t2.rnum_y  = 1
       OR
           t1.rnum    = 1
       AND t2.in_year_quarter  = t1.c_year || '1'
       AND t2.rnum_yq = 1
       OR
           t2.in_year < T1.C_YEAR
       AND t2.r_num_4 = 1
      )
     ORDER BY
           CD_PLT
         , PRE_NAME  DESC
         , BASE_NAME
         , SUF_NAME
         , SUF_NAME
         , per_year
         , t2.in_dt
    ;
    received error like
    Code:
    SQL0203N  A reference to column "C_YEAR" is ambiguous.  
    SQLSTATE=42702
    removed t1.c_year
    Code:
    ;
    ;
           ) AS r_num_4
            FROM  TABLE
                 (SELECT t2.* 
                       , ROW_NUMBER()
                            OVER( PARTITION BY in_year
                                      ORDER BY in_dt DESC ) AS rnum_y
                       , ROW_NUMBER()
                            OVER( PARTITION BY in_year_quarter
                                      ORDER BY in_dt DESC ) AS rnum_yq
                   FROM (SELECT t2.*
    ;
    ;
    received error like
    Code:
    SQL0965W There is no message text corresponding to SQL warning "203" in the 
    message file on this workstation. The warning was returned from module 
    "DSNXORSO" with original tokens "T2.*". SQLSTATE=01552
    as per your latest direction changed the query like below and executed
    Code:
    SELECT t1.CD_PLT
         , t1.BASE_NAME
         , t1.PRE_NAME
         , t1.SUF_NAME
         , t2.WKLY_CA
         , t1.C_YEAR  AS per_year
         , t1.ITEM_NO
         , t2.IN_DT
     FROM  (SELECT t1.*
                 , ROW_NUMBER()
                      OVER( PARTITION BY CD_PLT , BASE_NAME , PRE_NAME , SUF_NAME
                                ORDER BY C_YEAR
                          ) AS rnum
             FROM  TABLE1 t1
             WHERE t1.C_YEAR  >= CHAR( YEAR(current_date) )
               AND t1.ITEM_NO >  0
           ) t1
     LEFT  OUTER JOIN
           TABLE
          (SELECT t2.*
                , ROW_NUMBER()
                     OVER( ORDER BY NULLIF( t2.c_year || '1' , in_year_quarter ) DESC
                                  , NULLIF( t2.c_year        , in_year )         ASC
                                  , in_dt                                     DESC
                         ) AS r_num_4
            FROM  TABLE
                 (SELECT t2.* , t1.c_year
                       , ROW_NUMBER()
                            OVER( PARTITION BY in_year
                                      ORDER BY in_dt DESC ) AS rnum_y
                       , ROW_NUMBER()
                            OVER( PARTITION BY in_year_quarter
                                      ORDER BY in_dt DESC ) AS rnum_yq
                   FROM (SELECT t2.*
                              , CHAR(  YEAR(IN_DT)      ) AS in_year
                              , CHAR(  YEAR(IN_DT) * 10
                                     + QUARTER(IN_DT)   ) AS in_year_quarter
                          FROM  TABLE2 t2
                        ) t2
                   WHERE 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
                     AND
                    (    t2.in_year = t1.C_YEAR
                     OR  t1.rnum    = 1
                     AND t2.in_year < t1.C_YEAR
                    )
                 ) T2
          ) t2
      ON
      (    t2.in_year = T1.C_YEAR
       AND t2.rnum_y  = 1
       OR
           t1.rnum    = 1
       AND t2.in_year_quarter  = t1.c_year || '1'
       AND t2.rnum_yq = 1
       OR
           t2.in_year < T1.C_YEAR
       AND t2.r_num_4 = 1
      )
     ORDER BY
           CD_PLT
         , PRE_NAME  DESC
         , BASE_NAME
         , SUF_NAME
         , SUF_NAME
         , per_year
         , t2.in_dt
    ;
    same Ambiguous c_year error is getting
    after removing the t1.c_year
    Code:
    .
    .
     FROM  TABLE
                 (SELECT t2.* 
                       , ROW_NUMBER()
                            OVER( PARTITION BY in_year
                                      ORDER BY in_dt DESC ) AS rnum_y
    .
    .
    getting same error
    Code:
    SQL0965W There is no message text corresponding to SQL warning "203" in the 
    message file on this workstation. The warning was returned from module 
    "DSNXORSO" with original tokens "T2.*". SQLSTATE=01552

  11. #11
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Are columns of TABLE2 same as Example 1-1: Test data(added some more test data) or your last sample data?

    Which were
    Code:
    , TABLE2
    ( CD_PLT , BASE_NAME , PRE_NAME , SUF_NAME , WKLY_CA , IN_DT ) AS (
    No c_year in TABLE2?
    Last edited by tonkuma; 04-26-12 at 09:30. Reason: Add "or your last sample data"

  12. #12
    Join Date
    Sep 2011
    Posts
    220
    Table2 should not have C_YEAR column

  13. #13
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    So, try by changing aliases to narrow the problematic codes.
    And see error message.
    Code:
    SELECT t1.CD_PLT
         , t1.BASE_NAME
         , t1.PRE_NAME
         , t1.SUF_NAME
         , t2.WKLY_CA
         , t1.C_YEAR  AS per_year
         , t1.ITEM_NO
         , t2.IN_DT
     FROM  (SELECT t1a.*
                 , ROW_NUMBER()
                      OVER( PARTITION BY CD_PLT , BASE_NAME , PRE_NAME , SUF_NAME
                                ORDER BY t1a.C_YEAR
                          ) AS rnum
             FROM  TABLE1 t1a
             WHERE t1a.C_YEAR  >= CHAR( YEAR(current_date) )
               AND t1a.ITEM_NO >  0
           ) t1
     LEFT  OUTER JOIN
           TABLE
          (SELECT t2b.*
                , ROW_NUMBER()
                     OVER( ORDER BY NULLIF( t2b.c_year || '1' , in_year_quarter ) DESC
                                  , NULLIF( t2b.c_year        , in_year )         ASC
                                  , in_dt                                     DESC
                         ) AS r_num_4
            FROM  TABLE
                 (SELECT t2.* , t1.c_year
                       , ROW_NUMBER()
                            OVER( PARTITION BY in_year
                                      ORDER BY in_dt DESC ) AS rnum_y
                       , ROW_NUMBER()
                            OVER( PARTITION BY in_year_quarter
                                      ORDER BY in_dt DESC ) AS rnum_yq
                   FROM (SELECT t2a.*
                              , CHAR(  YEAR(IN_DT)      ) AS in_year
                              , CHAR(  YEAR(IN_DT) * 10
                                     + QUARTER(IN_DT)   ) AS in_year_quarter
                          FROM  TABLE2 t2a
                        ) t2
                   WHERE 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
                     AND
                    (    t2.in_year = t1.C_YEAR
                     OR  t1.rnum    = 1
                     AND t2.in_year < t1.C_YEAR
                    )
                 ) T2b
          ) t2
      ON
      (    t2.in_year = T1.C_YEAR
       AND t2.rnum_y  = 1
       OR
           t1.rnum    = 1
       AND t2.in_year_quarter  = t1.c_year || '1'
       AND t2.rnum_yq = 1
       OR
           t2.in_year < T1.C_YEAR
       AND t2.r_num_4 = 1
      )
     ORDER BY
           CD_PLT
         , PRE_NAME  DESC
         , BASE_NAME
         , SUF_NAME
         , SUF_NAME
         , per_year
         , t2.in_dt
    ;

  14. #14
    Join Date
    Sep 2011
    Posts
    220
    Thanks for the nice reply...after removing of t1.c_year its working fine

    Code:
    ;
    ;
           ) AS r_num_4
            FROM  TABLE
                 (SELECT t2.* ,t1.c_year                   , ROW_NUMBER()
                            OVER( PARTITION BY in_year
                                      ORDER BY in_dt DESC ) AS rnum_y
                       , ROW_NUMBER()
                            OVER( PARTITION BY in_year_quarter
                                      ORDER BY in_dt DESC ) AS rnum_yq
                   FROM (SELECT t2.*
    ;
    ;
    but i found one issue...find the below set of test datas,.for the below case it returns all past rows,but we need only the latest past row
    Code:
    WITH
    TABLE1
    ( CD_PLT , BASE_NAME , PRE_NAME , SUF_NAME , C_YEAR , ITEM_NO ) AS (
    VALUES
      ( 'ABACC' , '6065' , 'REWS' , 'AE' , '2012' ,  209 )
    Code:
    , TABLE2
    ( CD_PLT , BASE_NAME , PRE_NAME , SUF_NAME , WKLY_CA , IN_DT ) AS (
    VALUES
      ( 'ABACC' , '6065' , 'REWS' , 'AE' , 21 , '2011-04-01' )
    , ( 'ABACC' , '6065' , 'REWS' , 'AE' , 31 , '2012-04-13' )
    We got the output like
    Code:
    CD_PLT BASE_NAME PRE_NAME SUF_NAME WKLY_CA     PER_YEAR ITEM_NO     IN_DT     
    ------ --------- -------- -------- ----------- -------- ----------- ----------
    ABACC  6065      REWS     AE                21 2012             209 2011-04-01
    ABACC  6065      REWS     AE                31 2012             209 2012-04-13
    but EXPECTED OUTPUT is
    Code:
    CD_PLT BASE_NAME PRE_NAME SUF_NAME WKLY_CA     PER_YEAR ITEM_NO     IN_DT     
    ------ --------- -------- -------- ----------- -------- ----------- ----------
    ABACC  6065      REWS     AE                31 2012             209 2012-04-13

  15. #15
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Why didn't you try my suggestion?
    It's easy to execute my last example and to copy/paste the error message(s) you recieved.
    Quote Originally Posted by tonkuma View Post
    So, try by changing aliases to narrow the problematic codes.
    And see error message.
    Don't try to remove t1.c_year itself.
    You should try to remove ambiguity of t1.c_year, if my considerations are not miss the point.
    Last edited by tonkuma; 04-28-12 at 20:02. Reason: Add quote.

Posting Permissions

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