Can't you extract the update out of the cursor loop, and build a new
cursor just for this update, in which you exclude the rows with "XXX is
null" ? If you execute both loops, the result would be the same ...
-----Oorspronkelijk bericht-----
Van: Steven Joshua [mailto:wndyu_at_yahoo.com]
Verzonden: woensdag 13 maart 2002 15:53
Aan: Multiple recipients of list ORACLE-L
Onderwerp: RE: Smart Update, How ?
No, I can't do that. The whole row contains a lot of
other information for other updates.
Any other ideas?
Steven
- "Daemen, Remco" <Remco.Daemen_at_werklinq.nl> wrote:
> Hi Steven,
>
> Exclude the rows with "XXX is null" in the cursor
> definition. Does that
> get you what you want ?
>
> HTH, Remco
>
> -----Oorspronkelijk bericht-----
> Van: steven wndy [mailto:wndyu_at_yahoo.com]
> Verzonden: dinsdag 12 maart 2002 22:34
> Aan: Multiple recipients of list ORACLE-L
> Onderwerp: Smart Update, How ?
>
>
> Hello list:
>
> In my cursor loop, I have an update statement, see
> below. Now I need to the update only for the field
> (in
> that cursor) that has data in it. (which means when
> is
> null, that field will not be updated).
> - Dynamic SQL can do it. But now for many reasons
> that
> I can't use Dynamic SQL in this code. Hard for other
> people to maintain
> - And I believe use multiple "if XXX is not null
> then
> update ...", this is accomplishable. But that will
> be
> a lot of "if .. then ... update ..." statement.
>
> Is there a smarter way to do this update? I don't
> know
> if DECODE can be used here. at least I don't know
> how.
> Any ideas? Many Thanks
>
>
> UPDATE KOMP
> SET C_INVENTORY_DATE =
> DECODE(rec_UpdIES.PHYINVENTORYDATE, NULL, NULL,
> TO_DATE(rec_UpdIES.PHYINVENTORYDATE, 'YYYYMMDD')),
> KFLDC03 = RTRIM(rec_UpdIES.RESPONSIBLEDEPT),
> KFLDC24 = RTRIM(rec_UpdIES.COMPBILLCODE),
> KFLDA01 = DECODE(RTRIM(rec_UpdIES.DATEINSTALLED),
> NULL, NULL, TO_DATE(RTRIM(rec_UpdIES.DATEINSTALLED),
> 'YYYYMMDD')),
> KFLDA04 = DECODE(RTRIM(rec_UpdIES.DATERECEIVED),
> NULL,
> TO_DATE(TO_CHAR(SYSDATE, 'YYYYMMDD'), 'YYYYMMDD'),
> TO_DATE(RTRIM(rec_UpdIES.DATERECEIVED),
> 'YYYYMMDD')),
> KFLDC11 = RTRIM(rec_UpdIES.MAINTPONUMBER),
>
>
> KFLDC12 = RTRIM(rec_UpdIES.MAINTPOLINE),
>
>
> KFLDC13 = RTRIM(rec_UpdIES.MAINTRELEASENO),
>
>
> KFLDC14 = RTRIM(rec_UpdIES.MAINTVENDOR),
>
>
> GARANTBIS =
> TO_DATE(RTRIM(rec_UpdIES.WARRANTYENDDATE),
> 'YYYYMMDD'),
> C_BILLCODE = V_BILLCODE,
> ACQUISITIONMODE = rec_UpdIES.OWNERSHIPCODE,
> USERCHG = 'RECEIPT',
> DATCHG =
> TO_CHAR(SYSDATE,'YYYY-MM-DD-HH24.MI.SS')||'.000000'
>
>
> WHERE in_IDENT = IDENT;
>
>
> __________________________________________________
> Do You Yahoo!?
> Try FREE Yahoo! Mail - the world's greatest free
> email!
> http://mail.yahoo.com/
> --
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
> --
> Author: steven wndy
> INET: wndyu_at_yahoo.com
>
> Fat City Network Services -- (858) 538-5051 FAX:
> (858) 538-5051
> San Diego, California -- Public Internet
> access / Mailing Lists
>
> To REMOVE yourself from this mailing list, send an
> E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of
> 'ListGuru') and in
> the message BODY, include a line containing: UNSUB
> ORACLE-L
> (or the name of mailing list you want to be removed
> from). You may
> also send the HELP command for other information
> (like subscribing).
> --
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
> --
> Author: Daemen, Remco
> INET: Remco.Daemen_at_werklinq.nl
>
> Fat City Network Services -- (858) 538-5051 FAX:
> (858) 538-5051
> San Diego, California -- Public Internet
> access / Mailing Lists
>
> To REMOVE yourself from this mailing list, send an
> E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of
> 'ListGuru') and in
> the message BODY, include a line containing: UNSUB
> ORACLE-L
> (or the name of mailing list you want to be removed
> from). You may
> also send the HELP command for other information
> (like subscribing).
Do You Yahoo!?
Try FREE Yahoo! Mail - the world's greatest free email!
http://mail.yahoo.com/
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Steven Joshua
INET: wndyu_at_yahoo.com
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Daemen, Remco
INET: Remco.Daemen_at_werklinq.nl
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Received on Wed Mar 13 2002 - 09:43:28 CST
