Sql problem with LIKE in WHERE: sqlcode +100

pankaj_psm · New User Joined: 03 May 2005 Posts: 4 Location: Pune

Hi there,
I have one problem in COBOL-DB2 -CICS program with LIKE in WHERE clause.This sql runs in SPUFI with hardcoded value but in program it is giving sqlcode +100.
The column in where clause is char(26) and has values like
'SCMTA 20070226093602001' .I want to ignore last 3 digit( i.e. 001) in sql. So value of WS-APLCN should be 'SCMTA 20070226093602%'. OR 'SCMTA 20070226093602&&&'
Please suggest .....

EXEC SQL
UPDATE mytable name
SET column-name = '99'
WHERE column name LIKE :WS-APLCN
END-EXEC.

Pankaj

William Thompson · Posted: Fri Mar 23, 2007 5:42 pm

Isn't there a substring function you could use?

vini_srcna · Posted: Mon Mar 26, 2007 10:07 am

I believe the host variable value is not populated correctly in the run time.
Make sure % wild char is in place before the execution.

This would not be an efficient method, Instead you can use the bulit in function SUBSTR as suggested by William.

Try this:
EXEC SQL
UPDATE mytable name
SET column-name = '99'
WHERE SUBSTR(colname,1,23) = :WS-APLCN
END-EXEC.

In this case WS-APLCN can have the value 'SCMTA 20070226093602' .
I mean no need for wild char's. Hope this helps. Thanks