Re: decode

[Stephane Faroult]

Imran Ashraf wrote:
> 
> Hi,
> 
> I have this query...
> select decode(length(sn),8),substr(sn,-7),sn )from .
> 
> However i want to change it so that if the length of sn is >= 8 then set sn
> to last 7 characters.
> 
> How can i implement this?
> 
> Regards
> --
 
   decode(sign(length(sn) - 8, 1, ..., 0, ..., sn)

-- 
Regards,

Stephane Faroult
Oriole Software
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Stephane Faroult
  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).

RE: decode

[Igor Neyman]

decode(sign(8 - length(sn)), -1, substr(sn,-7), sn )


Igor Neyman, OCP DBA
[EMAIL PROTECTED]



-Original Message-
Imran Ashraf
Sent: Wednesday, September 03, 2003 1:00 PM
To: Multiple recipients of list ORACLE-L

Hi,

I have this query...
select decode(length(sn),8),substr(sn,-7),sn )from .

However i want to change it so that if the length of sn is >= 8 then set
sn
to last 7 characters.

How can i implement this?

Regards
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Imran Ashraf
  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.net
-- 
Author: Igor Neyman
  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).

RE: decode

[Stephane Paquette]

Use greatest ...

decode(greatest(sn,7),sn, substr(sn,-7),sn )


Stephane Paquette
Administrateur de bases de donnees
Database Administrator
Standard Life
www.standardlife.ca
Tel. (514) 499-7999 7470 and (514) 925-7187
[EMAIL PROTECTED] 



-Original Message-
Imran Ashraf
Sent: Wednesday, September 03, 2003 2:00 PM
To: Multiple recipients of list ORACLE-L


Hi,

I have this query...
select decode(length(sn),8),substr(sn,-7),sn )from .

However i want to change it so that if the length of sn is >= 8 then set sn
to last 7 characters.

How can i implement this?

Regards
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Imran Ashraf
  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.net
-- 
Author: Stephane Paquette
  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).

RE: DECODE or not to DECODE

[Denham Eva]

Thanks To all for the support!
I managed to use a combination of thesuggestions which worked great!

Denham

-Original Message-
Sent: Monday, August 04, 2003 4:24 PM
To: Multiple recipients of list ORACLE-L


Hello Listers,

I need some help please,
I am trying to create a DECODE statement, on a date column and looks
something like this, but does not work.
SELECT   COL1,
COL2,
DECODE((SYSDATE - COL_DATE), <= 30, '30_days', NULL) Days30,
DECODE((SYSDATE - COL_DATE), (between 31 and 60), '60 Days',
NULL) Days60,
..
..
FROM ACCOUNT_TBL
WHERE COL = '0'

Obviously, I have worked out that this can not be done. The problem is also
that the server is 7.3.4 and I need to use the code in a view.
Does anyone have any solutions/work arounds for this?
Any help will be appraciated.

TIA
Denham Eva
Oracle DBA


_
This e-mail message has been scanned for Viruses and Content and cleared 
by MailMarshal

For more information please visit www.marshalsoftware.com

_


#
Note:
This message is for the named person's use only.  It may contain
confidential,
proprietary or legally privileged information.  No confidentiality or
privilege
is waived or lost by any mistransmission.  If you receive this message in
error,
please immediately delete it and all copies of it from your system, destroy
any
hard copies of it and notify the sender.  You must not, directly or
indirectly,
use, disclose, distribute, print, or copy any part of this message if you
are not
the intended recipient. TFMC and any of its subsidiaries each reserve
the right to monitor all e-mail communications through its networks.

Any views expressed in this message are those of the individual sender,
except where
the message states otherwise and the sender is authorized to state them to
be the
views of any such entity.

Thank You.
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Denham Eva
  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).


_
This e-mail message has been scanned for Viruses and Content and cleared 
by MailMarshal

For more information please visit www.marshalsoftware.com

_

_
This e-mail message has been scanned for Viruses and Content and cleared 
by MailMarshal

For more information please visit www.marshalsoftware.com
_

#
Note:
This message is for the named person's use only.  It may contain confidential,
proprietary or legally privileged information.  No confidentiality or privilege
is waived or lost by any mistransmission.  If you receive this message in error,
please immediately delete it and all copies of it from your system, destroy any
hard copies of it and notify the sender.  You must not, directly or indirectly,
use, disclose, distribute, print, or copy any part of this message if you are not
the intended recipient. TFMC and any of its subsidiaries each reserve
the right to monitor all e-mail communications through its networks.

Any views expressed in this message are those of the individual sender, except where
the message states otherwise and the sender is authorized to state them to be the
views of any such entity.

Thank You.
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Denham Eva
  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).

RE: DECODE or not to DECODE

[Lord David]

Hmm.  I think this might be better

decode(sign(sysdate - col_date - 31), -1, '30_days', NULL) Days30,
decode(sign(sysdate - col_date - 31), -1, NULL, decode(sign(sysdate -
col_date - 61), -1, '60_days', NULL)) Days60

