I can use NVL function.
update komp a
set field1 =NVL(rec.xxx, a.field1)
...
Thanks for all the replies
- "Daemen, Remco" <Remco.Daemen_at_werklinq.nl> wrote:
> 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).
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).
Received on Wed Mar 13 2002 - 11:53:31 CST
