Oracle-L: RE: Smart Update, How ?

From: Daemen, Remco <Remco.Daemen_at_werklinq.nl>
Date: Wed, 13 Mar 2002 02:23:24 -0800
Message-ID: <F001.00426B60.20020313022324@fatcity.com>

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
--------------------------------------------------------------------