nested decode help [message #340790] |
Thu, 14 August 2008 05:45  |
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 #340793 is a reply to message #340792] |
Thu, 14 August 2008 06:01   |
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   |
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
|
|
|
|