SQL Cursor return to client issue while using views

I've a program that needs to return an SQL cursor to a .net app.

I've an SQL in an RPG service program that:

* Is declared as return to client
* Is a SELECT
* Uses a table that is a VIEW that joins at least three other physical tables (other examples with more joins and unions of the VIEWS are going to be needed)
* Uses conditional CASE statements in the WHERE to determine what user-supplied search criteria is to be compared against the views columns

When I copy the SQL from the program and plug in all of the variable data and run as standalone SQL, the data is found.

However, when running the same SQL within the service program...nothing. No SQL errors. No CPF errors. The JOBLOG is clean. No dumps. No data. When testing via iNav, the cursor is returned empty.

I'm stumped.

Sample of SQL within the program:

Exec SQL Declare TABLEget Cursor with return to client for
Select *
from TABLEVW1
where upper(classdesc) like
case when :parmSrchValue <> ' ' and
:SearchOnDesc = 'Y'
then :parmSrchValue
else upper(classdesc) end
and upper(classcode) like
case when :parmSrchValue <> ' ' and
:SearchOnDesc = 'N'
then :parmSrchValue
else upper(classcode) end;


Steve Needles

Northland Insurance

385 Washington Avenue
MC #9275-SB03N

St. Paul MN 55102

(651) 310-4203


==============================================================================
This communication, including attachments, is confidential, may be subject to legal privileges, and is intended for the sole use of the addressee. Any use, duplication, disclosure or dissemination of this communication, other than by the addressee, is prohibited. If you have received this communication in error, please notify the sender immediately and delete or destroy this communication and all copies.