Yeah!!! Data was found, but it wouldn't make itself available to the application reading the cursor. Turned out that blanks in the variable made a difference. Chuck noted the issue and suggested STRIP which fixed the problem.
Cool SQL coding trick!
But in this case, we are fed a parameter to determine whether or not we are doing a "contains" search or a "begins with" search.
It is simply easier to do these string manipulations in RPG rather than in the SQL statement.
Thanks for the feedback.
Steve Needles
-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Birgitta Hauser
Sent: Saturday, May 12, 2012 9:12 AM
To: 'RPG programming on the IBM i / System i'
Subject: AW: SQL Cursor return to client issue while using views
Yes. All zeroes.
Then data must have been found!
If no data is found SQLCOD 100 is returned!
Are fetching the data in the correct data structure?
BTW if you change your DECLARE statement as follows, you do not need to
include percent signs into your host variables nor have to remove any
leading or trailing blanks:
Exec SQL Declare TABLEget Cursor with return to client for
Select *
from TABLEVW1
where upper(classdesc)
like '%' concat
case when Trim(:parmSrchValue) > ''
and :SearchOnDesc = 'Y'
then Trim(:parmSrchValue)
else upper(classdesc) end
concat '%'
and upper(classcode)
like '%' concat
case when Trim(:parmSrchValue) > ''
and :SearchOnDesc = 'N'
then Trim(:parmSrchValue)
else upper(classcode) end
Concat '%';
Mit freundlichen Grüßen / Best regards
Birgitta Hauser
"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"
-----Ursprüngliche Nachricht-----
Von: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx] Im
Auftrag von Needles,Stephen J
Gesendet: Friday, 11.5 2012 17:03
An: RPG programming on the IBM i / System i
Betreff: RE: SQL Cursor return to client issue while using views
Yes. All zeroes.
-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx]
On Behalf Of Justin Taylor
Sent: Friday, May 11, 2012 9:51 AM
To: rpg400-l@xxxxxxxxxxxx
Subject: RE: SQL Cursor return to client issue while using views
Have you checked the SQLSTATE/SQLCOD while running the program?
--
This is the RPG programming on the IBM i / System i (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx To subscribe, unsubscribe, or
change list options,
visit:
http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives at
http://archive.midrange.com/rpg400-l.
============================================================================
==
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.
midrange.com
