URGENT Help with STRIP on a Varchar(250) with TRANSLATE

[Fin]

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

[Helmut Tessarek]

The IN clause expects single values (either separated by commas or as a
resultset), whereas you are providing one value.

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.
*/

[Helmut Tessarek]

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.
*/

[Serge Rielau]

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

[Tonkuma]

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.