RE: SQL and case structure

From: Droogendyk, Harry <Harry.Droogendyk_at_CIBC.com>
Date: Mon, 07 Oct 2002 14:58:35 -0800
Message-ID: <F001.004E2DB2.20021007145835@fatcity.com>

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
Rich.Jesse_at_qtiworld.com              Quad/Tech International, Sussex, WI USA

> -----Original Message-----
> From: Droogendyk, Harry [mailto:Harry.Droogendyk_at_CIBC.com]
> 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: Rich.Jesse_at_qtiworld.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).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Droogendyk, Harry
  INET: Harry.Droogendyk_at_CIBC.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:58:35 CDT