Home » Developer & Programmer » Reports & Discoverer » How to achieve in DECODE
How to achieve in DECODE [message #266490] Tue, 11 September 2007 00:38 Go to next message
Imran_Chennai
Messages: 22
Registered: June 2007
Location: CHN,INDIA
Junior Member

Hi,

My below query picks record for 7,30 & 90 days old orders,

SELECT DISTINCT TRUNC(SYSDATE-order_date),
DECODE(TRUNC(SYSDATE-order_date),7,'7 DAYS BO',30,'30 DAYS BO',90,'90 DAYS BO','OTHERS') NOTICE
FROM coe_orders ORDER BY NOTICE;

I need to pick records having 7+, 30+ & 90+ days. And categorize the string message like '7+ DAYS BO','30+ DAYS BO',90,'90+ DAYS BO'

The above query is part of select stmt, so i cant put it in seprate IF or CASE, I need to achieve this by decode or anything equivalent within SELECT stmt

Please give your idea

Thanks in advance
Imran
Re: How to achieve in DECODE [message #266622 is a reply to message #266490] Tue, 11 September 2007 04:17 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Quote:
The above query is part of select stmt, so i cant put it in seprate IF or CASE
I have never seen any restriction on CASE in the select statement.

By
Vamsi
Re: How to achieve in DECODE [message #266716 is a reply to message #266622] Tue, 11 September 2007 08:40 Go to previous messageGo to next message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
Lookup CASE in the SQL reference (not PL/SQL), you'll be amazed Wink
Re: How to achieve in DECODE [message #266967 is a reply to message #266716] Wed, 12 September 2007 02:24 Go to previous messageGo to next message
Imran_Chennai
Messages: 22
Registered: June 2007
Location: CHN,INDIA
Junior Member

Yes friends,

I got it .. able to frame CASE in SELECT

thx
Imran
Re: How to achieve in DECODE [message #267248 is a reply to message #266622] Wed, 12 September 2007 17:31 Go to previous messageGo to next message
Soumen Kamilya
Messages: 128
Registered: August 2007
Location: Kolkata
Senior Member

[quote title=vamsi kasina wrote on Tue, 11 September 2007 10:17]
Quote:
I have never seen any restriction on CASE in the select statement.



Yes there is a restriction before oracle 9i. You can't be able to use CASE in SELECT statement in cursor if you are using the database version below 9i, i.e. Oracle 8i does not support CASE statement in a cursor.

Cheers
Soumen
Re: How to achieve in DECODE [message #267395 is a reply to message #267248] Thu, 13 September 2007 03:27 Go to previous message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
@Soumen
oops!!!
Thanks for the update.

By
Vamsi
Previous Topic: HELLO
Next Topic: comparing an existing field with an empty string
Goto Forum:
  


Current Time: Sun Dec 11 04:26:19 CST 2016

Total time taken to generate the page: 0.03861 seconds