Re: embedded sql error

[Rpglist <rpglist@xxxxxxxxxxx>]

Thanks Eric I think that just did the trick 

Sent from my iPhone

On Nov 28, 2012, at 11:28 AM, "DeLong, Eric" <EDeLong@xxxxxxxxxxxxxxx> wrote:

> Well, you can't use the update syntax as you've specified.  Table B cannot be referenced for data values since it is specified in the exists clause.  Your syntax should be something more like this:
>
> 0033.47     Exec Sql
> 0033.48        Update ClpAdjRpt as a
> 0033.49        Set (a.xAjdFnd, a.xajdAmd, a.xAjdAmp, a.xAjdBal, a.xAjdTyp ) = 
>            (Select b.ajdfnd, b.ajdamd, b.ajdamp, b.ajdbal, b.ajdtyp
> 0033.55            from AdjDue as b
> 0033.56            Where
> 0033.57              b.ajdrcl = a.xwarcl and b.ajdrln = a.xwarln)
> 0033.52        Where exists
> 0033.53           (Select 1 from AdjDue as c
> 0033.56           Where
> 0033.57              c.ajdrcl = a.xwarcl and c.ajdrln = a.xwarln);
>
> -Eric DeLong
>
> -----Original Message-----
> From: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of rpglist@xxxxxxxxxxx
> Sent: Wednesday, November 28, 2012 9:59 AM
> To: rpg400-l@xxxxxxxxxxxx
> Subject: embedded sql error
>
> I am attempting to update one file CLPADJRPT (Only selected fields), from
> another file ADJDUE (again only selected fields) where two fields match,
> these are unique so its always a one to one comparison.
>
> I'm getting a -206 error but I'm not quite sure why.
>
> Here is the code:
>
> 0033.47     Exec Sql
> 0033.48        Update ClpAdjRpt as a
> 0033.49        Set a.xAjdFnd = b.AjdFnd, a.xajdAmd = b.AjdAmd,
> 0033.50            a.xAjdAmp = b.AjdAmp, a.xAjdBal = b.AjdBal,
> 0033.51            a.xAjdTyp = b.AjdTyp
> 0033.52        Where exists
> 0033.53           (Select b.ajdfnd, b.ajdamd, b.ajdamp, b.ajdbal,
> 0033.54                   b.ajdtyp
> 0033.55              from AdjDue as b
> 0033.56           Where
> 0033.57              b.ajdrcl = a.xwarcl and b.ajdrln = a.xwarln);
>
> When I attempted to run this via interactive SQL it bombs on the ajdfnd
> field...
>
> Any idea what I'm doing wrong here?
>
>
> -- 
> 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 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.