> -----Original Message-----
> From: Droogendyk, Harry [mailto:[EMAIL PROTECTED]]
>
> 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/analysis.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 ;
