DB2 [AS/400] Stored Procedure - using LIKE '%' in WHERE clause not working
Posted on 2007-04-11
The following Stored Proc Selects Data via cursor, then proceeds to DELETE data from three tables with the same key - including the same table from which the key originated.
here is my query below:
DECLARE cursor1 CURSOR FOR
select a.TRTRNO as AuditTranNumber
, a.TRRXNO as ClaimNumber
, a.TRRXSQ as ClaimSeq
FROM PSIAUDIT.AUTRAN a
WHERE IFNULL(a.TRSCOR, 0) BETWEEN TRAN_SCORE_FROM AND TRAN_SCORE_TO
AND IFNULL(a.TREDAT, 19000101) BETWEEN DATE_FROM AND DATE_TO
AND IFNULL(a.TRASNN, '') LIKE USER_NAME;
Focusing on the last line (LIKE USER_NAME . . . CHAR(10) b.t.w.)
1) I pass in '%' or 'LNAME_FN' and I get nothing (no results returned, nothing deleted)
2) When I comment out this line the Stored proc does what it is supposed to do and deletes the data. (aka. data was returned in the cursor.)
3) i run the above as a SELECT (as-is except I hard code the params) in iSQL and it returns data. So something is awry.
So, given the above:
1) What is wrong with this query from a Stored proc perspective?
2) How can I leave in the USER_NAME column in case I want to delete by that icriteria as well as the other column criteria?
thanks for your help