RE: SQL All fields update

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Alan Campin
Sent: Thursday, October 11, 2007 8:34 PM
To: rpg400-l@xxxxxxxxxxxx
Subject: SQL All fields update

I have a decision support table that has arrays of fields
that I want to update some of the data and not others.

I have a data structure to maps to the table.

d LEADINDDS ds
d CDATE 7P 0
d CTIME 6P 0
d LCYTM 7P 2 DIM(13) INZ(0)
d LCY1M 7P 2 DIM(13) INZ(0)
d LCY2M 7P 2 DIM(13) INZ(0)
d LACR 5P 2 DIM(12) INZ(0)
d LICR 5P 2 DIM(12) INZ(0)
d LOCR 5P 2 DIM(12) INZ(0)
d LMCR 5P 2 DIM(13) INZ(0)
d LM1CR 5P 2 DIM(13) INZ(0)
d LM2CR 5P 2 DIM(13) INZ(0)
d LONT 9P 2 DIM(13) INZ(0)
d LACP 9P 2 DIM(13) INZ(0)
d LPRB 9P 2 DIM(13) INZ(0)
d LVARH 5P 2 DIM(13) INZ(0)
d LSCR 5P 2 DIM(13) INZ(0)
d LRWR 5P 2 DIM(13) INZ(0)
d LSHP 5P 2 DIM(13) INZ(0)
d LEMF 5P 2 DIM(13) INZ(0)
d LPLN 5P 2 DIM(13) INZ(0)
d LPL 11P 2 DIM(13) INZ(0)

Is it possible to update all fields using an Update Where
Current of statement?

Exec Sql Declare C1 Cursor For
Select *
From LEADIND;
For Update;

Exec Sql Open C1;

Exec Sql Fetch Next From C1 Into :LEADINDDS;

// Do my updating.

Exec Sql Update LEADIND
Set Row = :LEADINDDS
Where Current Of C1;

The compile fails with update statement.

Don't think that's supported. The syntax diagram for the UPDATE statement shows:
ROW = ( exp1, exp2, ...)
NULL
DEFAULT
row-fullsubselect

So you could do:
ROW = (:CDATE, :CTIME, :LCYTM, ...)

I found an example online of

Exec Sql Update LEADIND
Set Row = :LEADINDDS
Where CRDATE = 1071001;

If I wanted to a positioned update of all fields, how would I do it?

Also, can you do a select into with all fields? I keep
getting a compile error.

Exec Sql Select *
Into :LEADINDDS
From LEADIND;

No.

You can only do a SELECT INTO when the results set contains only one row. However, I would expect the
above to return a runtime error not a compile error.

What is the error you are getting?


Lastly, remember my motto:

In general, if you're using a cursor in embbeded SQL, chances are you're doing something wrong.

Now, I grant you that depending on the processing you need to do to calculate the new values, a cursor
might be the best way. But consider for example, a user defined table function called NEWVALUES

Exec SQL
update LEADIND
set ROW = (select *
from TABLE(NEWVALUES()) as newVals
where leadind.key = newVals.key);


HTH,
Charles


This e-mail transmission contains information that is intended to be confidential and privileged. If you receive this e-mail and you are not a named addressee you are hereby notified that you are not authorized to read, print, retain, copy or disseminate this communication without the consent of the sender and that doing so is prohibited and may be unlawful. Please reply to the message immediately by informing the sender that the message was misdirected. After replying, please delete and otherwise erase it and any attachments from your computer system. Your assistance in correcting this error is appreciated.