> -Original Message-
> From: Lord David [mailto:[EMAIL PROTECTED]
> Sent: 04 August 2003 15:39
> To: Multiple recipients of list ORACLE-L
> Subject: RE: DECODE or not to DECODE
> 
> 
> One trick is to use the sign() function...
> 
>   decode(sign(30 - sysdate + col_date), 1, '30_days', NULL),
> 
> Regards
> David Lord
> 
> > -Original Message-
> > From: Denham Eva [mailto:[EMAIL PROTECTED]
> > Sent: 04 August 2003 15:24
> > To: Multiple recipients of list ORACLE-L
> > Subject: DECODE or not to DECODE
> > 
> > 
> > Hello Listers,
> > 
> > I need some help please,
> > I am trying to create a DECODE statement, on a date column and looks
> > something like this, but does not work.
> > SELECT   COL1,
> > COL2,
> > DECODE((SYSDATE - COL_DATE), <= 30, 
> > '30_days', NULL) Days30,
> > DECODE((SYSDATE - COL_DATE), (between 31 and 
> > 60), '60 Days',
> > NULL) Days60,
> > ...
> > ...
> > FROM ACCOUNT_TBL
> > WHERE COL = '0'
> > 
> > Obviously, I have worked out that this can not be done. The 
> > problem is also
> > that the server is 7.3.4 and I need to use the code in a view.
> > Does anyone have any solutions/work arounds for this?
> > Any help will be appraciated.
> > 
> > TIA
> > Denham Eva
> > Oracle DBA
> > 
> > __
> > ___
> > This e-mail message has been scanned for Viruses and Content 
> > and cleared 
> > by MailMarshal
> > 
> > For more information please visit www.marshalsoftware.com
> > __
> > ___
> > 
> > ##
> > ###
> > Note:
> > This message is for the named person's use only.  It may 
> > contain confidential,
> > proprietary or legally privileged information.  No 
> > confidentiality or privilege
> > is waived or lost by any mistransmission.  If you receive 
> > this message in error,
> > please immediately delete it and all copies of it from your 
> > system, destroy any
> > hard copies of it and notify the sender.  You must not, 
> > directly or indirectly,
> > use, disclose, distribute, print, or copy any part of this 
> > message if you are not
> > the intended recipient. TFMC and any of its subsidiaries 
> each reserve
> > the right to monitor all e-mail communications through its networks.
> > 
> > Any views expressed in this message are those of the 
> > individual sender, except where
> > the message states otherwise and the sender is authorized to 
> > state them to be the
> > views of any such entity.
> > 
> > Thank You.
> > -- 
> > Please see the official ORACLE-L FAQ: http://www.orafaq.net
> > -- 
> > Author: Denham Eva
> >   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).
> > 
> 
> 
> *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** 
> *** *** *** 
>  This e-mail and its attachments are intended for the
>  author's addressee only and may be confidential. 
> 
>  If they have come to you in error you must take no 
>  action based on them, nor must you copy or show 
>  them to anyone; please reply to this e-mail and  
>  highlight the error. 
> 
>  Please note that this e-mail has been created in the
>  knowledge that Internet e-mail is not a 100% secure 
>  communications medium. We advise that you 
>  understand and observe this lack of security when 
>  e-mailing us. Steps have been 

RE: DECODE or not to DECODE

[Carol Bristow]

Eva - 

I dredged this out of some ancient code that I wrote against a 7.2
database.  The inner trunc functions were there to get rid of the time -
I don't remember why the outer trunc function was there at this point
(it was five years ago).  This doesn't get you completely there, but
will hopefully get you started.

Decode
(sign(30-(trunc(trunc(SysDate)-trunc(col_date,
1,'30 days',null)



Carol Bristow
DPRA Inc.
1300 N 17th St Suite 950
Rosslyn, VA 22209
Work: 703-841-8025
Fax: 703-524-9415

-Original Message-
Sent: Monday, August 04, 2003 10:24 AM
To: Multiple recipients of list ORACLE-L

Hello Listers,

I need some help please,
I am trying to create a DECODE statement, on a date column and looks
something like this, but does not work.
SELECT   COL1,
COL2,
DECODE((SYSDATE - COL_DATE), <= 30, '30_days', NULL)
Days30,
DECODE((SYSDATE - COL_DATE), (between 31 and 60), '60
Days',
NULL) Days60,
...
...
FROM ACCOUNT_TBL
WHERE COL = '0'

Obviously, I have worked out that this can not be done. The problem is
also
that the server is 7.3.4 and I need to use the code in a view.
Does anyone have any solutions/work arounds for this?
Any help will be appraciated.

TIA
Denham Eva
Oracle DBA


_
This e-mail message has been scanned for Viruses and Content and cleared

by MailMarshal

For more information please visit www.marshalsoftware.com

_


#
Note:
This message is for the named person's use only.  It may contain
confidential,
proprietary or legally privileged information.  No confidentiality or
privilege
is waived or lost by any mistransmission.  If you receive this message
in error,
please immediately delete it and all copies of it from your system,
destroy any
hard copies of it and notify the sender.  You must not, directly or
indirectly,
use, disclose, distribute, print, or copy any part of this message if
you are not
the intended recipient. TFMC and any of its subsidiaries each reserve
the right to monitor all e-mail communications through its networks.

Any views expressed in this message are those of the individual sender,
except where
the message states otherwise and the sender is authorized to state them
to be the
views of any such entity.

Thank You.
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Denham Eva
  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.net
-- 
Author: Carol Bristow
  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).

Re: DECODE or not to DECODE

[Jonathan Gennick]

One possibility that comes to mind is to write a really,
really long DECODE:

DECODE((SYSDATE - COL_DATE), 1, '30_days',
   2, '30_days', 3, '30_days', ... NULL) Days30

But there must be a better possibility. Perhaps use the SIGN
function:

DECODE ( SIGN((SYSDATE - COL_DATE)-30),
   -1, '30_DAYS', 0, '30_DAYS', NULL)

If the difference is 1-29, subtracting 30 will result in a
negative value, and SIGN will return -1. If the difference
is exactly 30, the subtraction will result in 0, and SIGN
will return 0. The only other possible return is 1, for
positive numbers, and I let the default (NULL) handle that
case.

My guess is that you could do something similar for your
second DECODE statement, though the math would get a bit
more complex. I'd have to think a bit longer to work up a
solution, but I'm optimistic that there is one.

Hope this helps.

Best regards,

Jonathan Gennick --- Brighten the corner where you are
http://Gennick.com * 906.387.1698 * mailto:[EMAIL PROTECTED]

Join the Oracle-article list and receive one
article on Oracle technologies per month by 
email. To join, visit http://four.pairlist.net/mailman/listinfo/oracle-article, 
or send email to [EMAIL PROTECTED] and 
include the word "subscribe" in either the subject or body.


Monday, August 4, 2003, 10:24:22 AM, you wrote:
DE> Hello Listers,

DE> I need some help please,
DE> I am trying to create a DECODE statement, on a date column and looks
DE> something like this, but does not work.
DE> SELECT   COL1,
DE> COL2,
DE> DECODE((SYSDATE - COL_DATE), <= 30, '30_days', NULL) Days30,
DE> DECODE((SYSDATE - COL_DATE), (between 31 and 60), '60 Days',
DE> NULL) Days60,
DE> ...
DE> ...
DE> FROM ACCOUNT_TBL
DE> WHERE COL = '0'

DE> Obviously, I have worked out that this can not be done. The problem is also
DE> that the server is 7.3.4 and I need to use the code in a view.
DE> Does anyone have any solutions/work arounds for this?
DE> Any help will be appraciated.

DE> TIA
DE> Denham Eva
DE> Oracle DBA

DE> 
_
DE> This e-mail message has been scanned for Viruses and Content and cleared 
DE> by MailMarshal

DE> For more information please visit www.marshalsoftware.com
DE> 
_

DE> 
#
DE> Note:
DE> This message is for the named person's use only.  It may contain confidential,
DE> proprietary or legally privileged information.  No confidentiality or privilege
DE> is waived or lost by any mistransmission.  If you receive this message in error,
DE> please immediately delete it and all copies of it from your system, destroy any
DE> hard copies of it and notify the sender.  You must not, directly or indirectly,
DE> use, disclose, distribute, print, or copy any part of this message if you are not
DE> the intended recipient. TFMC and any of its subsidiaries each reserve
DE> the right to monitor all e-mail communications through its networks.

DE> Any views expressed in this message are those of the individual sender, except 
where
DE> the message states otherwise and the sender is authorized to state them to be the
DE> views of any such entity.

DE> Thank You.
DE> -- 
DE> Please see the official ORACLE-L FAQ: http://www.orafaq.net

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jonathan Gennick
  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).

RE: DECODE or not to DECODE

[Lord David]

One trick is to use the sign() function...

  decode(sign(30 - sysdate + col_date), 1, '30_days', NULL),

Regards
David Lord

> -Original Message-
> From: Denham Eva [mailto:[EMAIL PROTECTED]
> Sent: 04 August 2003 15:24
> To: Multiple recipients of list ORACLE-L
> Subject: DECODE or not to DECODE
> 
> 
> Hello Listers,
> 
> I need some help please,
> I am trying to create a DECODE statement, on a date column and looks
> something like this, but does not work.
> SELECT   COL1,
> COL2,
> DECODE((SYSDATE - COL_DATE), <= 30, 
> '30_days', NULL) Days30,
> DECODE((SYSDATE - COL_DATE), (between 31 and 
> 60), '60 Days',
> NULL) Days60,
> ...
> ...
> FROM ACCOUNT_TBL
> WHERE COL = '0'
> 
> Obviously, I have worked out that this can not be done. The 
> problem is also
> that the server is 7.3.4 and I need to use the code in a view.
> Does anyone have any solutions/work arounds for this?
> Any help will be appraciated.
> 
> TIA
> Denham Eva
> Oracle DBA
> 
> __
> ___
> This e-mail message has been scanned for Viruses and Content 
> and cleared 
> by MailMarshal
> 
> For more information please visit www.marshalsoftware.com
> __
> ___
> 
> ##
> ###
> Note:
> This message is for the named person's use only.  It may 
> contain confidential,
> proprietary or legally privileged information.  No 
> confidentiality or privilege
> is waived or lost by any mistransmission.  If you receive 
> this message in error,
> please immediately delete it and all copies of it from your 
> system, destroy any
> hard copies of it and notify the sender.  You must not, 
> directly or indirectly,
> use, disclose, distribute, print, or copy any part of this 
> message if you are not
> the intended recipient. TFMC and any of its subsidiaries each reserve
> the right to monitor all e-mail communications through its networks.
> 
> Any views expressed in this message are those of the 
> individual sender, except where
> the message states otherwise and the sender is authorized to 
> state them to be the
> views of any such entity.
> 
> Thank You.
> -- 
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> -- 
> Author: Denham Eva
>   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).
> 


*** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** 
 This e-mail and its attachments are intended for the
 author's addressee only and may be confidential. 

 If they have come to you in error you must take no 
 action based on them, nor must you copy or show 
 them to anyone; please reply to this e-mail and  
 highlight the error. 

 Please note that this e-mail has been created in the
 knowledge that Internet e-mail is not a 100% secure 
 communications medium. We advise that you 
 understand and observe this lack of security when 
 e-mailing us. Steps have been taken to ensure this 
 e-mail and attachments are free from any virus, but 
 advise the recipient to ensure they are actually virus 
 free. 

 The views, opinions and judgments expressed in this 
 message are solely those of the author. The message 
 contents have not been reviewed or approved by Iron 
 Mountain.

*** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** ***
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Lord David
  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).

Re: DECODE AND SELECT

[Chip]

Oracle 8i (8.1.6+) PL/SQL can use execute immediate for
SQL with searched CASE expressions.
Have Fun :)

Nuno Souto wrote:

- Original Message - 

 

I am in urgent need of backporting oracle9i pl/sql to
oracle8i.I have encountered some case satements like
this
CASE 
when x>0 then (select y from deptno)
else
(select Z from emp)
end

i need to convert them to decode statements.Can
anybody tell me how to write a select statement within
decode.
   

You already got some suggestions.  But if you're 
backporting from 9i to 8i, don't bother:
8i supports CASE happily.  At least in SQL...

Cheers
Nuno Souto
[EMAIL PROTECTED]
 



--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Chip
 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).

