Neil,

Success at last! I had the tic marks out of whack. I have always had issues with
the tic marks going back to OpnQryF. 

Thanks again,

Steve

It's at the top of the code after the plist

      /Free
        Exec SQL set OPTION
          COMMIT = *NONE ;

        Exec SQL
         Declare CursorWrn Cursor
           For DynSqlStmt ;

      /End-Free

Ummmm....  Where's the DECLARE CURSOR?  (Am I just not seeing it?  Is it in 
code you didn't post?)

exec sql declare CursorWrn cursor for DynSqlStmt;

Neil,

Forgive me for bothering you again, but I have another problem now. After taking
care of the numeric parms my sql stmnt evals to the following in debug:

Select * From AllWrn Where WnCmit = 'SyCmit' And WnDlcd = 'D
lDlcd' And WnSmAd = 'CS_Yes' And WnEnYm >= 0906 And WnEnym <
= 1006 Order By WnCmit, WnDlcd, WnEnCy, WnEnYm  For Read Onl
y                                                           

Joblog during debug:
Cursor CURSORWRN opened.    
Row not found for CURSORWRN.

FetchNext does NOT return any rows. If I copy paste the select stmnt above into
Run Sql Scripts, it returns a table.

How can the SQL behave differently? Any more ideas?

Thanks again,

Steve

   PgSqlStmt = 'Select * '               +
               'From AllWrn '            +
               'Where WnCmit = '         +
               '''SyCmit'''              +
               ' And WnDlcd = '          +
               '''DlDlcd'''              +
               ' And WnSmAd = '          +
               '''CS_Yes'''              +
               ' And WnEnYm >= '         +
               %EditC( Lst12YyMm : 'X' ) +
               ' And WnEnym <= '         +
               %EditC( YearMonth : 'X' ) +
               ' Order By WnCmit, '      +
               'WnDlcd, '                +
               'WnEnCy, '                +
               'WnEnYm  '                +
               'For Read Only' ;

       Exec SQL
         Prepare DynSqlStmt From :PgSqlStmt ;

       Exec SQL
         Open CursorWrn ;

       DoW FetchNext() ;
           Do some stuff    
       EndDo

     P FetchNext       B
      *
     D FetchNext       PI              N
     D PgInd           S               N
      /Free
        Exec SQL
          Fetch Next
            From CursorWrn
            Into :DsWrn ;

            PgInd = MonitorSql() ;
        Return PgInd ;
      /End-Free
     P FetchNext       E
      ********************************************************************
     P MonitorSql      B
      *
     D MonitorSql      PI              N
      /Free
       Select ;
       When SqlState = SqlStateOk ;
            Return *On ;

       When SqlState = SqlStateNoRow ;
            Return *Off ;

       Other ;
            Return *Off ;
       Endsl ;
      /End-Free
     P MonitorSql      E

Neil,

I will give it a shot. By the way, I am old enough to have worked with OpnQryF.
It stunk!!!!!!

Thanks,

Steve

Neil,

I will give it a shot. By the way, I am old enough to have worked with OpnQryF.
It stunk!!!!!!

Thanks,

Steve

