Case Statement problem

teel · #1 (**permalink**) 06-27-11, 11:59

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

n_i · #2 (**permalink**) 06-27-11, 13:21

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

tonkuma · #3 (**permalink**) 06-27-11, 13:50

Quote:

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)

teel · #4 (**permalink**) 06-28-11, 11:16

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?

n_i · #5 (**permalink**) 06-28-11, 12:19

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) ...

teel · #6 (**permalink**) 06-28-11, 13:32

Thanks buddy ! Let me try it out.

teel · #7 (**permalink**) 06-28-11, 15:12

Quote:

Originally Posted by n_i

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

n_i · #8 (**permalink**) 06-28-11, 15:33

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.

teel · #9 (**permalink**) 06-28-11, 15:36

ohh ok. let me check on that. Thanks!

n_i · #10 (**permalink**) 06-28-11, 15:38

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.

teel · #11 (**permalink**) 06-28-11, 16:32

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

tonkuma · #12 (**permalink**) 06-28-11, 19:56

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
;

Peter.Vanroose · #13 (**permalink**) 06-29-11, 02:02

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

teel · #14 (**permalink**) 06-29-11, 08:49

Quote:

Originally Posted by tonkuma

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

tonkuma · #15 (**permalink**) 06-29-11, 11:00

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.

Internet Services

Web Development

Digital Marketing

Consumer Tech