Re: DECODE AND SELECT

[Nuno Souto]

- Original Message - 

> I am in urgent need of backporting oracle9i pl/sql to
> oracle8i.I have encountered some case satements like
> this
> 
> CASE 
> when x>0 then (select y from deptno)
> else
> (select Z from emp)
> end
> 
> i need to convert them to decode statements.Can
> anybody tell me how to write a select statement within
> decode.

You already got some suggestions.  But if you're 
backporting from 9i to 8i, don't bother:
8i supports CASE happily.  At least in SQL...

Cheers
Nuno Souto
[EMAIL PROTECTED]

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Nuno Souto
  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).

RE: DECODE AND SELECT

[Kevin Lange]

 One thing you could consider is to make functions out of each of the sql
statements in the case.  Then, in your decode statement you could call the
correct function based on the value of X.  Either that or put the entire
logic into a function that has all the case statement broken down into
individual IF tests .

-Original Message-
To: Multiple recipients of list ORACLE-L
Sent: 7/11/03 11:39 PM

Hi

I am in urgent need of backporting oracle9i pl/sql to
oracle8i.I have encountered some case satements like
this

CASE 
when x>0 then (select y from deptno)
else
(select Z from emp)
end

i need to convert them to decode statements.Can
anybody tell me how to write a select statement within
decode.

