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

    Unanswered: String handling in the Select query

    Hi;

    I need to execute below query

    Table1 has the sub parts of P_WHOLE_NO in Table2 along with the - value.

    All columns data types are CHAR
    P_SOR CHAR(4)
    P_NOR CHAR(5)
    P_XOR CHAR(3)

    i need to join the table2 with table1 with the matching conditions

    I was trying to split the P_WHOLE_NO with SUBSTR and matches with
    Table1 columns and get the results of NO DATA...
    Code:
    select DISTINCT
    
     t1.p_loc
    ,t1.p_sor
    ,t1.p_nor
    ,t1.p_xor
    ,t2.s_code
    
    
    
    from
    
      table1 t1
    inner join
    
      TABLE2 T2
    on
    t2.p_loc=t1.p_loc
    and substr(t2.p_whole_no,1,4) = t1.p_sor
    and substr(t2.p_whole_no,6,5) = t1.p_nor
    and substr(t2.p_whole_no,12,3) = t1.p_xor
    TABLE1
    Code:
    p_loc   p_SOR        p_NOR   P_XOR
    AAA     A115         E1114    D11     
    BBB      131          T543    E2 
    CCC                  D2345    R
    DDD     C12         44324
    TABLE2
    Code:
    P_LOC    p_whole_no          S_CODE
    AAA      A115-E1114-D11      WEST
    AAA      A115-E1114-D11      EAST
    BBB      131-T543-E2         WEST
    CCC      -D2345-R            WEST
    DDD      C12-44324           EAST 
    DDD      C12-44324           WEST 
    DDD      C12-44324           NORT
    Expected Result set
    Code:
    p_loc   p_SOR        p_NOR   P_XOR   S_CODE
    AAA     A115         E1114    D11     WEST
    AAA     A115         E1114    D11     EAST
    BBB      131          T543    E2      WEST
    CCC                  D2345    R       WEST
    DDD     C12          44324            EAST
    DDD     C12          44324            WEST
    DDD     C12          44324            NORT
    Pl help..how to match the joining conditions

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Although the following sample query gives you same result as your "Expected Result set",
    it might not the right answer which you expected.

    If the query was not the expected answer,
    the reason might be that your sample data were too little
    and not covered some possible (exceptional?) combinations of rows of table1 and table2
    including mathing and un-matching rows.

    Example 1: Added some result columns to show how the query worked.
    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH
     TABLE1(p_loc , p_SOR , p_NOR , P_XOR) AS (
    VALUES
      ( 'AAA' , 'A115' , 'E1114' , 'D11' )
    , ( 'BBB' , '131'  , 'T543'  , 'E2'  )
    , ( 'CCC' , ''     , 'D2345' , 'R'   )
    , ( 'DDD' , 'C12'  , '44324' , ''    )
    )
    , TABLE2(P_LOC , p_whole_no , S_CODE) AS (
    VALUES
      ( 'AAA' , 'A115-E1114-D11' , 'WEST' )
    , ( 'AAA' , 'A115-E1114-D11' , 'EAST' )
    , ( 'BBB' , '131-T543-E2'    , 'WEST' )
    , ( 'CCC' , '-D2345-R'       , 'WEST' )
    , ( 'DDD' , 'C12-44324'      , 'EAST' )
    , ( 'DDD' , 'C12-44324'      , 'WEST' )
    , ( 'DDD' , 'C12-44324'      , 'NORT' )
    )
    select /*DISTINCT*/
           t1.p_loc
         , t1.p_sor
         , t1.p_nor
         , t1.p_xor
         , t2.s_code
         , t2.p_whole_no
         , substr(t2.p_whole_no, 1,4) AS t2_sor
         , substr(t2.p_whole_no, 6,5) AS t2_nor
         , substr(t2.p_whole_no,12,3) AS t2_xor
     from
           table1 t1
     inner join
           TABLE2 T2
       on
           t2.p_loc = t1.p_loc
       AND (
                substr(t2.p_whole_no, 1,4)  = t1.p_sor
            and substr(t2.p_whole_no, 6,5)  = t1.p_nor
            and substr(t2.p_whole_no,12,3)  = t1.p_xor
            OR  substr(t2.p_whole_no, 1,4) <> t1.p_sor
            OR  substr(t2.p_whole_no, 6,5) <> t1.p_nor
            OR  substr(t2.p_whole_no,12,3) <> t1.p_xor
           )
    ;
    ------------------------------------------------------------------------------
    
    P_LOC P_SOR P_NOR P_XOR S_CODE P_WHOLE_NO     T2_SOR T2_NOR T2_XOR
    ----- ----- ----- ----- ------ -------------- ------ ------ ------
    AAA   A115  E1114 D11   WEST   A115-E1114-D11 A115   E1114  D11   
    AAA   A115  E1114 D11   EAST   A115-E1114-D11 A115   E1114  D11   
    BBB   131   T543  E2    WEST   131-T543-E2    131-   543-E        
    CCC         D2345 R     WEST   -D2345-R       -D23   5-R          
    DDD   C12   44324       EAST   C12-44324      C12-   4324         
    DDD   C12   44324       WEST   C12-44324      C12-   4324         
    DDD   C12   44324       NORT   C12-44324      C12-   4324         
    
      7 record(s) selected.

Posting Permissions

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