AW: SQL fetch problem - skipping records

Hi,

1. Did you check your Statement W_SQL with the debugger?
Have you copied, pasted and executed W_SQL with interactive SQL?
Returns the interactive SQL the result you want?


2. If you prepare your SQL Statement completely (like in your example), the
variables specified in the Open clause will not be used.
3. If you prepare your SQL Statement completely and pass either character or
date variables, those variables must be surrounded by quotes.

4. If you only want to prepare your SQL statement once and specify the
variables to use in the open statement you have to work with parameter
markers (?).
When working with parameter markers character or date variables must not
be surrounded by quotes.

C Eval W_Sql='Select * From F0311 where ? = ?' +
C ' Order by RPDIVJ, RPDOC'

C/Exec SQL
C+ Prepare @S1 from :W_Sql
C/End-Exec
C/Exec SQL
C+ Declare CsrF0311 Cursor For @S1
C/End-Exec
C/Exec SQL Open CsrF0311 USING :W_Field, :W_Value
C/End-Exec

5. I'm not quite sure if field names can be used in combination with
parameter markers.

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 loosing them? Not training them
and keeping them!"


-----Ursprüngliche Nachricht-----
Von: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx] Im
Auftrag von Donna Fabianke
Gesendet: Friday, 03. October 2008 16:03
An: rpg400-l@xxxxxxxxxxxx
Betreff: FW: SQL fetch problem - skipping records



I am creating a cursor on a file and then doing a fetch next to load a
subfile.
The problem I am having is that the fetch seems to be skipping records.
I ran this in debug and for example I have more than 1 record for a
particular invoice. So, if invoice 1 with a date of 1/09/08 for both
records, the fetch gets the first record but then it goes onto the next
date (which is in the order by clause) - the date is rpdivj and the
invoice number is rpdoc.
I appreciate any advice!

C Eval W_Sql='Select * From F0311 where ' +
C W_Field + '=' + W_Value +
C ' Order by RPDIVJ, RPDOC'
C/Exec SQL
C+ Prepare @S1 from :W_Sql
C/End-Exec
C/Exec SQL
C+ Declare CsrF0311 Cursor For @S1
C/End-Exec
C/Exec SQL Open CsrF0311 USING :W_Field, :W_Value
C/End-Exec


Fetch statements:
C/Exec SQl
C+ Fetch From CsrF0311
C+ Into :DS0311
C/End-Exec

Thanks,

Donna Fabianke
McLane Company
Sr. Programmer/Analyst
254-771-7094
Donna.Fabianke@xxxxxxxxxxxx <mailto:Donna.Fabianke@xxxxxxxxxxxx>