regards
Hrishy


Want to chat instantly with your online friends?  Get the FREE Yahoo!
Messenger http://uk.messenger.yahoo.com/
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: =?iso-8859-1?q?hrishy?=
  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.net
-- 
Author: Kevin Lange
  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).

Re: DECODE AND SELECT

[Mladen Gogala]

You are lucky! My sinuses are getting better. Decode is
a function which is to be called within a SQL statement,
not the other way round. What do you translate a "CASE"
statement into? That's elementary, my dear Hrishy: a sequence
of "if ... then ... else ... end if" commands.
On 2003.07.12 00:39, hrishy wrote:
Hi

I am in urgent need of backporting oracle9i pl/sql to
oracle8i.I have encountered some case satements like
this
CASE
when x>0 then (select y from deptno)
else
(select Z from emp)
end
i need to convert them to decode statements.Can
anybody tell me how to write a select statement within
decode.
regards
Hrishy

Want to chat instantly with your online friends?  Get the FREE Yahoo!
Messenger http://uk.messenger.yahoo.com/
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: =?iso-8859-1?q?hrishy?=
  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).
--
Mladen Gogala
Oracle DBA
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Mladen Gogala
 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).

RE: RE: Decode

[Mercadante, Thomas F]

Dick,

But is was a fun tool to program in!  It was so string in that it (at the
time) was the only tool that could interface with OS files, Oracle, and
produce output at the same time.  We bought it instead of RPT and RPF.  I
almost had a heart-attack when Oracle classes showed us those POC and said
"this is our batch tool".  I started searching immediately as we wanted to
throw away Cobol.  SQR was the only choice.  (This is back about 10 years
ago).

Have you tried the "Reverse Hebrew Wrap" printing option?  :)

When things got slow, I'd bring that baby up for a few laughs.

Tom Mercadante
Oracle Certified Professional


-Original Message-
Sent: Thursday, September 12, 2002 3:58 PM
To: Multiple recipients of list ORACLE-L


Tom,

I regret to say that SQR is still running around.  SOD awful piece of
crap
that should have dies along with RPT and RPF.  PeopleSoft makes a lot of use
of
it & it is a REAL performance boat anchor.

Dick Goulet

Reply Separator
Author: "Mercadante; Thomas F" <[EMAIL PROTECTED]>
Date:   9/12/2002 11:03 AM

Raj,
 
I know SQR.  Bought it when it was being peddled by Sql Solutions.  Great
tool!  Somehow, it never got marketed correctly.  I had heard that at one
point, Oracle "almost" bought it, and was going to position it as one of its
products.  But that boat has sailed.
 
Tom Mercadante 
Oracle Certified Professional 

-Original Message-
Sent: Thursday, September 12, 2002 2:08 PM
To: Multiple recipients of list ORACLE-L



Wait till you see some of the SQL that we run ... it is dynamic sql used
within SQR (a reporting tool). Compared to that, this is production quality.

Raj 
__ 
Rajendra Jamadagni  MIS, ESPN Inc. 
Rajendra dot Jamadagni at ESPN dot com 
Any opinion expressed here is personal and doesn't reflect that of ESPN Inc.

QOTD: Any clod can have facts, but having an opinion is an art! 


-Original Message- 
<mailto:[EMAIL PROTECTED]> ] 
Sent: Thursday, September 12, 2002 12:38 PM 
To: Multiple recipients of list ORACLE-L 


here's a decode that should *NEVER* be put into production!  :) 





RE: Decode



Raj,
 
I
know

SQR.  Bought it when it was being peddled by Sql Solutions.  Great

tool!  Somehow, it never got marketed correctly.  I had heard that
at 
one point, Oracle "almost" bought it, and was going to position it as one of
its

products.  But that boat has sailed.
 
Tom Mercadante Oracle Certified Professional 

  -Original Message-From: Jamadagni, Rajendra 
  [mailto:[EMAIL PROTECTED]]Sent: Thursday, September
12, 
  2002 2:08 PMTo: Multiple recipients of list 
  ORACLE-LSubject: RE: Decode
  Wait till you see some of the SQL that we run ... it is 
  dynamic sql used within SQR (a reporting tool). Compared to that, this is 
  production quality.
  Raj __
Rajendra Jamadagni  
      MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any 
  opinion expressed here is personal and doesn't reflect that of ESPN Inc. 
  QOTD: Any clod can have facts, but having an
opinion 
  is an art! 
  -Original Message- From: 
  Mercadante, Thomas F [mailto:[EMAIL PROTECTED]";>mailto:[EMAIL PROTECTED]]<
/F
ONT> 
  Sent: Thursday, September 12, 2002 12:38 PM
To: Multiple recipients of list ORACLE-L Subject: RE: Decode 
  here's a decode that should *NEVER* be put into 
  production!  :) 

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: 
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

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: Mercadante, Thomas F
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

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).

RE: Decode

[cbeckley]

Well, if we're going to be picky :)

select   chr(84)||chr(104)||chr(97)
 ||chr(110)||chr(107)||chr(32)
 ||chr(71)||chr(111)||chr(100)
 ||chr(101)||chr(115)||chr(115)
 ||chr(33)
 from dual
