Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2011
    Posts
    220

    Wink Unanswered: help needed for Query

    Hi all,

    Please have a look on below query


    DB2/ZOS V 9.7


    RL_NO SMALLINT
    ID_BG CHAR(5)
    ID_MM CHAR(6)
    ID_LS CHAR(4)
    ID_VAL INTEGER
    Code:
    SELECT * FROM
    (SELECT T1.RL_NO,
     T1.ID_BG,
    T1.ID_MM,
    T1.ID_LS,
    T1.ID_VAL
     FROM BASE_TABLE T1
    
    WHERE T1.RL_NO =111
    ) T1
    FULL OUTER JOIN
    (SELECT T2.RL_NO,
     T2.ID_BG,
    T2.ID_MM,
    T2.ID_LS,
    T2.ID_VAL
     FROM BASE_TABLE T2
    
    WHERE T2.RL_NO =222
    ) T2
    
    ON
     T1.ID_BG  = T2.ID_BG
    AND T1.ID_MM = T2.ID_MM
    AND T1.ID_LS = T2.ID_LS
    
    Base_Table
    
    RL_NO  ID_BG    ID_MM    ID_LS  ID_VAL
    
    111     A1B      R4Y2     AA     200
    
    111     B1B      S4Y2     AC     2000
    
    111     Z2B      RRY3     BC     1200
    
    111     AAA      DDT2     AA     200
    
    111     Z9A      YWR3     BC     1200
    
    111     ADC      QWR3     B1C    1200
    
    111     DDC      ZWR3     BCC    100
    
    
    
    
    
    
    
    Base_table
     
    RL_NO  ID_BG    ID_MM    ID_LS  ID_VAL
    
    222     A1B      R4Y2     AA     500
    
    222     B1B      S4Y2     AD     1000
    
    222     Z2B      RRY3     BC     1203
    
    222     AAA      DDT2     AB     2100
    
    222     YYY      DST2     AB     2111
    
    222     DZX      RDT2     ZB     2500
    
    222     ADC      QWR3     B1D    1200
    
    222     DDC      ZWR3     BCB    900
    Base_table has the two different set of rows


    In that 'ON' check, we have to match the rows even third column
    ID_Ls has the possibility of matching with lower/higher value of 2nd byte

    in that both set of rows.matching rows would be 2 rows

    but we need to match record of ID_LS column value
    if it has next higher/lower value.

    for 3 bytes ID_LS value,if the 2nd byte has the numeral value,it would be applicable for
    lower.higher value comparison


    For ex:
    Code:
    RL_NO  ID_BG    ID_MM    ID_LS  ID_VAL
    
    111     B1B      S4Y2     AC     2000
    111     AAA      DDT2     AA     200
    111     ADC      QWR3     B1C    1200
    
    
    
    222     B1B      S4Y2     AD     1000
    222     AAA      DDT2     AB     2100
    222     ADC      QWR3     B1D    1200
    
    
    Result will be
    
    
    RL_NO  ID_BG    ID_MM    ID_LS  ID_VAL  RL_NO1  ID_BG1    ID_MM1    ID_LS1  ID_VAL1 
    
    111     B1B      S4Y2     AC     2000   222     B1B      S4Y2     AD     1000  
    
    111     AAA      DDT2     AA     200    222     AAA      DDT2     AB     2100  
    
    111     ADC      QWR3     B1C    1200   222     ADC      QWR3     B1D    1200
    Code:
    Expected Result set will be
    
    
    RL_NO  ID_BG    ID_MM    ID_LS  ID_VAL  RL_NO1  ID_BG1    ID_MM1    ID_LS1  ID_VAL1 
                                                                                   
    111     A1B      R4Y2     AA     200    222     A1B      R4Y2     AA     500   
                                                                                   
    111     B1B      S4Y2     AC     2000   222     B1B      S4Y2     AD     1000  
                                                                                   
    111     Z2B      RRY3     BC     1200   222     Z2B      RRY3     BC     1203  
                                                                                   
    111     AAA      DDT2     AA     200    222     AAA      DDT2     AB     2100  
                                                                                   
    111     Z9A      YWR3     BC     1200    
                                            222     YYY      DST2     AB     2111  
                                                                                   
    111     ADC      QWR3     B1C    1200   222     ADC      QWR3     B1D    1200   
    
     
                                            222     DZX      RDT2     ZB     2500 
                                                                                   
    111     DDC      ZWR3     BCC    100C    
                                                                                   
                                            222     DDC      ZWR3     BCB    900
    Please help

    Thanks,

  2. #2
    Join Date
    Jul 2016
    Location
    Germany
    Posts
    32
    Provided Answers: 2

    quick and dirty

    Hi Billa007,

    if you ignore the ID_LS in the join you get:

    RL_NO ID_BG ID_MM ID_LS ID_VAL RL_NO ID_BG ID_MM ID_LS ID_VAL
    ------ ----- ------ ----- ----------- ------ ----- ------ ----- -----------
    111 A1B R4Y2 AA 200 222 A1B R4Y2 AA 500
    111 B1B S4Y2 AC 2000 222 B1B S4Y2 AD 1000
    111 Z2B RRY3 BC 1200 222 Z2B RRY3 BC 1203
    111 AAA DDT2 AA 200 222 AAA DDT2 AB 2100
    111 ADC QWR3 B1C 1200 222 ADC QWR3 B1D 1200
    111 DDC ZWR3 BCC 100 222 DDC ZWR3 BCB 900
    - - - - - 222 YYY DST2 AB 2111
    - - - - - 222 DZX RDT2 ZB 2500
    111 Z9A YWR3 BC 1200 - - - - -

    A inner join leads to :

    RL_NO ID_BG ID_MM ID_LS ID_VAL RL_NO ID_BG ID_MM ID_LS ID_VAL
    ------ ----- ------ ----- ----------- ------ ----- ------ ----- -----------
    111 A1B R4Y2 AA 200 222 A1B R4Y2 AA 500
    111 B1B S4Y2 AC 2000 222 B1B S4Y2 AD 1000
    111 Z2B RRY3 BC 1200 222 Z2B RRY3 BC 1203
    111 AAA DDT2 AA 200 222 AAA DDT2 AB 2100
    111 ADC QWR3 B1C 1200 222 ADC QWR3 B1D 1200
    111 DDC ZWR3 BCC 100 222 DDC ZWR3 BCB 900

    Some WHERE clauses with a LENGTH(ID_LS) should do the trick

    Good luck
    db2dp

  3. #3
    Join Date
    Sep 2011
    Posts
    220
    Thank you for the suggestion,

    We cant igonre the ID_LS while joins,why because we may miss the other records also.
    Please see below for example

    The table has same ID_BG and ID_MM but different ID_LS ,but should not match
    Code:
    Base_Table
    
    RL_NO  ID_BG    ID_MM    ID_LS  ID_VAL
    
    111     P2C      HK3     FA     232
    
    
    
    
    
    
    Base_table
     
    RL_NO  ID_BG    ID_MM    ID_LS  ID_VAL
    
    222     P2C      HK3      TT     500
    
    
    OUTPUT RESULTSET LIKE BELOW
    
    
    RL_NO  ID_BG    ID_MM    ID_LS  ID_VAL  RL_NO1  ID_BG1    ID_MM1    ID_LS1  ID_VAL1 
    
    111     P2C      HK3     FA     232
                                             222     P2C      HK3     FA     232
    Thanks

  4. #4
    Join Date
    Jul 2016
    Location
    Germany
    Posts
    32
    Provided Answers: 2

    quick and dirty

    Hi Billa007,

    that's the reason for the WHERE clauses

    ...
    WHERE
    (
    LENGTH(ID_LS) = 2
    AND
    SUBSTR(T1.ID_LS,1,1) = SUBSTR(T2.ID_LS,1,1)
    AND
    ...
    )
    OR
    (
    LENGTH(ID_LS) = 3
    AND
    SUBSTR(T1.ID_LS,1,1) = SUBSTR(T2.ID_LS,1,1)
    AND
    ...
    )

    Still some work to do for you ...

    Good luck
    db2dp

Posting Permissions

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