RE: SQL and case structure

From: Jacques Kilchoer <Jacques.Kilchoer_at_quest.com>
Date: Mon, 07 Oct 2002 14:29:34 -0800
Message-ID: <F001.004E2D3D.20021007142934@fatcity.com>

> -----Original Message-----
> From: Droogendyk, Harry [mailto:Harry.Droogendyk_at_CIBC.com]
>
> I've used SAS's version of SQL and it allows the coding of
> conditional logic
> in the SELECT statement:
>
> proc sql;
> select acct_no,
> case substr(acct_no,16,1)
> when '1' then 'one'
> when '2' then 'two'
> else 'other'
> end as desc
> from star.kills;
> quit;
>
> The same syntax does not work in SQL*Plus for Oracle 8. Can
> someone point
> me to the correct syntax?
>
> Secondly, any URLs for this kind of information would be most
> appreciated.

The case syntax was introduced in Oracle 8.1. Here's a link to Oracle 8.0 documentation http://download-west.oracle.com/docs/cd/A64702_01/doc/index.htm (read the SQL Reference manual)

CASE expressions are described in the Oracle 8.1 Data Warehousing guide http://download-west.oracle.com/docs/cd/A87860_01/doc/index.htm

Click on Oracle8i Server and SQL*Plus and then on Data Warehousing guide: you will eventually find this:
http://download-west.oracle.com/docs/cd/A87860_01/doc/server.817/a76994/anal ysis.htm#18058

To do something similar to a CASE in Oracle 8.0 SQL, use the decode function.
Example:

if column = 1 return 10
if column = 2 return 20
if column = 3 return 40

otherwise return the value of the column

select

  decode (col,  -- expression
          1, 10, -- change 1 to 10
          2, 20, -- change 2 to 20
          3, 40, -- change 3 to 40
          col  -- default is the original value
         ) from table ;

or
select decode (col, 1, 10, 2, 20, 3, 40, col) from table ;

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jacques Kilchoer
  INET: Jacques.Kilchoer_at_quest.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
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 Mon Oct 07 2002 - 17:29:34 CDT