Prev: can not start db2 after changing hostname in db2nodes.cfg fileto service IP alias
Next: LOGRETAIN for SQL Replication
From: Fin on 21 Jun 2010 14:24 I am using DB2 v9.7.1 and I have a column named for arguments sake 'IN_STRING' defined as VARCHAR(250) The contents of which are for example '246485+522831+1431234'. Now what I an 'trying' to do is use this value in an SQL Query as an 'IN" clause, ie:(246485,522831,1431234) so I am using TRANSLATE to get rid of the '+' and substitute a comma. I can do so by using TRANSLATE(IN_STRING,',','+') which by itself works just fine. The problem I have however is whenever I add the IN clause I get the following error: SQL0420N Invalid character found in a character string argument of the function "DECFLOAT". SQLSTATE=22018 From what I can tell the problem is something to do with the length of the result and for the life of me I cannot get it to TRIM, STRIP or anything to get rid of either leading or trailing spaces, especially when using TRANSLATE. Now given that the column IN_STRING is varchar(250) the results can contain values such as: 123456 1786886+76759762 652867+878766828+71282638+6222826+12345573489 etc This seems like it should be the easiest thing in the world but for the life of me I cannot get it to work as a simple value in an SQL IN clause. Nor for that matter can I STRIP or TRIM the field.I have tried TRIMing before TRANSLATEing and visa versa but nothing appears to work. Any assistance would be very greatly appreciated. Tim Query example: SELECT DISTINCT ID, ELEMENT FROM DATA WHERE ID IN(SELECT TRANSLATE(IN_STRING,',','+') FROM DATA1 WHERE ID = 901773);
From: Helmut Tessarek on 21 Jun 2010 15:24 The IN clause expects single values (either separated by commas or as a resultset), whereas you are providing one value (or better said you are providing a string that should represent several values). e.g.: works: select * from table where col in ('1','2') select * from table where col in (1,2) does not work: select * from table where col in ('1,2') Your query returns a string not values, hence the error. Hope this helps. On 21.6.2010 14:24, Fin wrote: > SQL0420N Invalid character found in a character string argument of > the > function "DECFLOAT". SQLSTATE=22018 > SELECT DISTINCT ID, ELEMENT > FROM DATA > WHERE ID IN(SELECT TRANSLATE(IN_STRING,',','+') > FROM DATA1 > WHERE ID = 901773); -- Helmut K. C. Tessarek DB2 Performance and Development /* Thou shalt not follow the NULL pointer for chaos and madness await thee at its end. */
From: Serge Rielau on 21 Jun 2010 16:00 On 6/21/2010 2:24 PM, Fin wrote: > I am using DB2 v9.7.1 and I have a column named for arguments sake > 'IN_STRING' defined as VARCHAR(250) The contents of which are for > example '246485+522831+1431234'. > > Now what I an 'trying' to do is use this value in an SQL Query as an > 'IN" clause, ie:(246485,522831,1431234) so I am using TRANSLATE to get > rid of the '+' and substitute a comma. I can do so by using > TRANSLATE(IN_STRING,',','+') which by itself works just fine. The > problem I have however is whenever I add the IN clause I get the > following error: > > SQL0420N Invalid character found in a character string argument of > the > function "DECFLOAT". SQLSTATE=22018 > > From what I can tell the problem is something to do with the length of > the result and for the life of me I cannot get it to TRIM, STRIP or > anything to get rid of either leading or trailing spaces, especially > when using TRANSLATE. > > Now given that the column IN_STRING is varchar(250) the results can > contain values such as: > > 123456 > 1786886+76759762 > 652867+878766828+71282638+6222826+12345573489 > etc > > This seems like it should be the easiest thing in the world but for > the life of me I cannot get it to work as a simple value in an SQL IN > clause. Nor for that matter can I STRIP or TRIM the field.I have tried > TRIMing before TRANSLATEing and visa versa but nothing appears to > work. > > Any assistance would be very greatly appreciated. > > Tim > > Query example: > > SELECT DISTINCT ID, ELEMENT > FROM DATA > WHERE ID IN(SELECT TRANSLATE(IN_STRING,',','+') > FROM DATA1 > WHERE ID = 901773); > Tim, TRIM and TRANSLATE both turn one scalar string value into another scalar string value. They do not decompose a string into a some sort of components which is what you appear to be trying. Search this group for XMLTABLE. That may be the easiest way to "shred" the values out. Cheers Serge -- Serge Rielau SQL Architect DB2 for LUW IBM Toronto Lab
From: Tonkuma on 21 Jun 2010 16:08
Making a table function may be another solution. Here is an example of table UDF: ------------------------------ Commands Entered ------------------------------ CREATE FUNCTION extract_element ( in_string VARCHAR(254) , delimiter VARCHAR(1) ) RETURNS TABLE(element VARCHAR(254) ) READS SQL DATA DETERMINISTIC NO EXTERNAL ACTION RETURN WITH find_delimiters(k , pos) AS ( VALUES (0 , 0) UNION ALL SELECT k + 1 , LOCATE(delimiter , in_string , pos + 1) FROM find_delimiters WHERE k < 254 AND (k = 0 OR pos > 0) ) SELECT SUBSTR( in_string , pos1 + 1 , CASE pos2 WHEN 0 THEN LENGTH(in_string) + 1 ELSE pos2 END - pos1 - 1 ) FROM find_delimiters f1(k1 , pos1) , find_delimiters f2(k2 , pos2) WHERE k2 = k1 + 1 ; ------------------------------------------------------------------------------ DB20000I The SQL command completed successfully. Sample usage of the UDF: ------------------------------ Commands Entered ------------------------------ WITH search_pattern(id , in_string) AS ( VALUES (1 , '000100+000200+000300') , (2 , '000102+000202+000302') , (3 , '000400') , (4 , '000200') , (5 , '00100+000100+0000100+00000100') ) SELECT id , empno , in_string FROM employee , search_pattern WHERE empno IN (SELECT element FROM TABLE( extract_element(in_string , '+') ) AS t ); ------------------------------------------------------------------------------ ID EMPNO IN_STRING ----------- ------ ----------------------------- 1 000100 000100+000200+000300 1 000200 000100+000200+000300 1 000300 000100+000200+000300 4 000200 000200 5 000100 00100+000100+0000100+00000100 5 record(s) selected. |