String handling in the Select query

Billa007 · #1 (**permalink**) 12-21-11, 05:29

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

tonkuma · #2 (**permalink**) 12-21-11, 07:42

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.

Internet Services

Web Development

Digital Marketing

Consumer Tech