fshtank
asked on
DB2 [AS/400] Stored Procedure - using LIKE '%' in WHERE clause not working
Everyone,
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I solved my problem by converting incoming Parameter USER_NAME from CHAR(10) to VARCHAR(10).
Question closed.