Re: CASE - WHEN ELSE ORACLE 7.3

From: Bob Cunningham <bcunn_at_cucomconsultants.com>
Date: Mon, 30 Aug 1999 03:17:41 GMT
Message-ID: <37c9efef.84768847_at_news.telusplanet.net>

How does...

UPDATE DISKS_LOCAL_SP DLS
[Quoted] 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?

[Quoted] Pretty ugly?...me too...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...

CREATE OR REPLACE FUNCTION f_bios_type (in_bios_type IN VARCHAR2) RETURN VARCHAR2 IS
BEGIN
  IF in_bios_type LIKE '%Toshiba%' THEN

     RETURN 'Toshiba';
  ELSIF in_bios_type LIKE '%IBM%' THEN

     RETURN 'IBM';
  ELSIF in_bios_type LIKE '%COMPAQ%' THEN

     RETURN 'COMPAQ';
  ELSE
     RETURN 'CLONE';
  END IF;
END; ...so I could write the update like...

UPDATE DISKS_LOCAL_SP DLS
[Quoted] 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; DECODE is the pseudo-equivalent of a CASE statement and, used in conjunction with other built-in functions, can pretty well accomplish everything a CASE statement can achieve but sometimes at a distinct loss of readability (...er...maintainability?).

On Sun, 29 Aug 1999 21:31:02 +0100, "Craig Beevers" <cbeevers_at_easynet.co.uk> wrote:

>
>The example as requested.
>
>This example looks at data from a Inventory tool, if the make & model is not
>retrieved by the tool, I look in the Bios to see if I can find the
>Manufacturer. If I can't find a standard manufacturer I class the machine as
>a 'clone'.
>
>(This works in SQL Server 6.5. )
>
>---
>
>
>UPDATE DISKS_LOCAL_SP
>SET MAKEMODEL =  (SELECT DISTINCT makemodel =
>  CASE
>   WHEN bios_type like '%Toshiba%' then 'Toshiba'
>   WHEN bios_type like '%IBM%' then 'IBM'
>   WHEN bios_type like '%COMPAQ%' then 'COMPAQ'
>  ELSE    'CLONE'
>  END
>FROM CFGM.nodes_local
>WHERE NODE_NAME = NL.NODE_NAME
>)
>FROM CFGM.nodes_local NL , DISKS_LOCAL_SP DLS
>WHERE DLS.NODE_NAME = NL.NODE_NAME AND MAKEMODEL = ''
>
>> >Have you looked up the decode pseudo-function?
>
>> I've looked up the function list in the Oracle Online documentation- no
>sign of 'CASE'' or its equivalent. I have just referenced a generic SQL2
>reserved keyword list and 'CASE' is there.
>
>From the example above. what's the "Oracle way" to achieve the same results.
>?
>
>
>--
>Best Regards
>
>Craig Beevers
>
>Craig Beevers <cbeevers_at_easynet.co.uk> wrote in message
>news:7qbnih$18ic$1_at_quince.news.easynet.net...
>> Hi,
>>
>> Does Oracle 7.3 on NT support the CASE (When - then - else) statement ?
>>
>> I have tried it in selects & updates and I still have no luck . I have
>tried
>> the On-line help but cannot find a reference to it.
>>
>> If anyone has been successful in deploying the command, please could you
>> forward an example of the query structure.
>>
>> Many Thanks
>> --
>> Best Regards
>>
>> Craig Beevers
>> E-mail:  beevers_at_maincontrol.co.uk
>>
>>
>>
>
>

Received on Mon Aug 30 1999 - 05:17:41 CEST