RE: Doing a SET with variables

Wow! Answered my own question... It updated all records in the file.
WHERE here I come!!  :-( 

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Turnidge, Dave
Sent: Thursday, November 30, 2006 10:16 AM
To: RPG programming on the AS400 / iSeries
Subject: RE: Doing a SET with variables

Thank you. That is VERY helpful.

Next, regarding the WHERE clause. *IF* I have just "selected" the
record, and displayed it, and changed something on it, do I need a WHERE
clause? Won't I be updating the record I just retrieved?

Thanks again,

Dave 

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of rob@xxxxxxxxx
Sent: Thursday, November 30, 2006 10:06 AM
To: RPG programming on the AS400 / iSeries
Subject: Re: Doing a SET with variables

First, you are missing a WHERE clause.  You better have one.

Dynamic SQL is great, but can impact performance.  Be nice to see if you
could do something like the following (which I haven't tried) (sample is
V5R4)

exec sql Update $AUDITSTG/SAVHISTORY
case FileFieldValue<>:HostFieldValue
  Set FileFieldValue=:HostFieldValue
...
where ...;

But, if it doesn't and you continue to use "dynamic" SQL (suggested
search words or important concepts are double quoted) then you'll need
to do one of the following two solutions

"PREPARE" a statement
"EXECUTE" a statement

or
"EXECUTE IMMEDIATE" a statement

like

exec sql prepare stmt1 from :SelectTwo;
exec sql execute stmt1;

or

exec sql execute immediate :SelectTwo;


Rob Berendt

--
This is the RPG programming on the AS400 / 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.