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

    Unanswered: rows fetched based on dates

    Hi all,

    ID_LOC CHAR(3)
    ID_NO CHAR(5)
    ID_NAME CHAR(5)
    ID_QNTY INTEGER
    ID_DATE DATE
    ID_SURNAME CHAR(5)

    DB2 9.1/ZOS



    Ex1:

    If there is any data change compare to first row(other than ID_DATE),
    query has to fetch that row
    ID_LOC and ID_NO should not be the compare key

    Row1 is compared with next row ROW2,nochange
    Row2 is compared with next row Row3,no change

    SO Row1 is the valid record
    Code:
    ID_LOC   ID_NO    ID_NAME ID_QNTY  ID_DATE    ID_SURNAME
    
    AAA      WE-RE     A1111    10     2009-09-09  A1111---row1
    AAA      WE-RE     A1111    10     2013-03-11  A1111---row2
    AAA      WE-RE     A1111    10     2015-12-23  A1111---row3
    Ex2:
    Row1 is compared with next row ROW2,nochange
    Row2 is compared with next row Row3,change in ID_NAME and
    ID_SURNAME
    Row3 is compared with next row ROW4,nochange

    sO Row1 and Row3 are valid

    Code:
    AAA      T11-T     B1111    10     2013-12-31  B1111
    AAA      T11-T     B1111    10     2014-03-21  B1111
    AAA      T11-T     C2222    10     2014-12-31  C2222
    AAA      T11-T     C2222    10     2015-03-11  C2222
    Ex3:
    Row1 is compared with next row ROW2,change in ID_NAME,SURNAME

    Row2 is compared with next row Row3,change in ID_NAME,SURNAME and ID_qnty
    Row3 is compared to next row Row4,no change

    So Row1,Row2,Row3 are valid
    Code:
    AAA      XX-XT     D1222    05     2009-03-31  D1222
    AAA      XX-XT     E2322    05     2009-03-31  E2322
    AAA      XX-XT     R1212    10     2013-01-31  R1212
    AAA      XX-XT     R1212    10     2014-07-12  R1212
    Ex4:
    Row1 is compared with next row ROW2,change in ID_SURNAME

    Row2 is compared with next row Row3,no change
    Row3 is compared to next row Row4,change in ID_NAME,SURNAME,ID_QNTY
    Row4 is compared to next row Row5,change in ID_NAME,SURNAME,ID_QNTY


    So Row1,Row2,Row4,Row5 are valid
    Code:
    AAA      S-ERS     F1234    10     2014-02-21  F1234
    AAA      S-ERS     F1234    10     2014-05-23  G1111
    AAA      S-ERS     F1234    10     2014-12-24  G1111
    AAA      S-RES     X3333    03     2015-03-21  X3333
    AAA      S-RES     Z3243    07     2015-03-21  Z3243
    Code:
    EXPECTED RESULT SET
    
    
    AAA      WE-RE     A1111    10     2009-09-09  A1111
        
    AAA      T11-T     B1111    10     2013-12-31  B1111
    AAA      T11-T     C2222    10     2014-12-31  C2222
    
    AAA      XX-XT     D1222    05     2009-03-31  D1222
    AAA      XX-XT     E2322    05     2009-03-31  E2322
    AAA      XX-XT     R1212    10     2013-01-31  R1212
    
    AAA      S-ERS     F1234    10     2014-02-21  F1234
    AAA      S-ERS     F1234    10     2014-05-23  G1111
    AAA      S-RES     X3333    03     2015-03-21  X3333
    AAA      S-RES     Z3243    07     2015-03-21  Z3243
    Thanks,

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    It might be easier to understand your requirements by the descriptions with comparing to previous row, lke...

    If there is any data change compare to /*first*/previous row(other than ID_DATE),
    query has to fetch that row
    ID_LOC and ID_NO should not be the compare key


    Ex1:
    Row1 has no previous row, assume change all columns
    Row2 is compared with previous row Row1, no change
    Row3 is compared with previous row Row2, no change

    So, Row1 is the valid record.

    Code:
    ID_LOC   ID_NO    ID_NAME ID_QNTY  ID_DATE    ID_SURNAME
    AAA      WE-RE     A1111    10     2009-09-09  A1111             -- valid
    AAA      WE-RE     A1111    10     2013-03-11  A1111 -- no change
    AAA      WE-RE     A1111    10     2015-12-23  A1111 -- no change

    Ex2:
    Row1 has no previous row, assume change all columns
    Row2 is compared with previous row Row1, no change
    Row3 is compared with previous row Row2, change in ID_NAME and ID_SURNAME
    Row4 is compared with previous row ROW3, no change

    So, Row1 and Row3 are valid.

    Code:
    AAA      T11-T     B1111    10     2013-12-31  B1111             -- valid
    AAA      T11-T     B1111    10     2014-03-21  B1111 -- no change
    AAA      T11-T     C2222    10     2014-12-31  C2222 -- Changed  -- valid
    AAA      T11-T     C2222    10     2015-03-11  C2222 -- no change

    Ex3:
    Row1 has no previous row, assume change all columns
    Row2 is compared with previous row Row1, change in ID_NAME,SURNAME
    Row3 is compared with previous row Row2, change in ID_NAME,SURNAME and ID_qnty
    Row4 is compared with previous row Row3, no change

    So, Row1, Row2, Row3 are valid.

    Code:
    AAA      XX-XT     D1222    05     2009-03-31  D1222            -- valid
    AAA      XX-XT     E2322    05     2009-03-31  E2322 -- Changed -- valid
    AAA      XX-XT     R1212    10     2013-01-31  R1212 -- Changed -- valid
    AAA      XX-XT     R1212    10     2014-07-12  R1212 -- no change

    Ex4:
    Row1 has no previous row, assume change all columns
    Row2 is compared with previous row Row1, change in ID_SURNAME
    Row3 is compared with previous row Row2, no change
    Row4 is compared with previous row Row3, change in ID_NAME,SURNAME,ID_QNTY
    Row5 is compared with previous row Row4, change in ID_NAME,SURNAME,ID_QNTY

    So, Row1, Row2, Row4, Row5 are valid.

    Code:
    AAA      S-ERS     F1234    10     2014-02-21  F1234            -- valid
    AAA      S-ERS     F1234    10     2014-05-23  G1111 -- Changed -- valid
    AAA      S-ERS     F1234    10     2014-12-24  G1111 -- no change
    AAA      S-RES     X3333    03     2015-03-21  X3333 -- Changed -- valid
    AAA      S-RES     Z3243    07     2015-03-21  Z3243 -- Changed -- valid


    You might want to use the following funcions/ways, to get previous value.

    DB2 for LUW:
    LAG(...) OVER(PARTITION BY ID_LOC , ID_NO ORDER BY ID_DATE ASC)


    DB2 10 for z/OS: assumed no support of "LAG(...) OVER(...)"
    MAX(...) OVER(PARTITION BY ID_LOC , ID_NO ORDER BY ID_DATE ASC
    ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING)


    DB2 9 for z/OS: assumed no support of "MAX(...) OVER(...)"
    In a common-table-expression, add the following expression(column).
    ROW_NUMBER() OVER(PARTITION BY ID_LOC , ID_NO ORDER BY ID_DATE ASC) AS row_num

    Then self-join the common-table-expression(with alias names, say previous and current),
    and add the following condition to ON clause.
    AND current.row_num = previous.row_num + 1

    Please see details on
    IBM Knowledge Center: DB2 for z/OS

    IBM Knowledge Center: DB2 for z/OS 9.0.0 >>> OLAP specification

    so on...
    Last edited by tonkuma; 06-20-14 at 19:17.

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

    Tried with below sample data

    Code:
    ('CCC','VFD-W','E2342',10,'2013-03-31','E2342')
    ('CCC','VFD-W','JC311',10,'2013-06-25','JC312')
    ('CCC','VFD-W','JC311',10,'2013-09-21','JC312')
    ('CCC','VFD-W','JC311',10,'2013-12-25','JC312')
    ('CCC','VFD-W','JC311',10,'2014-03-29','JC312')
    ('CCC','VFD-W','JC311',10,'2014-06-22','JC312')
    ('CCC','AXX-W','TC144',09,'2013-01-31','TC144')
    ('CCC','AXX-W','DC334',01,'2013-01-31','DC334')
    ('CCC','AXX-W','TC144',09,'2014-03-31','TC144')
    ('CCC','AXX-W','DC334',01,'2014-03-31','DC334')
    Below qurey i ran
    Code:
    SELECT
    ID_LOC,
    ID_NO,
    ID_NAME,
    ID_QNTY,
    ID_DATE,
    ID_SURNAME
    ,MAX(ID_NAME)
     OVER(PARTITION BY
    ID_LOC,
    ID_NO
     
    ORDER BY ID_DATE ASC
    ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) AS OLD_RN
    
    FROM MASTER_TABLE
    Got RESULT SET LIKE
    Code:
    ID_LOC ID_NO ID_NAME ID_QNTY  ID_DATE ID_SURNAME  OLD_RN
    
    CCC    VFD-W   E2342  10  2013-03-31 E2342        E2342   
    CCC    VFD-W   JC311  10  2013-06-25 JC312        JC311
    CCC    VFD-W   JC311  10  2013-09-21 JC312        JC311 
    CCC    VFD-W   JC311  10  2013-12-25 JC312        JC311
    CCC    VFD-W   JC311  10  2014-03-29 JC312        JC311
    CCC    VFD-W   JC311  10  2014-06-22 JC312        JC311
    CCC    AXX-W   TC144  09  2013-01-31 TC144        TC144
    CCC    AXX-W   DC334  01  2013-01-31 DC334        TC144
    CCC    AXX-W   TC144  09  2014-03-31 TC144        TC144
    CCC    AXX-W   DC334  01  2014-03-31 DC334        TC144
    BUT EXPECTED RESULTSET IS
    Code:
    ID_LOC ID_NO ID_NAME ID_QNTY  ID_DATE ID_SURNAME
    
    CCC VFD-W   E2342  10  2013-03-31 E2342
    CCC VFD-W   JC311  10  2013-06-25 JC312
    CCC AXX-W   TC144  09  2013-01-31 TC144         
    CCC AXX-W   DC334  01  2013-01-31 DC334
    Thanks,

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Some questions.

    (1) Did you really got the results shown after "Got RESULT SET LIKE"?
    I got the following results on DB2 9.7.5 for Windows, like...
    Code:
    ID_LOC ID_NO ID_NAME ID_QNTY     ID_DATE    ID_SURNAME OLD_RN
    ------ ----- ------- ----------- ---------- ---------- ------
    CCC    VFD-W E2342            10 2013-03-31 E2342      -     
    CCC    VFD-W JC311            10 2013-06-25 JC312      E2342 
    CCC    VFD-W JC311            10 2013-09-21 JC312      JC311 
    CCC    VFD-W JC311            10 2013-12-25 JC312      JC311 
    CCC    VFD-W JC311            10 2014-03-29 JC312      JC311 
    CCC    VFD-W JC311            10 2014-06-22 JC312      JC311 
    CCC    AXX-W TC144             9 2013-01-31 TC144      -     
    CCC    AXX-W DC334             1 2013-01-31 DC334      TC144 
    CCC    AXX-W TC144             9 2014-03-31 TC144      DC334 
    CCC    AXX-W DC334             1 2014-03-31 DC334      TC144 
    
      10 record(s) selected.
    The Query used was: Added code were marked by Bold/Ialic/Red.
    Code:
    WITH MASTER_TABLE
    ( ID_LOC , ID_NO , ID_NAME , ID_QNTY , ID_DATE , ID_SURNAME ) AS (
    VALUES
      ('CCC','VFD-W','E2342',10,'2013-03-31','E2342')
    , ('CCC','VFD-W','JC311',10,'2013-06-25','JC312')
    , ('CCC','VFD-W','JC311',10,'2013-09-21','JC312')
    , ('CCC','VFD-W','JC311',10,'2013-12-25','JC312')
    , ('CCC','VFD-W','JC311',10,'2014-03-29','JC312')
    , ('CCC','VFD-W','JC311',10,'2014-06-22','JC312')
    , ('CCC','AXX-W','TC144',09,'2013-01-31','TC144')
    , ('CCC','AXX-W','DC334',01,'2013-01-31','DC334')
    , ('CCC','AXX-W','TC144',09,'2014-03-31','TC144')
    , ('CCC','AXX-W','DC334',01,'2014-03-31','DC334')
    )
    SELECT
    ID_LOC,
    ID_NO,
    ID_NAME,
    ID_QNTY,
    ID_DATE,
    ID_SURNAME
    ,MAX(ID_NAME)
     OVER(PARTITION BY
    ID_LOC,
    ID_NO
     
    ORDER BY ID_DATE ASC
    ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) AS OLD_RN
    
    FROM MASTER_TABLE
    ORDER BY
          ID_LOC , ID_NO DESC , ID_DATE;

    (2) Why did you excluded last two rows from EXPECTED RESULTSET?
    Code:
    ID_LOC ID_NO ID_NAME ID_QNTY  ID_DATE ID_SURNAME
    
    CCC VFD-W   E2342  10  2013-03-31 E2342 -- row 1
    CCC VFD-W   JC311  10  2013-06-25 JC312 -- row 2
    CCC AXX-W   TC144  09  2013-01-31 TC144 -- row 3
    CCC AXX-W   DC334  01  2013-01-31 DC334 -- row 4
    CCC AXX-W   TC144  09  2014-03-31 TC144 -- row 5 different ID_NAME, ID_QNTY, ID_SURNAME from row 4
    CCC AXX-W   DC334  01  2014-03-31 DC334 -- row 6 different ID_NAME, ID_QNTY, ID_SURNAME from row 5

    (3) Why no WHERE clause?

  5. #5
    Join Date
    Sep 2011
    Posts
    220
    (1) Did you really got the results shown after "Got RESULT SET LIKE"?
    By mistaken i commented the "rows between 1 preceeding......" and ran

    Now i ran the correct qury and got output like
    Code:
    ID_LOC ID_NO ID_NAME ID_QNTY  ID_DATE ID_SURNAME  PAST_RN
    
    CCC    VFD-W   E2342  10  2013-03-31 E2342            
    CCC    VFD-W   JC311  10  2013-06-25 JC312        E2342
    CCC    VFD-W   JC311  10  2013-09-21 JC312        JC311 
    CCC    VFD-W   JC311  10  2013-12-25 JC312        JC311
    CCC    VFD-W   JC311  10  2014-03-29 JC312        JC311
    CCC    VFD-W   JC311  10  2014-06-22 JC312        JC311
    CCC    AXX-W   DC334  01  2013-01-31 DC334        
    CCC    AXX-W   TC144  09  2013-01-31 TC144        DC334
    CCC    AXX-W   DC334  01  2014-03-31 DC334        TC144
    CCC    AXX-W   TC144  09  2014-03-31 TC144        DC334
    (2) Why did you excluded last two rows from EXPECTED RESULTSET?
    Code:
    ID_LOC ID_NO ID_NAME ID_QNTY ID_DATE ID_SURNAME

    CCC VFD-W E2342 10 2013-03-31 E2342 -- row 1
    CCC VFD-W JC311 10 2013-06-25 JC312 -- row 2
    CCC AXX-W TC144 09 2013-01-31 TC144 -- row 3
    CCC AXX-W DC334 01 2013-01-31 DC334 -- row 4
    CCC AXX-W TC144 09 2014-03-31 TC144 -- row 5 different ID_NAME, ID_QNTY, ID_SURNAME from row 4
    CCC AXX-W DC334 01 2014-03-31 DC334 -- row 6 different ID_NAME, ID_QNTY, ID_SURNAM
    for that record,Row 5 differernt from Row4 but matched with Row3
    Row 6 differernt from Row5 but matched with Row4

    Code:
    CCC AXX-W   TC144  09  2013-01-31 TC144 -- row 1
    CCC AXX-W   DC334  01  2013-01-31 DC334 -- row 2
    CCC AXX-W   TC144  09  2014-03-31 TC144 -- row 3 
    CCC AXX-W   DC334  01  2014-03-31 DC334 -- row 4
    Please see the above records
    The TC144 is effect from 2013-01-31 with 9% supply
    The TC144 is effect from 2013-01-31 with 1% supply
    so the combination of ID_LOC and ID_NO should have 10%

    so,
    The same(Row3,Row4) ID_NAME and ID_qnty are repeated with future date with out any change,So that we have to eliminate Row3 and Row4
    (sorry that is my mistake,for not giving full requirement)
    (3) Why no WHERE clause?
    We have to insert the records into another table whatever the query returns
    The master table has millions of records with different ID_LOC and ID_NO

    Please excuse me again

    Thanks,

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Then, your requirements might be described something like...
    Exclude rows having same ID_LOC, ID_NO, ID_NAME, ID_QNTY, ID_SURNAME and lower ID_DATE.
    If so, an idea might be to use NOT EXISTS predicate.

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

    Please see the descriptions below

    1.In the combination of ID_LOC and ID_NO are getting full(10%)supply(ID_QNTY) from ID_NAME with the effective date(quarter wise)

    2.ID_LOC and ID_NAME can have getting supply from different ID_NAME also in the sum of whole 10%

    3.The Master table gets records updated by every quarter if there is no change in ID_NAME,ID_QNTY,ID_SURNAME

    4.So eliminating those repeated records and fetch the unique row and has to insert into another table

    Code:
    ID_LOC ID_NO ID_NAME ID_QNTY  ID_DATE ID_SURNAME  OLD_RN
    
    CCC    VFD-W   E2342  10  2013-03-31 E2342        E2342  -Stating date 
    CCC    VFD-W   JC311  10  2013-06-25 JC312        JC311 -New ID_NAME 
    CCC    VFD-W   JC311  10  2013-09-21 JC312        JC311 -same ID_NAME
    CCC    VFD-W   JC311  10  2013-12-25 JC312        JC311 -same ID_NAME
    CCC    VFD-W   JC311  10  2014-03-29 JC312        JC311-same ID_NAME
    CCC    VFD-W   JC311  10  2014-06-22 JC312        JC311-same ID_NAME
    The above example the master table gets same record with quarter date change only..means that JC311 is going to supply 10 % up to 2014-06-22
    So there is no meaning to keeping the remainig record in the table

    Please let me know,if any
    Thanks,

  8. #8
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Do you want to keep those two rows?
    Code:
    ID_LOC ID_NO ID_NAME ID_QNTY  ID_DATE ID_SURNAME  OLD_RN
    
    CCC    VFD-W   E2342  10  2013-03-31 E2342        E2342  -Stating date 
    CCC    VFD-W   JC311  10  2013-06-25 JC312        JC311 -New ID_NAME
    If so, consider this
    Quote Originally Posted by tonkuma View Post
    ...
    If so, an idea might be to use NOT EXISTS predicate.
    If not, please show the rows which you want to keep.

  9. #9
    Join Date
    Sep 2011
    Posts
    220
    correct,I want those rows,

    Can you please say that your proposed logic will work for the below scenario


    Code:
    CCC    AXX-W   DC334  01  2013-01-31 DC334        
    CCC    AXX-W   TC144  09  2013-01-31 TC144        
    CCC    AXX-W   DC334  01  2014-03-31 DC334        
    CCC    AXX-W   TC144  09  2014-03-31 TC144
    
    CCC    BYX-W   XXX11  04  2009-03-15 XXX11        
    CCC    BYX-W   YYY11  06  2009-03-15 YYY11         
    CCC    BYX-W   XXX11  10  2014-03-31 XXX11         
    CCC    BYX-W   XXX11  10  2014-06-24 XXX11
    EXPECTED RESULT SET
    Code:
    CCC    AXX-W   DC334  01  2013-01-31 DC334        
    CCC    AXX-W   TC144  09  2013-01-31 TC144 
    
    CCC    BYX-W   XXX11  04  2009-03-15 XXX11        
    CCC    BYX-W   YYY11  06  2009-03-15 YYY11 
    CCC    BYX-W   XXX11  10  2014-03-31 XXX11
    Can you tell me where i have to use NOT EXIST predicate in the query
    Thanks,

  10. #10
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I think that NOT EXISTS logic will work for the scenario.


    Though, I thought the usage of NOT EXISTS in this issue might be a basic one(no special tric, etc.),
    I'll show you some more hint(almost final answer).

    The query might be like...
    Code:
    SELECT
         ...
         ...
     FROM  MASTER_TABLE AS t
     WHERE NOT EXISTS
           (SELECT 0
             FROM  MASTER_TABLE AS s
             WHERE 
             /*
               put the conditions here:
                   s have same ID_LOC, ID_NO, ID_NAME, ID_QNTY, ID_SURNAME
                          and lower ID_DATE
                   compared to t
             */
           )

  11. #11
    Join Date
    Sep 2011
    Posts
    220
    Hi,
    /*
    put the conditions here:
    s have same ID_LOC, ID_NO, ID_NAME, ID_QNTY, ID_SURNAME
    and lower ID_DATE
    compared to t
    */
    Sorry,I could not understand the above condition,can you say little bit more
    and one more question
    Why lower ID_DATE ?

    Thanks,

  12. #12
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    (1) Please consider this way of description of the requirements.
    Quote Originally Posted by tonkuma View Post
    Then, your requirements might be described something like...
    Exclude rows having same ID_LOC, ID_NO, ID_NAME, ID_QNTY, ID_SURNAME and lower ID_DATE.
    If so, an idea might be to use NOT EXISTS predicate.
    If you thought that the description was not fully describe your requirements,
    please show counter-example which satisfy your description of requiremens but not agree with my description.


    (2) After you understood the description,
    you might know that
    the rows NOT "having(exists) same ID_LOC, ID_NO, ID_NAME, ID_QNTY, ID_SURNAME and lower ID_DATE" should be included.
    Last edited by tonkuma; 06-21-14 at 10:10. Reason: Add a senence "If you thought that the description was not fully describe your requirements, ... "

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

    Exclude rows having same ID_LOC, ID_NO, ID_NAME, ID_QNTY, ID_SURNAME and lower ID_DATE.
    Please tell me,my understanding is correct or not

    for the below,need to eliminate first two rows right?
    Code:
    CCC    BYX-W   XXX11  04  2009-03-15 XXX11 --exclude       
    CCC    BYX-W   YYY11  06  2009-03-15 YYY11 --exclude        
    CCC    BYX-W   XXX11  10  2014-03-31 XXX11         
    CCC    BYX-W   XXX11  10  2014-06-24 XXX11
    after that, NOT EXISTS need to include

    I am assuming that query seems to be like
    Code:
    SELECT
    ID_LOC,
    ID_NO,
    ID_NAME,
    ID_QNTY,
    ID_DATE,
    ID_SURNAME
     
    FROM MASTER_TABLE as t
    WHERE NOT EXISTS
           (SELECT 0
             FROM  MASTER_TABLE AS s
             WHERE
           T.ID_LOC=S.ID_LOC
    AND T.ID_NO=S.ID_NO
    AND T.ID_NAME=S.ID_NAME
    AND T.ID_QNTY=S.ID_QNTY
    AND T.ID_DATE=S.ID_DATE
    AND T.ID_SURNAME=S.ID_SURNAME
    )
    Still ,I get confusion to exclude lower ID_DATE

    EXPECTED RESULT SET
    Code:
    CCC    BYX-W   XXX11  04  2009-03-15 XXX11        
    CCC    BYX-W   YYY11  06  2009-03-15 YYY11 
    CCC    BYX-W   XXX11  10  2014-03-31 XXX11
    Can you please help me

  14. #14
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Quote Originally Posted by Billa007 View Post
    Hi,

    Exclude rows having same ID_LOC, ID_NO, ID_NAME, ID_QNTY, ID_SURNAME and lower ID_DATE.
    Please tell me,my understanding is correct or not

    for the below,need to eliminate first two rows right?
    Code:
    CCC    BYX-W   XXX11  04  2009-03-15 XXX11 --exclude       
    CCC    BYX-W   YYY11  06  2009-03-15 YYY11 --exclude        
    CCC    BYX-W   XXX11  10  2014-03-31 XXX11         
    CCC    BYX-W   XXX11  10  2014-06-24 XXX11
    No!
    Code:
    CCC    BYX-W   XXX11  04  2009-03-15 XXX11 -- Include. -- NOT having same ID_LOC, ID_NO, ID_NAME, ID_QNTY, ID_SURNAME and lower ID_DATE
    CCC    BYX-W   YYY11  06  2009-03-15 YYY11 -- Include. -- NOT having same ID_LOC, ID_NO, ID_NAME, ID_QNTY, ID_SURNAME and lower ID_DATE
    CCC    BYX-W   XXX11  10  2014-03-31 XXX11 -- Include. -- NOT having same ID_LOC, ID_NO, ID_NAME, ID_QNTY, ID_SURNAME and lower ID_DATE
    CCC    BYX-W   XXX11  10  2014-06-24 XXX11 -- Exclude. -- there is a lower ID_DATE '2014-03-31' having same ID_LOC, ID_NO, ID_NAME, ID_QNTY, ID_SURNAME and

  15. #15
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Are these comments useful for your understanding?

    Code:
    CCC    BYX-W   XXX11  04  2009-03-15 XXX11 -- row 1 -- Include.
    /* no row having lower ID_DATE than row 1 */
    CCC    BYX-W   YYY11  06  2009-03-15 YYY11 -- row 2 -- Include.
    /* no row having lower ID_DATE than row 2 */
    CCC    BYX-W   XXX11  10  2014-03-31 XXX11 -- row 3 -- Include.
    /* row 1 and 2 having lower ID_DATE than row 3. */
    /* But, row 1 was different in ID_QNTY, row 2 was different in ID_NAME, ID_QNTY. */ 
    CCC    BYX-W   XXX11  10  2014-06-24 XXX11 -- row 4 -- Exclude.
    /* row 3 having lower ID_DATE than row 4, */
    /* and all other columns are same as row 4. */

Posting Permissions

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