Re: declare cursor problem.

[Charles Wilt <charles.wilt@xxxxxxxxx>]

You can't pass a list in a single variable using static SQL.

you'd need to have
NOT IN (:val1, :val2, :var3,...)

Assuming austllist is loaded from a table, you'd be better served just
using a subselect.

Charles

Charles



On Thu, Jun 25, 2015 at 10:04 AM, Smith, Mike <Mike_Smith@xxxxxxxxxxxxxxxx>
wrote:

> I'm sure I'm missing something really simple here, but I just can't see it.
>
> I'm trying to compare authorization lists(last quarter to current)
>
> I'm doing a DSPAUTL in Cl
>
> SO   Auth List  Last quarter has   CMDQRY.
>         Auth List current I have removed CMDQRY
>
> I want to identify that CMDQRY has been removed.   Which works, but I also
> want to exclude CMDQRY when simply looking line by line for changes.
> Basically trying to prevent it showing up as a change in user as well as
> being removed.
>
>  code snippet.
>      EXEC SQL
>        Declare OLDCURS Cursor for
>        Select OANAME,OAUSR,OAOBJA, OAOPR,OAOMGT,OAEXS,
>               OAREAD,OAADD,OAUPD,OADLT
>        From  AUTLDETLM
>        where OANAME not in(:autllist);
>
>        Exec Sql
>        Open OLDCURS ;
>
>        Exec Sql
>        Fetch OLDCURS into :OANAME, :OAUSR,:OAOBJA,:OAOPR,
>                           :OAOMGT, :OAEXS, :OAREAD,:OAADD,:OAUPD,:OADLT;
>
>        Dow SQLSTATE = '00000' ;
>
> When I run the program, autllist contains 'CMDQRY'.  But still picks up in
> the fetch.
> If if copy the select to STRSQL it works correctly and 'CMDQRY' doesn't
> show up.
> The file AUTLDETLM only exists in my test library.
> It seems to be ignoring the where clause.
>
> Anybody see what I might be missing.
>
> Mike
>
>
>
> NOTICE: This message, including any attachment, is intended as a
> confidential and privileged communication. If you have received this
> message in error, or are not the named recipient(s), please immediately
> notify the sender and delete this message.
> --
> This is the RPG programming on the IBM i (AS/400 and iSeries) (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.