PgSqlStmt = 'Select * '             +
            'From AllWrn '          +
            'Where WnCmit = '''     +
            SyCmit                  +
            ''' And WnDlcd = '...

will give you the following string...
Select * from AllWrn Where WnCmit = '<value in SyCmit>' And Wndlcd = ...

If SyCmit is numeric then you need to eliminate the extra quote and convert it 
to a character representation, ie:
... 'Where WnCmit = '       +
    %editc(SyCmit:'3')      +
    ' And WnDlcd = '...

Ths will give you...
Select * from AllWrn Where WnCmit = <value in SyCmit> And Wndlcd = ...

I am doing much better, but now have a new problem. Getting the following error:

Field    HVR0004 and value 4 not compatible. Reason 7.
7 -- Value contains numeric data that is not valid.
Conversion error on host variable or parameter *N.    
Cursor CURSORWRN not open.          

I assume 'HVR004 is referring to field WnEnYm in the SQL stmnt below. Is my
assumption correct? I checked the values in the file for field WnEnym( S 4,0)
and all fields are numeric.

The sql stmnt will execute in Run Sql Scripts.

Any ideas?

Thanks
       PgSqlStmt = 'Select * '             +
                   'From AllWrn '          +
                   'Where WnCmit = '       +
                   '''SyCmit'''            +
                   ' And WnDlcd = '        +
                   '''DlDlcd'''            +
                   ' And WnSmAd = '        +
                   '''CS_Yes'''            +
                   ' And WnEnYm >= '       +
                   '''%Char(Lst12YyMm)'''  +
                   ' And WnEnym <= '       +
                   '''%Char(YearMonth)'''  +
                   ' Order By WnCmit, '    +
                   'WnDlcd, '              +
                   'WnEnCy, '              +
                   'WnEnYm  '              +
                   ' For Read Only' ;

It's very simple when you remember that...

"FileField = PgmField"
is not the same as
"FileField = <value in PgmField>"

Many people code the first when they mean the second.  It's one reason why 
(when I used to code OPNQRYF) I would split statements thus:

CHGVAR &SELECT value(... some big long complex select string ...)
OPNQRYF mylib/myfile select(&SELECT)

It made it VERY easy to run debug and look at the value of &SELECT.  Typically 
I'd missed a quote somewhere, or displaced it to the wrong side of the *EQ.

Doug, Neil and Bob,

Thanks for your all your help, the answer is so simple. How I ever thought I
could pass one big string is beyond me.

Regards,

Steve

And anyone who is old enough to have used OPNQRYF would also be aware of such 
issues.  As Bob demonstrates below, for string values.  For numerics and dates 
you would need to do something like...

' and X3 = ' + %editc(x3:'3');
' and X4 = "' + %char(x4:*iso) + '"';


You need to convert numerics and dates into character representations.

lotusstv,

Both Doug and Neil gave you good solutions.
I want to reiterate something that Doug said; Just about every programmer trying
something new (self included) bring their historical experiences with them.
Unfortunately most technologies are created in a vacuum and seldom carry over
the methods of previous technology in their entirety. 

Having said that, its a common situation where a new embedded SQL programmer
needs a dynamic sql statement or portion thereof, and does exactly what you did
in your code: embedded the RPG variable names ("host variable names") in a
quoted character string. But if you think about it, a quoted value, even if it
is a variable name, is still a quoted variable value. So there is no way for RPG
or anything else to extract the value of that field. If there were, TFisher's
fantasy feature for RPG IV of being able to store one field name inside of
another field and then retrieve the value of the inner field name, would be a
reality. But it is not. 

So remember, when building a quoted string, whatever you want in that string
(the customer number, or special codes or flag values) must be explicitly inserted. 

That is this:

D name        S            20A  Inz('Bob')
 /free
     myVar = 'Hello NAME';

is not the same as:

   myVar = 'Hello ' + NAME;

Therefore,
   mySQLStmt = 'SELECT fld1, fld2, fld2 FROM CUSTMAST WHERE X1=:X1 and X2=:X2';

Is not the same as:

   mySQLStmt = 'SELECT fld1, fld2, fld2 FROM CUSTMAST WHERE X1="' +
                   X1 + '" and X2="' + X2 + '"';

and a comment:
I've taken to using the fetch procedure as part of the do loop....

dow FetchNext();
  //  stuff here
enddo;

p FetchNext     b
d               pi              n
 /free
    exec sql fetch next from cursor into :variable;

    return MonitorSql();
 /end-free
p FetchNext     e

p MonitorSql    b
d               pi              n
 /free
   if SqlState = SqlStateOK;
     return *on;
   elseif SqlState = SqlStateNoRow;
     return *off;
   else;
     return *off;
   endif;
 /end-free
p MonitorSql   e

Alternatively:
Select * From AllWrn 
         Where WnCmit = ? And
               WnDlcd = ? And
               WnSmAd = ? And
               WnEnYm >= ? And
               WnEnym <= ?
         Order By WnCmit, WnDlcd, WnEnCy, WnEnYm
         For Read Only' ;

Then later you do:
Open CursorWrn using :SyCmit, :DlDlcd, :CS_Yes, :Lst12YyMm, :YearMonth 

(at least I think you do the USING on the OPEN, only used this once and got 
told we don't do that here, so I don't have a ready example).

Your SQL statement is syntactically incorrect.  You do not use host variables
the same way with dynamic SQL.  Just put the values directly into the
statement.  For example...

PgSqlStmt = 'Select * '                   +
               'From AllWrn '                +
               'Where WnCmit = ''' + %trimr(SyCmit) + ''' And ' +
               'WnDlcd = ''' + %trimr(DlDlcd) + ''' And ' +
                 |
               'WnEnym <= ' + %char(YearMonth) + 
               <and so on>

Use quotes and %trim on alpha fields, and %char on numeric fields.

Also, I like to populate the statement, then PREPARE, then DECLARE, then OPEN.

I am new to dynamic sql cursors and have a problem where i get SqlState = 24501(
cursor not open ). Is my Sql stmnt bad or is there something going on that i
don't understand? The code below is inside of a larger loop where field DlDlcd
will change. The sql stmnt will run numerous times. It fails on the first time thru.

Any help would be greatly appreciated. 

Thanks,

Steve

PgSqlStmnt in debug has the following value:
        ....5...10...15...20...25...30...35...40...45...50...55...60 
  1   'Select * From AllWrn Where WnCmit = :SyCmit And WnDlcd = :Dl'
 61   'Dlcd       And WnSmAd = :CS_Yes       And WnEnYm >= :Lst12Yy'
121   'Mm   And WnEnym <= :YearMonth Order By WnCmit, WnDlcd, WnEnC'
181   'y, WnEnYm   For Read Only                                   '
241   '                                                            '
301   '                                                            '
361   '                                                            '
421   '                                                            '
481   '                                '                            

My RPG code is below, any help would be greatly appreciated.

/Free
   Exec SQL set OPTION
        COMMIT = *NONE ;

   Exec SQL
        Declare CursorWrn Cursor
        For PgDynSqlStmt ;

   PgSqlStmt = 'Select * '                   +
               'From AllWrn '                +
               'Where WnCmit = :SyCmit And ' +
               'WnDlcd = :DlDlcd       And ' +
               'WnSmAd = :CS_Yes       And ' +
               'WnEnYm >= :Lst12YyMm   And ' +
               'WnEnym <= :YearMonth '       +
               'Order By WnCmit, '           +
               'WnDlcd, '                    +
               'WnEnCy, '                    +
               'WnEnYm  '                    +
               ' For Read Only' ;

   Exec SQL
        Prepare PgDynSqlStmnt From :PgSqlStmt ;

   Exec SQL
        Open CursorWrn ;

   FetchNext() ;

   DoW @MoreRows = CS_Yes ;
       Do some processing if rec fnd
       FetchNext()       
   EndDo

   ********************************************************

     P FetchNext       B
      /Free
        Exec SQL
          Fetch Next
            From CursorWrn
            Into :DsWrn ;

        MonitorSql();
      /End-Free
     P FetchNext       E
      ********************************************************************
     P MonitorSql      B
      /Free
       Select ;
       When SqlState = SqlStateOk ;
            @MoreRows = CS_Yes ;

       When SqlState = SqlStateNoRow ;
            @MoreRows = CS_No ;

       Other ;
            @MoreRows = CS_No ;
       Endsl ;
      /End-Free
     P MonitorSql      E

/End-Free