> 
> Jared, didn't you mean:
> 
>   select
>  chr(84)||chr(104)||chr(97)
>  ||chr(110)||chr(107)||chr(32)
>  ||chr(71)||chr(111)||chr(100)
>  ||chr(33)
>  from dual
> 
> Just being picky...  :D
> 
> Rich Jesse   System/Database Administrator
> [EMAIL PROTECTED]  Quad/Tech International, Sussex, WI USA
> 
> 
> > -Original Message-
> > From: Jared Still [mailto:[EMAIL PROTECTED]]
> > Sent: Wednesday, September 11, 2002 7:33 PM
> > To: Multiple recipients of list ORACLE-L
> > Subject: Re: Decode
> > 
> > 
> > 
> > No, I didn't know that.
> > 
> > Interesting:
> > 
> > select decode (
> >(select to_char(sysdate, 'DAY') from dual),
> >'MONDAY','Groan...',
> >'TUESDAY','Another day, another dollar',
> >'WEDNESDAY','Hump Day',
> >'THURSDAY','Let me out of here!',
> >'FRIDAY',(
> >   select
> >  chr(84)||chr(104)||chr(97)
> >  ||chr(110)||chr(107)||chr(32)
> >  ||chr(103)||chr(111)||chr(100)
> >  ||chr(33)
> >  from dual
> >   ),
> >'SATURDAY','Sleeping in',
> >'SUNDAY','Gone fishing'
> > )
> > from dual
> > /
> -- 
> Please see the official ORACLE-L FAQ: http://mail.thirdparadigm.com//jump/http://www.orafaq.com";>http://www.orafaq.com
> -- 
> Author: Jesse, Rich
>   INET: [EMAIL PROTECTED]
> 
> Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
> San Diego, California-- Public Internet access / Mailing Lists
> 
> 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).

Christopher Beckley
ThirdParadigm LLC
[EMAIL PROTECTED]
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: 
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

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).

RE: Decode

[Mercadante, Thomas F]

Title: RE: Decode



Raj,
 
I know 
SQR.  Bought it when it was being peddled by Sql Solutions.  Great 
tool!  Somehow, it never got marketed correctly.  I had heard that at 
one point, Oracle "almost" bought it, and was going to position it as one of its 
products.  But that boat has sailed.
 
Tom Mercadante Oracle Certified Professional 

  -Original Message-From: Jamadagni, Rajendra 
  [mailto:[EMAIL PROTECTED]]Sent: Thursday, September 12, 
  2002 2:08 PMTo: Multiple recipients of list 
  ORACLE-LSubject: RE: Decode
  Wait till you see some of the SQL that we run ... it is 
  dynamic sql used within SQR (a reporting tool). Compared to that, this is 
  production quality.
  Raj __ Rajendra Jamadagni  
      MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any 
  opinion expressed here is personal and doesn't reflect that of ESPN Inc. 
  QOTD: Any clod can have facts, but having an opinion 
  is an art! 
  -Original Message- From: 
  Mercadante, Thomas F [mailto:[EMAIL PROTECTED]] 
  Sent: Thursday, September 12, 2002 12:38 PM To: Multiple recipients of list ORACLE-L Subject: RE: Decode 
  here's a decode that should *NEVER* be put into 
  production!  :) 

RE: Decode

[Jamadagni, Rajendra]

Title: RE: Decode





Wait till you see some of the SQL that we run ... it is dynamic sql used within SQR (a reporting tool). Compared to that, this is production quality.

Raj
__
Rajendra Jamadagni      MIS, ESPN Inc.
Rajendra dot Jamadagni at ESPN dot com
Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. 
QOTD: Any clod can have facts, but having an opinion is an art!



-Original Message-
From: Mercadante, Thomas F [mailto:[EMAIL PROTECTED]]
Sent: Thursday, September 12, 2002 12:38 PM
To: Multiple recipients of list ORACLE-L
Subject: RE: Decode



here's a decode that should *NEVER* be put into production!  :)




*This e-mail 
message is confidential, intended only for the named recipient(s) above and may 
contain information that is privileged, attorney work product or exempt from 
disclosure under applicable law. If you have received this message in error, or are 
not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 
and delete this e-mail message from your computer, Thank 
you.*1

RE: Decode

[Mercadante, Thomas F]

here's a decode that should *NEVER* be put into production!  :)


-Original Message-
Sent: Thursday, September 12, 2002 12:18 PM
To: Multiple recipients of list ORACLE-L


Beware...nasty sql below

select decode((select deptno from dept where upper(loc) = upper('&vloc')),
<-- expression
  (select deptno from dept where loc = 'NEW YORK'),
<-- value1
  (select count(*) from emp where deptno = 10),
<-- return1
  (select deptno from dept where loc = 'DALLAS'),
<-- value2
  (select count(*) from emp where deptno = 20),
<-- return2
  (select deptno from dept where loc = 'CHICAGO'),
<-- value3
  (select count(*) from emp where deptno = 30),
<-- return3
  (select count(*) from emp where deptno not in (10,20,30))
<-- default_return
 ) emp_count
 from dual
 
 SQL> /
 Enter value for vloc: DALLAS
  EMP_COUNT
 --
  5
 
 SQL> /
 Enter value for vloc: Chicago
  EMP_COUNT
 --
  6
 
 SQL> /
 Enter value for vloc: DENVER
  EMP_COUNT
 --
  0
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Fink, Dan
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

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: Mercadante, Thomas F
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

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).

RE: Decode

[Fink, Dan]

Beware...nasty sql below

select decode((select deptno from dept where upper(loc) = upper('&vloc')),
<-- expression
  (select deptno from dept where loc = 'NEW YORK'),
<-- value1
  (select count(*) from emp where deptno = 10),
<-- return1
  (select deptno from dept where loc = 'DALLAS'),
<-- value2
  (select count(*) from emp where deptno = 20),
<-- return2
  (select deptno from dept where loc = 'CHICAGO'),
<-- value3
  (select count(*) from emp where deptno = 30),
<-- return3
  (select count(*) from emp where deptno not in (10,20,30))
<-- default_return
 ) emp_count
 from dual
 
 SQL> /
 Enter value for vloc: DALLAS
  EMP_COUNT
 --
  5
 
 SQL> /
 Enter value for vloc: Chicago
  EMP_COUNT
 --
  6
 
 SQL> /
 Enter value for vloc: DENVER
  EMP_COUNT
 --
  0
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Fink, Dan
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

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).

Re: Decode

[Ruth Gramolini]

