Results 1 to 9 of 9

Thread: Error in joins

  1. #1
    Join Date
    Sep 2011
    Posts
    220

    Unanswered: Error in joins

    Hi

    The below query throwing the error code like
    SQLCODE=-338
    SQLSTATE=42972


    DB2 9.1 z/os
    Code:
    select DISTINCT
    
    T1.P_LOC,
    T2.P1_FUL_NUM,
    T2.P_PCODE
    
    FROM TABLE1 T1
    
    LEFT OUTER JOIN
    TABLE2 T2
    
    ON                                              
          T1.P_LOC    =  T2.P_LOC
            AND  T2.P1_FUL_NUM =       
           T1.P1_NUM || '-'
    ||     T1.P2_NUM 
    AND T2.P_PLT IN (SELECT DISTINCT T1_T3.P_PLT FROM TABLE1 T1_T3 
       WHERE 
       T1_T3.P_LOC    =  T2.P_LOC
            AND  T2.P1_FUL_NUM =       
           T1_T3.P1_NUM || '-'
    ||     T1_T3.P2_NUM )
    pl help
    Code:
    TABLE1
    
    P_LOC  P1_NUM P2_NUM P_PLT
    AAA     1111   AA    A121,B122
    AAA     1111   AA    D123,E124,F125
    AAA     1111   AA    A121,G126
    BBB     2222   BB    B122
    
    TABLE2
    
    P_LOC  P1_FUL_NUM P_PLT  P_PCODE
    AAA    1111-AA    B122   AA1
    AAA    1111-AA    E124   AA2
    BBB    2222-BB    B122   BB1
    Code:
    EXPECTED RESULT
    
    P_LOC  P1_ful_NUM  P_PCODE
    AAA    1111-AA     AA1
    BBB    2222-BB     BB1
    PLEASE HELP

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Did you saw manuals?
    DB2 9 - Codes - SQLCODE -338

    -338

    AN ON CLAUSE IS INVALID

    Explanation

    A statement contains an invalid ON clause. Conditions that can cause this error include, but are not limited to, violations of the following requirements:

    •For a full join, each expression of the predicate must reference only columns in its own operand tables.
    •Only VALUE and COALESCE functions are allowed in the ON clause for full outer joins and full joins.
    •Full outer joins and full joins permit only the = operator.
    The ON clause cannot contain a subquery.
    •The ON clause cannot contain an XMLEXISTS predicate
    Move the IN predicate to WHERE clause.

    Another issue is the IN predicate might not work as you expected.
    It is equivalent to something like...
    'E124' IN ('D123,E124,F125')
    Last edited by tonkuma; 04-04-12 at 12:18. Reason: Replace link to "DB2 9" from "DB2 10".

  3. #3
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963
    This part couldn't be in ON clause: "AN ON CLAUSE IS INVALID"...

    AND T2.P_PLT IN (SELECT DISTINCT T1_T3.P_PLT FROM TABLE1 T1_T3
    WHERE
    T1_T3.P_LOC = T2.P_LOC
    AND T2.P1_FUL_NUM =
    T1_T3.P1_NUM || '-'
    || T1_T3.P2_NUM )
    Lenny

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Are your sample data and expected results correct?

    column p_plt of row 2 of table1 include 'E124' which is equal to the value of column p_plt of row 2 of table2.
    Why was row 2 of table2 excluded?

  5. #5
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Arrow The solution

    If we'll move IN from ON to WHERE we'll not have error but Left Join became the Inner Join. Result we'll be incorrect.

    I think this simple change will solve the problem:

    Code:
    select DISTINCT
    T1.P_LOC,
    T2.P1_FUL_NUM,
    T2.P_PCODE
    
    FROM 
    TABLE1 T1
    LEFT JOIN
    (
    select t2i.*
    from TABLE2 T2i
    Where 
    T2i.P_PLT IN (SELECT DISTINCT T1_T3.P_PLT FROM TABLE1 T1_T3 
       WHERE 
       T1_T3.P_LOC =  T2i.P_LOC
            AND  T2i.P1_FUL_NUM =       
           T1_T3.P1_NUM || '-'
    ||     T1_T3.P2_NUM )
    ) t2
    
    ON                                              
          T1.P_LOC    =  T2.P_LOC
            AND  T2.P1_FUL_NUM =       
           T1.P1_NUM || '-'
    ||     T1.P2_NUM
    Lenny

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Quote Originally Posted by tonkuma View Post
    ...

    Another issue is the IN predicate might not work as you expected.
    It is equivalent to something like...
    'E124' IN ('D123,E124,F125')
    I tested Lenny's example on my DB2 9.7.5 on Windows/XP.

    Example 1: All values from t2 in a row with P_LOC = 'AAA' were NULL.
    Code:
    select DISTINCT
    T1.P_LOC,
    T2.P1_FUL_NUM,
    T2.P_PCODE
    
    FROM 
    TABLE1 T1
    LEFT JOIN
    (
    select t2i.*
    from TABLE2 T2i
    Where 
    T2i.P_PLT IN (SELECT DISTINCT T1_T3.P_PLT FROM TABLE1 T1_T3 
       WHERE 
       T1_T3.P_LOC =  T2i.P_LOC
            AND  T2i.P1_FUL_NUM =       
           T1_T3.P1_NUM || '-'
    ||     T1_T3.P2_NUM )
    ) t2
    
    ON                                              
          T1.P_LOC    =  T2.P_LOC
            AND  T2.P1_FUL_NUM =       
           T1.P1_NUM || '-'
    ||     T1.P2_NUM
    ;
    ------------------------------------------------------------------------------
    
    P_LOC P1_FUL_NUM P_PCODE
    ----- ---------- -------
    BBB   2222-BB    BB1    
    AAA   -          -      
    
      2 record(s) selected.

    I thought that it might be better to access from table2.

    Example 2: No row with P_LOC = 'AAA' was returned.
    Code:
    SELECT p_loc
         , p1_ful_num
         , p_pcode
     FROM  table2 t2
     WHERE EXISTS
          (SELECT 0
            FROM  table1 t1
            WHERE t2.p_loc      = t1.p_loc
              AND t2.p1_ful_num = t1.p1_num || '-' || t1.p2_num
              AND t2.p_plt IN t1.p_plt
          )
    ;
    ------------------------------------------------------------------------------
    
    P_LOC P1_FUL_NUM P_PCODE
    ----- ---------- -------
    BBB   2222-BB    BB1    
    
      1 record(s) selected.

    Example 3: Replace the IN predicate with a LOCATE function. Two rows with P_LOC = 'AAA' returned.
    Code:
    SELECT p_loc
         , p1_ful_num
         , p_pcode
     FROM  table2 t2
     WHERE EXISTS
          (SELECT 0
            FROM  table1 t1
            WHERE t2.p_loc      = t1.p_loc
              AND t2.p1_ful_num = t1.p1_num || '-' || t1.p2_num
              AND LOCATE(t2.p_plt || ',' , t1.p_plt || ',') > 0
          );
    ------------------------------------------------------------------------------
    
    P_LOC P1_FUL_NUM P_PCODE
    ----- ---------- -------
    AAA   1111-AA    AA1    
    AAA   1111-AA    AA2    
    BBB   2222-BB    BB1    
    
      3 record(s) selected.
    Quote Originally Posted by tonkuma View Post
    Are your sample data and expected results correct?

    column p_plt of row 2 of table1 include 'E124' which is equal to the value of column p_plt of row 2 of table2.
    Why was row 2 of table2 excluded?

  7. #7
    Join Date
    Sep 2011
    Posts
    220
    Thanks for the reply...

    Query working fine,but the i could not get the Table1 values..Table1 is the main table..

    Table2 using for only to fetch the P_PCODE value with the match of P_LOc and P1_NUM and P2_NUM

    we have to return more colums from Table1,so that ,need the query like to get all the columns value from Table1;
    Thanks for the understanding..

  8. #8
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    If you want other columns in table1,
    change EXISTS to JOIN in Example 3..

    Example 4:
    Code:
    SELECT t1.p_loc
         , t1.p_plt AS t1_p_plt
         , t2.p_plt AS t2plt
         , t2.p1_ful_num
         , t2.p_pcode
     FROM  table1 t1
     INNER JOIN
           table2 t2
      ON   t2.p_loc      = t1.p_loc
       AND t2.p1_ful_num = t1.p1_num || '-' || t1.p2_num
       AND LOCATE(t2.p_plt || ',' , t1.p_plt || ',') > 0
    ;
    ------------------------------------------------------------------------------
    
    P_LOC T1_P_PLT       T2PLT P1_FUL_NUM P_PCODE
    ----- -------------- ----- ---------- -------
    AAA   A121,B122      B122  1111-AA    AA1    
    AAA   D123,E124,F125 E124  1111-AA    AA2    
    BBB   B122           B122  2222-BB    BB1    
    
      3 record(s) selected.

  9. #9
    Join Date
    Sep 2011
    Posts
    220
    THANKS TO ALL! working fine..

Posting Permissions

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