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

Report message to a moderator

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: 2112
Registered: October 2003
Location: Cincinnati, OH
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

Report message to a moderator

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: 913
Registered: March 2005
Location: Netherlands
Senior Member
Lookup CASE in the SQL reference (not PL/SQL), you'll be amazed Wink

Report message to a moderator

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

Report message to a moderator

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

Report message to a moderator

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: 2112
Registered: October 2003
Location: Cincinnati, OH
Senior Member
@Soumen
oops!!!
Thanks for the update.

By
Vamsi

Report message to a moderator

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

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Fri Jul 05 05:49:36 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.