Good example! ;=)   Thanks, Ruth
- Original Message - 
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Wednesday, September 11, 2002 3:38 PM


> select decode(new_job_loc,'Vermont','TAKE IT','LEAVE IT') from dual;
> 
> Waleed
> 
> -Original Message-
> Sent: Wednesday, September 11, 2002 2:59 PM
> To: Multiple recipients of list ORACLE-L
> 
> 
> Hello everyone,
> 
> I am having a senior moment and I have forgetten the syntax for 'decode'.
> Can anyone give me a hint.  I have leant out my sql books and of course I
> need them now.
> 
> Thanks in advance,
> Ruth Gramolini
> Oracle DBA, Vermont Department of Taxes
> 
> -- 
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> -- 
> Author: Ruth Gramolini
>   INET: [EMAIL PROTECTED]
> 
> Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
> San Diego, California-- Public Internet access / Mailing Lists
> 
> 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: Khedr, Waleed
>   INET: [EMAIL PROTECTED]
> 
> Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
> San Diego, California-- Public Internet access / Mailing Lists
> 
> 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: Ruth Gramolini
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

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).

RE: Decode

[Jesse, Rich]

Jared, didn't you mean:

  select
 chr(84)||chr(104)||chr(97)
 ||chr(110)||chr(107)||chr(32)
 ||chr(71)||chr(111)||chr(100)
 ||chr(33)
 from dual

Just being picky...  :D

Rich Jesse   System/Database Administrator
[EMAIL PROTECTED]  Quad/Tech International, Sussex, WI USA


> -Original Message-
> From: Jared Still [mailto:[EMAIL PROTECTED]]
> Sent: Wednesday, September 11, 2002 7:33 PM
> To: Multiple recipients of list ORACLE-L
> Subject: Re: Decode
> 
> 
> 
> No, I didn't know that.
> 
> Interesting:
> 
> select decode (
>(select to_char(sysdate, 'DAY') from dual),
>'MONDAY','Groan...',
>'TUESDAY','Another day, another dollar',
>'WEDNESDAY','Hump Day',
>'THURSDAY','Let me out of here!',
>'FRIDAY',(
>   select
>  chr(84)||chr(104)||chr(97)
>  ||chr(110)||chr(107)||chr(32)
>  ||chr(103)||chr(111)||chr(100)
>  ||chr(33)
>  from dual
>   ),
>'SATURDAY','Sleeping in',
>'SUNDAY','Gone fishing'
> )
> from dual
> /
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jesse, Rich
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

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).

Re: Decode

[Jared Still]

No, I didn't know that.

Interesting:

select decode (
   (select to_char(sysdate, 'DAY') from dual),
   'MONDAY','Groan...',
   'TUESDAY','Another day, another dollar',
   'WEDNESDAY','Hump Day',
   'THURSDAY','Let me out of here!',
   'FRIDAY',(
  select
 chr(84)||chr(104)||chr(97)
 ||chr(110)||chr(107)||chr(32)
 ||chr(103)||chr(111)||chr(100)
 ||chr(33)
 from dual
  ),
   'SATURDAY','Sleeping in',
   'SUNDAY','Gone fishing'
)
from dual
/


Jared

On Wednesday 11 September 2002 12:33, Fink, Dan wrote:
> DECODE(expression,
>value1, return1,
>value2, return2,
>
>default_return)
>
> BTW, did you know that expression, value, return and default_return can all
> be scalar subqueries?
>
> Dan
>
> -Original Message-
> Sent: Wednesday, September 11, 2002 12:59 PM
> To: Multiple recipients of list ORACLE-L
>
>
> Hello everyone,
>
> I am having a senior moment and I have forgetten the syntax for 'decode'.
> Can anyone give me a hint.  I have leant out my sql books and of course I
> need them now.
>
> Thanks in advance,
> Ruth Gramolini
> Oracle DBA, Vermont Department of Taxes
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jared Still
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

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).

RE: Decode

[Markham, Richard]

Title: RE: Decode





an example straight from 9i SQL Reference should do


SELECT product_id,
   DECODE (warehouse_id, 1, 'Southlake', 
 2, 'San Francisco', 
 3, 'New Jersey', 
 4, 'Seattle',
    'Non-domestic') 
   quantity_on_hand FROM inventories;



-Original Message-
From: Ruth Gramolini [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, September 11, 2002 2:59 PM
To: Multiple recipients of list ORACLE-L
Subject: Decode



Hello everyone,


I am having a senior moment and I have forgetten the syntax for 'decode'.
Can anyone give me a hint.  I have leant out my sql books and of course I
need them now.


Thanks in advance,
Ruth Gramolini
Oracle DBA, Vermont Department of Taxes


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Ruth Gramolini
  INET: [EMAIL PROTECTED]


Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California    -- Public Internet access / Mailing Lists

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).

RE: Decode

[Khedr, Waleed]

select decode(new_job_loc,'Vermont','TAKE IT','LEAVE IT') from dual;

Waleed

-Original Message-
Sent: Wednesday, September 11, 2002 2:59 PM
To: Multiple recipients of list ORACLE-L


Hello everyone,

I am having a senior moment and I have forgetten the syntax for 'decode'.
Can anyone give me a hint.  I have leant out my sql books and of course I
need them now.

Thanks in advance,
Ruth Gramolini
Oracle DBA, Vermont Department of Taxes

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Ruth Gramolini
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

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: Khedr, Waleed
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

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).

Re: Decode

[Rachel Carmichael]

decode (column_name, value, replacement, value, replacement,
default)

where value is the column value 
replacement is value you want to replace the column value with
default is the value to use if you get a column value not in your list

decode (operating_system, 'PC','Windows','SUN','UNIX','LINUX')


so if the operating_system column has the value PC, you want to have
Windows as the value you actually get,if it has SUN you get UNIX, if it
has anything else you will see LINUX


