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

    Unanswered: String function in query

    Hi;

    We are sending the three columns value from the TABLE1 and fetching the related values from the another TABLE2.,that table has only the concatenated value
    for eg,, Table1 has the 3 column values like RET E123 YC ,but the Table2 has

    RET+E123+YC ,so we has to retrieve those values from Table2

    We have to get more records for single fetch,so that i am using LOW and High Values

    DB2 v9.1 Z/OS

    datatype

    FULL_NUMBER CHAR(20)
    FIRST_NO CHAR(5)
    SECOND_NO CHAR(6)
    THIRD_NO CHAR(7)

    Here is the Query,but it is giving error...

    Code:
    SELECT 
    T1.FIRST_NO
    ,T1.SECOND_NO
    ,T1.THIRD_NO
    ,T2.CD_DES
    FROM TABLE1 T1
    INNER JOIN
     TABLE2    T2
    ON
    T1.IN_CDE = T2.IN_CDE  
    
    WHERE
    
    T2.FULL_NUMBER =((T1.FIRST_NO BETWEEN :WS-FIRST-LOW AND :WS-FIRST-HIGH) || '+'
                   
                   (T1.SECOND_NO BETWEEN :WS-SECOND-LOW AND :WS-SECOND-HIGH) || '+'
                   (T1.THIRD_NO BETWEEN :WS-THIRD-LOW AND :WS-THIRD-HIGH)
    Code:
    TABLE1
    
    IN_CDE       FIRST_NO  SECOND_NO   THIRD_NO
    XXXX          QAE         456         WES
    YYYY          SER21      WUE009        AC
    ZZZZ                    XTERKFD      ERTV
    
    and table2 has the concatenated value 
    
    TABLE2
    IN_CDE        FULL_NUMBER          CD_DES   
    
    XXXX          QAE+456+WES           X123
    YYYY          SER21+WUE009+AC       Y123
    ZZZZ              +XTERKFD+ERTV     Z123
    Please help...

  2. #2
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    You are going about it in the wrong manner. It should be something along these lines.
    Code:
    SELECT 
    T1.FIRST_NO
    ,T1.SECOND_NO
    ,T1.THIRD_NO
    ,T2.CD_DES
    FROM TABLE1 T1
    INNER JOIN
     TABLE2    T2
    ON
    T1.IN_CDE = T2.IN_CDE  
    AND T2.FULL_NUMBER = (T1.FIRST_NO  || T1.SECOND_NO  || T1.THIRD_NO )
    --wasn't sure if you really wanted the + sign in there or just the values or if 
    --you actually wanted to add the numbers together, regardless you can
    --change above to suit.
    WHERE
    
    T1.FIRST_NO BETWEEN :WS-FIRST-LOW 
                             AND :WS-FIRST-HIGH  
    AND T1.SECOND_NO BETWEEN :WS-SECOND-LOW 
                                       AND :WS-SECOND-HIGH
    AND T1.THIRD_NO BETWEEN :WS-THIRD-LOW 
                                     AND :WS-THIRD-HIGH)
    Dave

Posting Permissions

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