RE: Using host variables in SQLRPGLE doesn't seem to be working.... IN clause

Another option is to build the entire select statement in a string variable. Prepare it and then declare your cursor over the prepared statement.

For example.

STRSQL = 'SELECT ' + ColumnsSQL + ' FROM DCR_PIVOT +
WHERE RNWORD1 in (' + %Trim(NameString) + ') +
ORDER BY ' + %TRIM(SortBy);
Exec SQL PREPARE S1 FROM :STRSQL;

Exec SQL DECLARE C1 CURSOR FOR S1;
Exec SQL OPEN C1;

Exec SQL Fetch C1 Into :ColumnsDS
DoW SQLCOD <> 100 And SQLCOD >= 0;
...
Exec SQL Fetch C1 Into :ColumnsDS
EndDo;

I'm assuming RNWORD1 is character so you will need to make sure that you have the single quotes around each name in your string.



-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Bryce Martin
Sent: Friday, January 07, 2011 5:08 PM
To: RPG programming on the IBM i / System i
Subject: Using host variables in SQLRPGLE doesn't seem to be working.... IN clause

I might be trying to be too fancy here, but I thought that maybe this
would work.

I have an array of values (words) that I need to search a particular field
for in a file.... So I thought that this might work...

Exec SQL
Declare NameSet Cursor For
Select Cast(rncust As Numeric(6,0)),
Cast(rnseq As Numeric(4,0)),
Cast(rntype As Numeric(2,0)),
rnword1, rnword2, rnword3, rnword4, rnword5
From rcm002pf
Where rnword1 in(:NameString)
and rntype = (case when :type<>'' then :NumType else rntype
End)
Order by rncust, rnseq;

I build NameString from the array values. Its a character string. Its
value is like... 'word1','word2','word3'

My thinking is that NameString would just plop right inside the IN()
clause and act as the list to check. But I keep getting and sql 100 error
(no results). If I put this into my favorite sql tool it works fine. So
I know that its something to do with the SQL. I thought this would be
cleaner than doing a Prepare, but maybe I'll have to do it anyway.

Any thoughts????


Thanks
Bryce Martin
Programmer/Analyst I
570-546-4777
--- This message (including any attachments) is intended only for the use of the individual or entity to which it is addressed and may contain information that is non-public, proprietary, privileged, confidential, and exempt from disclosure under applicable law. If you are not the intended recipient, you are hereby notified that any use, dissemination, distribution, or copying of this communication is strictly prohibited. If you have received this communication in error, please notify us and destroy this message immediately. ---