--- Ruth Gramolini <[EMAIL PROTECTED]> wrote:
> Hello everyone,
> 
> I am having a senior moment and I have forgetten the syntax for
> 'decode'.
> Can anyone give me a hint.  I have leant out my sql books and of
> course I
> need them now.
> 
> Thanks in advance,
> Ruth Gramolini
> Oracle DBA, Vermont Department of Taxes
> 
> -- 
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> -- 
> Author: Ruth Gramolini
>   INET: [EMAIL PROTECTED]
> 
> Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
> San Diego, California-- Public Internet access / Mailing
> Lists
> 
> 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).


__
Yahoo! - We Remember
9-11: A tribute to the more than 3,000 lives lost
http://dir.remember.yahoo.com/tribute
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Rachel Carmichael
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

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).

RE: Decode

[Fink, Dan]

DECODE(expression,
   value1, return1,
   value2, return2,
   
   default_return)

BTW, did you know that expression, value, return and default_return can all
be scalar subqueries?

Dan

-Original Message-
Sent: Wednesday, September 11, 2002 12:59 PM
To: Multiple recipients of list ORACLE-L


Hello everyone,

I am having a senior moment and I have forgetten the syntax for 'decode'.
Can anyone give me a hint.  I have leant out my sql books and of course I
need them now.

Thanks in advance,
Ruth Gramolini
Oracle DBA, Vermont Department of Taxes

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Ruth Gramolini
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

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: Fink, Dan
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

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).

RE: Decode

[Farnsworth, Dave]

DECODE(value,if1,then1,if2,then2,if3,then3,...else)

Have a great day Ruth!!

-Original Message-
Sent: Wednesday, September 11, 2002 1:59 PM
To: Multiple recipients of list ORACLE-L


Hello everyone,

I am having a senior moment and I have forgetten the syntax for 'decode'.
Can anyone give me a hint.  I have leant out my sql books and of course I
need them now.

Thanks in advance,
Ruth Gramolini
Oracle DBA, Vermont Department of Taxes

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Ruth Gramolini
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

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: Farnsworth, Dave
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

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).

RE: Decode

[DENNIS WILLIAMS]

Ruth - Google! Google!

Decode
Oracle's handy decode function works as follows: 
decode(expr, search, expr[, search, expr...] [, default])

To evaluate this expression, Oracle compares expr to each search value one
by one. If expr is equal to a search, Oracle returns the corresponding
result. If no match is found, Oracle returns default, or, if default is
omitted, returns null. 
 
Dennis Williams
DBA
Lifetouch, Inc.
[EMAIL PROTECTED]  

-Original Message-
Sent: Wednesday, September 11, 2002 1:59 PM
To: Multiple recipients of list ORACLE-L


Hello everyone,

I am having a senior moment and I have forgetten the syntax for 'decode'.
Can anyone give me a hint.  I have leant out my sql books and of course I
need them now.

Thanks in advance,
Ruth Gramolini
Oracle DBA, Vermont Department of Taxes

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Ruth Gramolini
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

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: DENNIS WILLIAMS
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

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).

RE: Decode

[Kevin Lange]

decode(test_value,if_A,then_B,if_C,then_D,...,otherwise_E)

decode(city,'San Francisco','CA','Dallas','TX','Unknown')

If the city is 'San Francisco', return 'CA'.
If the city is 'Dallas', return 'TX'
If neither, return 'Unknown'



-Original Message-
Sent: Wednesday, September 11, 2002 1:59 PM
To: Multiple recipients of list ORACLE-L


Hello everyone,

I am having a senior moment and I have forgetten the syntax for 'decode'.
Can anyone give me a hint.  I have leant out my sql books and of course I
need them now.

Thanks in advance,
Ruth Gramolini
Oracle DBA, Vermont Department of Taxes

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Ruth Gramolini
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

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: Kevin Lange
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

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).

RE: Decode

[Rodd Holman]

Here you go, Ruth,



select decode(field,'data value1','output1','data value2','output2',...'output if none others')  MEANINGFUL_ALIAS

from   yadayada



Rodd



On Wed, 2002-09-11 at 14:28, Carle, William T (Bill), ALCAS wrote:

Ruth,

You can pull manuals off the web at this URL:

http://download-east.oracle.com/otndoc/oracle9i/901_doc/nav/docindex.htm

This is for 9i. If you have 8i, they have that too.


Bill Carle
AT&T
Database Administrator
816-995-3922
[EMAIL PROTECTED]

 -Original Message-
Sent:	Wednesday, September 11, 2002 1:59 PM
To:	Multiple recipients of list ORACLE-L
Subject:	Decode

Hello everyone,

I am having a senior moment and I have forgetten the syntax for 'decode'.
Can anyone give me a hint.  I have leant out my sql books and of course I
need them now.

Thanks in advance,
Ruth Gramolini
Oracle DBA, Vermont Department of Taxes

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Ruth Gramolini
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

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: Carle, William T (Bill), ALCAS
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

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).

Re: Decode

[Ron Rogers]

Ruth,
 The DECODE is the equivelent of IF,THEN,ELSE.
  select Decode(column, value IF, value  THEN,  value ELSE) from
tablename;
Ron
ROR 

>>> [EMAIL PROTECTED] 09/11/02 02:59PM >>>
Hello everyone,

I am having a senior moment and I have forgetten the syntax for
'decode'.
Can anyone give me a hint.  I have leant out my sql books and of course
I
need them now.

Thanks in advance,
Ruth Gramolini
Oracle DBA, Vermont Department of Taxes

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com 
-- 
Author: Ruth Gramolini
  INET: [EMAIL PROTECTED] 

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

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: Ron Rogers
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

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).

RE: Decode

[Magaliff, Bill]

DECODE(,);



-Original Message-
Sent: Wednesday, September 11, 2002 2:59 PM
To: Multiple recipients of list ORACLE-L


Hello everyone,

I am having a senior moment and I have forgetten the syntax for 'decode'.
Can anyone give me a hint.  I have leant out my sql books and of course I
need them now.

Thanks in advance,
Ruth Gramolini
Oracle DBA, Vermont Department of Taxes

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Ruth Gramolini
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

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: Magaliff, Bill
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

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).

RE: Decode

[Carle, William T (Bill), ALCAS]

Ruth,

You can pull manuals off the web at this URL:

http://download-east.oracle.com/otndoc/oracle9i/901_doc/nav/docindex.htm

