Re: Strange SQL problem, same where clause works in one statement but not the other....

Ok,

Here's a simplified proc that has the same error...

create procedure dvpcmwnet.TestWhere (
in v_Description varchar(30))

DYNAMIC RESULT SETS 1
LANGUAGE SQL
NOT DETERMINISTIC
READS SQL DATA
CALLED ON NULL INPUT
SET OPTION
ALWBLK = *ALLREAD ,
ALWCPYDTA = *OPTIMIZE ,
DATFMT = *ISO ,
DBGVIEW = *SOURCE ,
SQLPATH = *LIBL ,
USRPRF = *OWNER,
DYNUSRPRF = *OWNER

BEGIN
DECLARE wDesc VARCHAR ( 31 ) ;
declare wTotalRows integer;

declare wSQL varchar(2000);
declare wSQLselect varchar(512);
declare wSQLFromAndWhere varchar(1024);
declare wSQLcount varchar(1024);

declare C1 cursor for S1;

set wSQLselect =
'select cast(? as integer) as Total_Rows, Description, ID ';

set wSQLFromAndWhere =
' from Customer_Item_History A'
CONCAT ' where jdivsn = 0 and jcusnr = 23510'
CONCAT ' and jordte between 1090201 and 1090228'
concat ' AND ( ( cast(? as varchar(30)) IS NULL ) '
concat ' OR ( A.DESCRIPTION LIKE ? ) ) ';

SET wDesc = UPPER ( V_DESCRIPTION CONCAT '%' ) ;

set wSQLcount = 'values (select count(*) ' concat wSQLFromAndWhere
concat ' ) into ?';

prepare cntStmt from wSqlCount;
execute cntstmt
using
v_Description,
wDesc,
wTotalRows;

set wSQL = wSQLselect CONCAT wSQLFromAndWhere;

prepare S1 from wSQL;

open C1 using wTotalRows,
v_Description,
wDesc;

END;


The returned results:
0 , BUSPIRONE 10MG TAB OS , 975730