Issues with LIKE keyword usage in dynamic SQL

[sri8707]

Hi All,

I use dynamic SQL for performing some search operation.

Example: User inputs a value for fieldname on screen. I pick that value and run SQL on a table to identify all records that has the field name as input by the user.

I use dynamic SQL for the same (I cannot use static sql as there are additional fields that are input only at later point in time).



SqlFLDStr string is built as "'Select distinct a,b,c from lib/file where fieldname = ?"

Declare cursor routine

C/exec sql
C+ Prepare FldSqlStm from :SqlFldStr
C/end-exec

C/exec sql
C+ Declare C1 cursor for FldSqlStm
C/end-exec

Open cursor routine

C/exec sql
C+ Open C1 using :Fld1 // This is where i face problem when LIKE keyword is used
C/end-exec

If user inputs a value of ABC on screen, then my logic is fine.

However, if user inputs a value of %ABC on screen, then i have to find all records that include SSSABC, DDDABC, etc.

But the problem is, FLD1 is defined as 10 characters in length. I am unable to trim this field when passing it through the "Open Cursor dynamically". The program considers it as '%ABC_____' (with spaces at end to fill 10 characters) instead of '%ABC'

Any ways to solve this?

[JonBoy]

Define the passed parm as Varchar and trim the input field into it?

[sri8707]

Hi JonBoy,

The problem here is i cannot trim the field while passing to OPEN CURSOR statement.

The input comes from display screen. User inputs value of FLD1 on screen and i have defined that variable as 10 characters.

Case1: Input value = ABC
I run the query as "select * from table where fld = 'ABC' // Works fine

Case2: Input value = ABC%
I am supposed to run the query as "select * from table where fld like 'ABC%'
But, rather the query gets built as "select * from table where fld like 'ABC%______ ' // which is incorrect

I cannot calculate length of 'ABC%' and pass it to my OPEN CURSOR statement as the length could be anywhere from 1 to 10. And I cannot directly trim the field in OPEN CURSOR statement.

[B.Hauser]

You also can add a trim around your variable:

SELECT ... From Table Where Field like Trim('ABC%             ');

In this case it does not make any difference whether there are trainling blanks or not.

You may also do something like this (if you want/need):

Select ... From Table Where Field like '%' concat Trim('ABC          ') concat '%'

[sri8707]

Hi Hauser,

Thanks for your reply. But i am not using static sql here. I am using dynamic sql.

In declare cursor, i have statement like “select * from table where trim(field) like ‘?’ “

only while opening cursor, i pass the value for ? as say, ABC%. How to trim at this point?

[B.Hauser]

So use dynamic instead.
You only have to prepare the string in the way I showed before.

YourSQLCMD = 'SELECT ... FROM TABLE  ' +
               ' WHERE YourColum like Trim(?) concat ''%''';

or

YourSQLCMD = 'SELECT ... FROM TABLE  ' +
               ' WHERE YourColum like ''%'' concat Trim(?) concat ''%''';

Putting a trim arround your original field on the left side of the LIKE will not help!
BTW why to use dynamic SQL at all? What I have seen from your example does not imply dynamic SQL

[sri8707]

Hi Hauser,

I am not sure i got your code.

So, this is what happening -
* User inputs value of A% on screen
* In declare cursor, the SQL is getting built as Select field1, field2 from lib/file where Fieldname like ?
* I am trying to open cursor as below -

FLD1TRIM = 'TRIM('A% ')' // Is this correct?
Open cursor using :FLD1TRIM

The subsequent fetch cursor produces sqlcode of 100 indicating no records found.

But, when i run query on the table as below, i get records.

SELECT field1, field2 FROM lib/file WHERE Fieldname like TRIM('A% ')


Just to add, i use dynamic sql because there are other fields that i pass dynamically (such as library, file, etc).