Jesse:
Can I also use the value of another column rather than a literal like my
example coded?
e.g.
select acct_no,
decode(substr(acct_no,16,1),'1',field1
'2',field2
field3 ) as descr
from star.kills
;
TIA
-----Original Message-----
Sent: Monday, October 07, 2002 5:28 PM
To: Multiple recipients of list ORACLE-L
"CASE" in PL/SQL serves a slightly different function, but DECODE should do
the trick. Try:
select acct_no,
DECODE(substr(acct_no,16,1),
'1','one',
'2','two',
'other') as DESCR
from star.kills;
I also changed "desc" to "descr", since "desc" is a reserved word.
HTH! GL! :)
Rich Jesse System/Database Administrator
[EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA
> -----Original Message-----
> From: Droogendyk, Harry [mailto:[EMAIL PROTECTED]]
> Sent: Monday, October 07, 2002 3:39 PM
> To: Multiple recipients of list ORACLE-L
> Subject: SQL and case structure
>
>
> Listers:
>
> 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.
>
> Regards,
> Harry
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Jesse, Rich
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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: Droogendyk, Harry
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
