Home » SQL & PL/SQL » SQL & PL/SQL » nested decode help (SQL*Plus: Release 10.1.0.4.2)
nested decode help [message #340790] Thu, 14 August 2008 05:45 Go to next message
pday@tullib.com
Messages: 11
Registered: March 2006
Location: London
Junior Member
Hi

I'm having a problem with a nested decode. I've tried a few permutations but can't get the result I need so wondered if anyone could help.

I've simplified the SQL by hard coding the dates - in the real sql they are supplied from tables.

What I need to see is as follows:

if the first date = second date take 'EOP' else look at the day of the week of the third date. If the day is a Friday take 'EOW' else take 'EOD'

select decode('05-SEP-2008','01-SEP-2008','EOP',(decode(to_char(to_date('05-SEP-2008'),'Day'),'Friday','EOW','EOD')))
from dual

The first part works fine - I can get the statement to return EOP however if the first 2 dates are not the same the statement returns EOD regardless of whether the third date is a Friday.

Hopefully that makes sense.

Many thanks
Paul
Re: nested decode help [message #340792 is a reply to message #340790] Thu, 14 August 2008 05:54 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If you have difficulties to express it with decode use CASE far simpler to use in complex cases.

Regards
Michel
Re: nested decode help [message #340793 is a reply to message #340792] Thu, 14 August 2008 06:01 Go to previous messageGo to next message
pday@tullib.com
Messages: 11
Registered: March 2006
Location: London
Junior Member
Good idea Michel, I had not thought about using case. I'll have a play now. I must admit though I would have thought it was possible with a decode and would still be interested to understand why the statement does not work.

Regards

Re: nested decode help [message #340794 is a reply to message #340793] Thu, 14 August 2008 06:03 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I think this highlights the actual cause of your problem quite nicely:
SQL> select 'X'||to_char(to_date('05-sep-2008','dd-mm-yyyy'),'Day')||'X'
  2        ,'X'||to_char(to_date('05-sep-2008','dd-mm-yyyy'),'fmDay')||'X'
  3  from dual;

'X'||TO_CHA 'X'||TO_CHA
----------- -----------
XFriday   X XFridayX
Re: nested decode help [message #340797 is a reply to message #340794] Thu, 14 August 2008 06:17 Go to previous message
pday@tullib.com
Messages: 11
Registered: March 2006
Location: London
Junior Member
Many thanks - query works now. Did not think about whitepace!
Previous Topic: Procedure getting hanged
Next Topic: ORA-01445: cannot select ROWID from, or sample, a join view without a key-preserved table
Goto Forum:
  


Current Time: Sun Dec 04 17:02:38 CST 2016

Total time taken to generate the page: 0.24130 seconds