This is for 9i. If you have 8i, they have that too.


Bill Carle
AT&T
Database Administrator
816-995-3922
[EMAIL PROTECTED]

 -Original Message-
Sent:   Wednesday, September 11, 2002 1:59 PM
To: Multiple recipients of list ORACLE-L
Subject:Decode

Hello everyone,

I am having a senior moment and I have forgetten the syntax for 'decode'.
Can anyone give me a hint.  I have leant out my sql books and of course I
need them now.

Thanks in advance,
Ruth Gramolini
Oracle DBA, Vermont Department of Taxes

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Ruth Gramolini
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

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: Carle, William T (Bill), ALCAS
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

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).

Re: DECODE HELP SOLVED

[Viktor]

Thanks Lyuda and Ron for your help!

--- Lyuda Hoska <[EMAIL PROTECTED]> wrote:
> Something like this would work.  
> SELECT GREATEST(FIRST_DATE,
> NVL(SEC_DATE,THIRD_DATE),NVL(THIRD_DATE,SEC_DATE))
> FROM ;
> Just an idea, you can explore it further..
> 
> -Original Message-
> Sent: Monday, May 20, 2002 3:48 PM
> To: Multiple recipients of list ORACLE-L
> 
> Hello all,
> 
> This has probably been discussed here before and I'm
> sorry if I'm repeating this. But I'd very much
> appreciate if someone could show me how to use
> DECODE
> to select a MAX date where records are like this:
> 
> PRIM. KEY  FIRST_DATE SEC_DATE   THIRD_DATE
> JO001599O 12/14/2000 03/23/2001 05/21/2001 
> JO001620L12/27/2000 05/16/2001 
> 
> The first_date, sec_date and third_date may or may
> not
> exist; either one of the dates will be there. I need
> to come up with another column LATEST_DATE which
> will
> show the latest date of the 3.
> 
> Thanks very much in advance!
> 
> 
> __
> Do You Yahoo!?
> LAUNCH - Your Yahoo! Music Experience
> http://launch.yahoo.com
> -- 
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
> -- 
> Author: Viktor
>   INET: [EMAIL PROTECTED]
> 
> Fat City Network Services-- (858) 538-5051  FAX:
> (858) 538-5051
> San Diego, California-- Public Internet
> access / Mailing Lists
>

> 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: Lyuda Hoska
>   INET: [EMAIL PROTECTED]
> 
> Fat City Network Services-- (858) 538-5051  FAX:
> (858) 538-5051
> San Diego, California-- Public Internet
> access / Mailing Lists
>

> 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).


__
Do You Yahoo!?
LAUNCH - Your Yahoo! Music Experience
http://launch.yahoo.com
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Viktor
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

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).

Re: DECODE HELP

[Ron Thomas]

Why use decode.  Look up the "greatest" sql function.

select prim_key, greatest(first_date,sec_date,third_date) from your_table.

Ron Thomas
Hypercom, Inc
[EMAIL PROTECTED]
"The problem with some people is that when they aren't drunk, they're sober."  
--William Butler
Yeats.


   
   
  [EMAIL PROTECTED]
   
  mTo:   [EMAIL PROTECTED]  
   
  Sent by: cc: 
   
  [EMAIL PROTECTED] Subject:  DECODE HELP   
   
   
   
   
   
  05/20/02 01:48 PM
   
  Please respond to
   
  ORACLE-L 
   
   
   
   
   




Hello all,

This has probably been discussed here before and I'm
sorry if I'm repeating this. But I'd very much
appreciate if someone could show me how to use DECODE
to select a MAX date where records are like this:

PRIM. KEY  FIRST_DATE SEC_DATE   THIRD_DATE
JO001599O 12/14/2000 03/23/2001 05/21/2001
JO001620L12/27/2000 05/16/2001

The first_date, sec_date and third_date may or may not
exist; either one of the dates will be there. I need
to come up with another column LATEST_DATE which will
show the latest date of the 3.

Thanks very much in advance!


__
Do You Yahoo!?
LAUNCH - Your Yahoo! Music Experience
http://launch.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Viktor
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

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: Ron Thomas
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

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).

Re: Decode in PL/Sql

[Igor Neyman]

Gyula,

I understand, that you want to apply DECODE function to some local variables
in your pl/sql block (and not to some columns in some table).

The trick is: you can always run DECODE() against 'dual' table:

select DECODE(base_expr, compare1, value1, compare2, value2,... default )
into local_variable_out from dual;

In this case 'base_expr' does not have to reference any column in any table.

Igor Neyman, OCP DBA
Perceptron, Inc.
(734)414-4627
[EMAIL PROTECTED]


- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Thursday, May 17, 2001 5:26 AM


> Hi Gurus !
>
>
> Is there any function that can be used in PL/SQL and  just works the way
as
> DECODE does in Sql?
>
> Thanks in Advance
>
> Gyula
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Andor, Gyula
>   INET: [EMAIL PROTECTED]
>
> Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
> San Diego, California-- Public Internet access / Mailing Lists
> 
> 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: Igor Neyman
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

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).

RE: Decode in PL/Sql

[FOX, Simon]

Feuerstein recommends replacing with IF statements.  Page 517 Oracle PL/SLQ
Programming.

Simon Fox

Room 205
Rail House
Gresty Road
CREWE
Cheshire
CW2 6EA
England

01270 533997

-Original Message-
Sent: 17 May 2001 10:26
To: Multiple recipients of list ORACLE-L


Hi Gurus !


Is there any function that can be used in PL/SQL and  just works the way as
DECODE does in Sql?

Thanks in Advance

Gyula
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Andor, Gyula
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

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).


___
This email is confidential and intended solely for the use of the 
individual to whom it is addressed. Any views or opinions presented are 
solely those of the author and do not necessarily represent those of 
Sema. 
If you are not the intended recipient, be advised that you have received this
email in error and that any use, dissemination, forwarding, printing, or 
copying of this email is strictly prohibited.

If you have received this email in error please notify the Sema UK
Helpdesk by telephone on +44 (0) 121 627 5600.
___
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: FOX, Simon
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

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).