Re: CASE - WHEN ELSE ORACLE 7.3

From: Jurij Modic <jmodic_at_src.si>
Date: Mon, 30 Aug 1999 17:42:17 GMT
Message-ID: <37cec231.13976597_at_news.arnes.si>

On Mon, 30 Aug 1999 03:17:41 GMT, bcunn_at_cucomconsultants.com (Bob Cunningham) wrote:

>How does...
>
>UPDATE DISKS_LOCAL_SP DLS
>SET MAKEMODEL = (SELECT DECODE(INSTR(bios_type,'Toshiba')
> ,0,DECODE(INSTR(bios_type,'IBM')
>,0,DECODE(INSTR(bios_type,'COMPAQ')
> ,0,'CLONE'
> ,'COMPAQ')
> ,'IBM')
> ,'Toshiba'
> )
> FROM CFGM.nodes_local NL
> WHERE DLS.NODE_NAME = NL.NODE_NAME
> )
>WHERE DLS.MAKEMODEL IS NULL;
>
>...hit you?
>Pretty ugly?...me too...

As far as readability is concerned, there is no need to nest the DECODEs - far more elegant and readable code using only one DECODE would be:

UPDATE DISKS_LOCAL_SP DLS
SET MAKEMODEL =

  (SELECT DECODE(1, SIGN(INSTR(bios_type,'Toshiba')), 'Toshiba',
                    SIGN(INSTR(bios_type,'IBM')), 'IBM',
                    SIGN(INSTR(bios_type,'COMPAQ')), 'COMPAQ',
                   'CLONE')
      FROM CFGM.nodes_local NL
      WHERE DLS.NODE_NAME = NL.NODE_NAME

  )
WHERE DLS.MAKEMODEL IS NULL;
>so I'd just create a function that would
>accept a parameter (bios_type) and return a result of 'Toshiba',
>'IBM', 'COMPAQ' or 'CLONE'...kinda like...
>
[SNIP]
>...so I could write the update like...
>
>UPDATE DISKS_LOCAL_SP DLS
>SET MAKEMODEL = (SELECT f_bios_type(bios_type)
> FROM CFGM.nodes_local NL
> WHERE DLS.NODE_NAME = NL.NODE_NAME
> )
>WHERE DLS.MAKEMODEL IS NULL;
If readability/maintainability of the code is the prime factor, one should go with this user_defined_function method. However, if the speed of execution is of main concern and if there is a lot of records to be updated, the usage of user defined functions might slow the update down significantly as opposed to built-in functions.

Regards,
Jurij Modic <jmodic_at_src.si>
Certified Oracle DBA (7.3 & 8.0 OCP)

---

The above opinions are mine and do not represent any official standpoints of my employer Received on Mon Aug 30 1999 - 19:42:17 CEST