OraFAQ Forum: SQL & PL/SQL » CASE statement

Home » SQL & PL/SQL » SQL & PL/SQL » CASE statement (10g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

CASE statement [message #597021]

Mon, 30 September 2013 03:57

pstanand
Messages: 133
Registered: February 2005
Location: Chennai,India

Senior Member

Hi,
I have a table test_date with the following structure.
test_date(id number,tdate date)
And it contains values as follows
1 5-Sep-13
2 6-Sep-13
3 4-Sep-13
4 null
5 1-Oct-13
6 5-Oct-13

I would like to get the maximum date value if the tdate is null. I used the following query and it doesn't fetches the correct result. Could you please correct me where i'm wrong.
select case when tdate is null then (select max(tdate)from test_date)end tdate from test_date.

Thanks.
Anand

Report message to a moderator

Re: CASE statement [message #597022 is a reply to message #597021]

Mon, 30 September 2013 04:36

Littlefoot
Messages: 21808
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

You are missing ELSE; you told it what to do when TDATE is null, but you didn't say what to do when it is NOT NULL:

select case when tdate is null then (select max(tdate) from test) 
            else tdate 
       end tdate

Or, you could have used NVL:

select
  nvl(tdate, (select max(tdate) from test_date)) datum
from test_date

Or

with max_date as
  (select max(tdate) mtdate from test_date)
select 
  nvl(tdate, mtdate)
from test_date,
     max_date

Or any other valid option you can think of.

Report message to a moderator

Re: CASE statement [message #597023 is a reply to message #597021]

Mon, 30 September 2013 04:38

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

What does mean "it doesn't fetches the correct result"?
What should be the result for the sample data you gave?

Report message to a moderator

Re: CASE statement [message #597024 is a reply to message #597021]

Mon, 30 September 2013 04:40

peterf
Messages: 5
Registered: September 2013
Location: India

Junior Member

Hi Anand,

Below code should work.

select id,
(case nvl(to_char(tdate,'dd/mm/yyyy'),'x')
when 'x' then (select max(tdate) from test_date)
else tdate
end)
from test_date

Let me know if it works Smile

Smile

Report message to a moderator

Re: CASE statement [message #597025 is a reply to message #597021]

Mon, 30 September 2013 04:40

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

If this the max in the whole table it could be:

select id,
       nvl(tdate, 
           max(tdate) over 
             (order by id
             rows between unbounded preceding and unbounded following)
         tdate
from test_date

or

select id,
       nvl(tdate, (select max(tdate) from test_date)) tdate
from test_date

or

with maxdate as (select max(tdate) maxdt from test_date)
select id, nvl(tdate, maxdt) tdate
from test_date, maxdate

...

[Updated on: Mon, 30 September 2013 13:35]

Report message to a moderator

Re: CASE statement [message #597026 is a reply to message #597025]

Mon, 30 September 2013 04:46

peterf
Messages: 5
Registered: September 2013
Location: India

Junior Member

Hello Moderators,
Apologies for not posting the code in previous message without using add code feature.

SELECT ID,
       (CASE NVL (TO_CHAR (tdate, 'dd/mm/yyyy'), 'x')
           WHEN 'x'
              THEN (SELECT MAX (tdate)
                      FROM test_date)
           ELSE tdate
        END
       )
  FROM test_date

Report message to a moderator

Re: CASE statement [message #597029 is a reply to message #597024]

Mon, 30 September 2013 05:18

pstanand
Messages: 133
Registered: February 2005
Location: Chennai,India

Senior Member

Thank you very much peter. It is working fine.

Report message to a moderator

Re: CASE statement [message #597031 is a reply to message #597029]

Mon, 30 September 2013 05:56

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

OK, now we know that you don't care about answers from others than Peter and it is useless to answer you.

Report message to a moderator

Re: CASE statement [message #597068 is a reply to message #597026]

Mon, 30 September 2013 13:15

pablolee
Messages: 2882
Registered: May 2007
Location: Scotland

Senior Member

Why the unnecessary to_char on the date Peter? What's the point of it?

Report message to a moderator

Re: CASE statement [message #597069 is a reply to message #597068]

Mon, 30 September 2013 13:35

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

And why the NVL(...,'x')?
Why the CASE?

Report message to a moderator

Re: CASE statement [message #597072 is a reply to message #597069]

Mon, 30 September 2013 13:44

pablolee
Messages: 2882
Registered: May 2007
Location: Scotland

Senior Member

Michel Cadot wrote on Mon, 30 September 2013 19:35

And why the NVL(...,'x')?
Why the CASE?

All valid questions that I was going to lead up to Michel Wink

Wink

Report message to a moderator

Re: CASE statement [message #597077 is a reply to message #597072]

Mon, 30 September 2013 14:24

Littlefoot
Messages: 21808
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Let me guess: it is because he managed to compose such a query at the moment.
Does it work? Yes, it seems so.
Could it be improved? Yes, certainly.

Although number of posts doesn't show someone's knowledge and experience, it seems that @peterf isn't very experienced. I'm quite sure that, after he spends years and decades in writing Oracle queries, he'll be capable of solving this very problem having his eyes closed. As of now, well, query looks as it does.

So, perhaps you could tell him what you found wrong or suboptimal so that all of us could learn something from you. How does that sound?

Report message to a moderator

Re: CASE statement [message #597078 is a reply to message #597077]

Mon, 30 September 2013 14:36

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

The questions were there to make him think about his query and then improve it, not to actually get an answer.

Report message to a moderator

Re: CASE statement [message #597085 is a reply to message #597078]

Mon, 30 September 2013 15:49

Littlefoot
Messages: 21808
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Right; I'm sure he won't sleep tonight, thinking about his query and the ways he could improve it.

Report message to a moderator

Re: CASE statement [message #597088 is a reply to message #597077]

Mon, 30 September 2013 16:12

pablolee
Messages: 2882
Registered: May 2007
Location: Scotland

Senior Member

Littlefoot wrote on Mon, 30 September 2013 20:24

Let me guess: it is because he managed to compose such a query at the moment.

I posted Quote:

All valid questions that I was going to lead up to Michel

So I'm not sure how our comment relates to what I wrote LF. However, yes, quite clearly that that is what he composed at that point in time. Do you have an issue with me asking him why he used the to_char? If so, why. I fully intended to discuss with him why it was not necessary, so I'm not sure what the problem is.

Quote:

Does it work? Yes, it seems so.

And if what he had written didn't work, I would have posted something different, rather than ask him about an initially unnecessary piece of code.
Quote:

Could it be improved? Yes, certainly.

OK, in fact, the code that you posted earlier was an improvement, however, the OP seemed to think that Peter's was the one to go for. Do you think that possibly, discussing the problems with that code might have been beneficial?

Quote:

Although number of posts doesn't show someone's knowledge and experience, it seems that @peterf isn't very experienced. I'm quite sure that, after he spends years and decades in writing Oracle queries, he'll be capable of solving this very problem having his eyes closed. As of now, well, query looks as it does.

I'm not really sure what point you're making here LittleFoot. The query looks as it does, yes, and it is need need of improvement as has already been said. In fact, better solutions already exist in the Thread, unfortunately, as I already said, the OP seems to want to take the 'least good' option. Again, the reason for me starting a discussion by asking, in the first instance, about the unnecessary to_char.

Quote:

So, perhaps you could tell him what you found wrong or suboptimal

I'm pretty sure that I did that, when I said:
Quote:

Why the unnecessary to_char on the date Peter?

Quote:

so that all of us could learn something from you. How does that sound?

Kind of condescending, to be honest, very unlike you LF. I don't pretend to be a font of all knowledge. I simply tried to initiate a discussion on the problems with the solution provided.

[Updated on: Mon, 30 September 2013 16:15]

Report message to a moderator

Re: CASE statement [message #597103 is a reply to message #597088]

Tue, 01 October 2013 00:20

Littlefoot
Messages: 21808
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Huh, right, this might turn into another useless discussion. Worst of all, I started it. What an idiot.

The fact is: I was astonished with all these why this or why not that questions. It was a loooong day yesterday and my tolerance level was pretty low, so I wrote what I wrote so I'll just have to live with it.

As of the OP picking the least good solution and thanking its creator, that's nothing to be upset about (especially not stating that he doesn't deserve answers in the future). Having ~40 posts on a forum in 8 years suggests that he is not an experienced forum user. People like him sometimes don't read everything that was posted, but jump to the last message and fail to observe everything else.

Finally, as I was pretty much wrong and probably unnecessarily overreacted, I apologize if I hurt anyone's feelings.

Report message to a moderator

Re: CASE statement [message #597106 is a reply to message #597021]

Tue, 01 October 2013 01:57

peterf
Messages: 5
Registered: September 2013
Location: India

Junior Member

Hello everyone.

Ok First of all I am new to SQL and PL-sql.

Second I wrote case statement as Anand was using it and saying that it is not working. So I thought I will try to use the case statement. Otherwise I myself would have not used it. I would have use as Michel said

select
  nvl(tdate, (select max(tdate) from test_date)) datum
from test_date

Thirdly yes you all are correct neither NVL nor to_char is necessary.
I am doing reporting now and I am converting every date datatype to char for formatting and hence maybe I went with the flow. Razz

Razz

It is good to have an discussion as it would improve my sql and maybe after a few years as Littlefoot said I would write these queries with my eyes closed Smile

Smile

And why did Anand use mine I think he was just looking for a readymade case statement and when he saw the code he checked it and then used it without breaking much head on other valuable posts.

So we should hopefully close this discussion now. thanks everyone for their suggestion.

Report message to a moderator

Re: CASE statement [message #597108 is a reply to message #597106]

Tue, 01 October 2013 02:27

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Thanks for the feedback.

Report message to a moderator

Re: CASE statement [message #597109 is a reply to message #597106]

Tue, 01 October 2013 02:41

pablolee
Messages: 2882
Registered: May 2007
Location: Scotland

Senior Member

Quote:

I am doing reporting now and I am converting every date datatype to char for formatting and hence maybe I went with the flow.

LOL, yes, that makes a lot of sense. Something I'm sure that we're all guilty of. Smile

Smile

Quote:

And why did Anand use mine I think he was just looking for a readymade case statement and when he saw the code he checked it and then used it without breaking much head on other valuable posts.

Agreed, that is a definite possibilty. Hopefully the OP will now realise that there were slightly better options available. What say you Annand?
Quote:

So we should hopefully close this discussion now. thanks everyone for their suggestion.

Thumbs Up

[Updated on: Tue, 01 October 2013 02:41]

Report message to a moderator

Previous Topic:	REGEXP_SUBSTR
Next Topic:	SQL query

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Apr 24 22:40:14 CDT 2024