Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Jun 2011
    Posts
    13

    Unanswered: Case Statement problem

    I am writing a SQL query in DB2 for a report that contains a case statement to pull matching strings from two tables. The case statement is below:

    CASE
    WHEN LEFT( t1.stringfield,7) = LEFT(t2.stringfield,7) THEN substr(t2.stringfield,1,7)

    WHEN SUBSTR(t1.stringfield,3,7) = SUBSTR(t2.stringfield,8,12) THEN SUBSTR(t2.stringfield,6,12)

    ELSE ' '
    END AS Derive

    the first 7 characters and the 3rd character to 7 of table 't1.stringfield' has a matching characters in table 't2.stringfield' in the 8th and 6th place of table 't2.stringfield' the string. I am getting an error as shown below :

    Failed to retrieve data from Database
    SQL0138N A numeric argument of a bulit-in string function is out of range SLQSTATE = 22011



    I dont know what other function can I use to extract string from the middle of a string field. I tried using left(right( in place of substr, but they dont pull out the the exact locations of the string. Please, Any help will be appreciated

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    It might mean that some of the strings are shorter than you think.
    Code:
    C:\>db2 values substr('abc',1,1)
    
    1
    -
    a
    
      1 record(s) selected.
    
    
    C:\>db2 values substr('abc',1,10)
    SQL0138N  A numeric argument of a built-in string function is out of range.
    SQLSTATE=22011
    
    C:\>db2 values substr('abc',10,1)
    SQL0138N  A numeric argument of a built-in string function is out of range.
    SQLSTATE=22011

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    WHEN SUBSTR(t1.stringfield,3,7) = SUBSTR(t2.stringfield,8,12) THEN SUBSTR(t2.stringfield,6,12)
    You might misunderstand the third parameter of SUBSTR built-in function.
    It is the length of the returned string.
    So, you might want to code...
    WHEN SUBSTR(t1.stringfield,3,5) = SUBSTR(t2.stringfield,8,5) THEN SUBSTR(t2.stringfield,6,7)

  4. #4
    Join Date
    Jun 2011
    Posts
    13

    Case statement problem

    Thanks for the reply, but there are millions of records in the stringfield and the length of the characters differ from row to row, some have 12 characters, some have 7 and so on, so what function in DB2 would be able to handle variable character lengths in a stringfield, besides substr?

  5. #5
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Something like
    Code:
    WHEN 
     LENGTH(t1.stringfield) >= 10 AND 
     LENGTH(t2.stringfield) >= 20 AND 
     SUBSTR(t1.stringfield,3,7) = SUBSTR(t2.stringfield,8,12) 
    THEN SUBSTR(t2.stringfield,6,12) ...

  6. #6
    Join Date
    Jun 2011
    Posts
    13

    Case statement problem

    Thanks buddy ! Let me try it out.

  7. #7
    Join Date
    Jun 2011
    Posts
    13

    Case statement problem

    Quote Originally Posted by n_i View Post
    Something like
    Code:
    WHEN 
     LENGTH(t1.stringfield) >= 10 AND 
     LENGTH(t2.stringfield) >= 20 AND 
     SUBSTR(t1.stringfield,3,7) = SUBSTR(t2.stringfield,8,12) 
    THEN SUBSTR(t2.stringfield,6,12) ...

    I tried using that method above but I get the same error as before.

    Failed to retrieve data from the database SQL0138N A numeric argument of a built-in function is out of range SQLSTATE=22011

  8. #8
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    I'm sure you have adjusted the LENGTH() comparison values according to the actual lengths of your strings. The point is to short-circuit the boolean expression before it gets to the SUBSTR() part.

  9. #9
    Join Date
    Jun 2011
    Posts
    13

    c

    ohh ok. let me check on that. Thanks!

  10. #10
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    It also matters if the column datatype is VARCHAR or CHAR. LENGTH() of a CHAR column will always be the same, while that of VARCHAR will vary according to the actual value length.

  11. #11
    Join Date
    Jun 2011
    Posts
    13

    Case statement problem

    I think the string is varchar because looking through the records, the actual lengths are different for different rows, the lengths are not fixed.

  12. #12
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Please show the result of this query,
    if you have not solved yet your problem.

    Code:
    SELECT LENGTH(t1.stringfield) AS len_str_1
         , LENGTH(t2.stringfield) AS len_str_2
         , COUNT(*)               AS count_rows
     FROM  t1
     JOIN  t2
       ON  <matching conditions>
     GROUP BY
           LENGTH(t1.stringfield)
         , LENGTH(t2.stringfield)
     ORDER BY
           len_str_1
         , len_str_2
    ;
    Last edited by tonkuma; 06-29-11 at 02:17. Reason: Add "if you have not solved yet your problem."

  13. #13
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Whhat you also could do (if the outcome of this is fine for you) is first pad the input data with blanks before taking substrings:
    Code:
    CASE
    WHEN LEFT( t1.stringfield||'       ',7) = LEFT(t2.stringfield||'       ',7)
     THEN substr(t2.stringfield,1,7)
    WHEN SUBSTR(t1.stringfield||'        ',3,5)
          = SUBSTR(t2.stringfield||'            ',8,5)
     THEN SUBSTR(t2.stringfield,6,7)
    ELSE ' '
    END AS Derive
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  14. #14
    Join Date
    Jun 2011
    Posts
    13

    Case statement problem

    Quote Originally Posted by tonkuma View Post
    Please show the result of this query,
    if you have not solved yet your problem.

    Code:
    SELECT LENGTH(t1.stringfield) AS len_str_1
         , LENGTH(t2.stringfield) AS len_str_2
         , COUNT(*)               AS count_rows
     FROM  t1
     JOIN  t2
       ON  <matching conditions>
     GROUP BY
           LENGTH(t1.stringfield)
         , LENGTH(t2.stringfield)
     ORDER BY
           len_str_1
         , len_str_2
    ;


    Thanks. Here is a sample of the result set:

    len_str_1 | len_str_2 | Count_Rows

    7 | 1 | 33
    7 | 4 | 688
    7 | 5 | 258
    7 | 6 | 265
    7 | 7 | 13901
    7 | 8 | 1158
    7 | 9 | 336
    7 | 10 | 654
    7 | 12 | 851

  15. #15
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Looking into your data,
    length of t1.stringfield were fixed to 7
    and maximun length of t2.stringfield was 12.

    If those were guaranteed
    (for example: data type of t1.stringfield was CHAR(7) and data type of t2.stringfield was VARCHAR(12).)
    then, try...
    Code:
    CASE
    WHEN t1.stringfield            = LEFT (t2.stringfield , 7) THEN
         t1.stringfield
    WHEN LENGTH(t2.stringfield) = 12
     AND RIGHT(t1.stringfield , 5) = RIGHT(t2.stringfield , 5) THEN
         RIGHT(t2.stringfield , 7)
    ELSE ' '
    END  AS Derive
    It returns 7 character string when matched on first WHEN clause.
    (if t2.stringfield was shorter than 7, padded with blanks)

    What DB2 version and platform are you using?
    If you are using older version, data type of returned value may be long.

Posting Permissions

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