Re: embedded sql error

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.