OraFAQ Forum: SQL & PL/SQL » Guide me in writing Case Statement

Home » SQL & PL/SQL » SQL & PL/SQL » Guide me in writing Case Statement

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

Guide me in writing Case Statement [message #570666]

Tue, 13 November 2012 23:45

srikanth_d5
Messages: 39
Registered: June 2012
Location: hyderabad

Member

hi all

For A_Year should be in between 2003 and 2012
and for A_year=2003 A_date should be greater than 31-mar-2003

select A_year ,count(*)
from temp_data
where (A_year between '2003' AND '2012')
and
case A_year when '2003' then to_date(A_date,'dd-mon-yy')>to_date('31-MAR-2003','dd-mon-yy')
end
GROUP BY A_year

Thanks and regards

Report message to a moderator

Re: Guide me in writing Case Statement [message #570668 is a reply to message #570666]

Tue, 13 November 2012 23:53

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

Senior Member
Account Moderator

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version, with 4 decimals.

With any SQL or PL/SQL question, please, Post a working [[Test case]]: create table and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

and ( a_year != 2003 or A_date > to_date('31-MAR-2003','dd-mon-yy') )

Assuming a_date is of DATE datatype and a_year NUMBER/INTEGER.
Why do you put quotes around numbers?

Regards
Michel

Report message to a moderator

Re: Guide me in writing Case Statement [message #570669 is a reply to message #570668]

Wed, 14 November 2012 00:02

srikanth_d5
Messages: 39
Registered: June 2012
Location: hyderabad

Member

in my table both A_year and A_date are Varchar2(100)

when i'm executing below statement the Query executing successful

select A_year,A_date
from temp_data
where A_year='2003' and to_date(A_date,'dd-mon-yy')>to_date('31-MAR-2003','dd-mon-yy')

thanks and regards

Report message to a moderator

Re: Guide me in writing Case Statement [message #570672 is a reply to message #570669]

Wed, 14 November 2012 01:13

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

Senior Member
Account Moderator

Before your next post:

Michel Cadot wrote on Wed, 14 November 2012 06:53

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version, with 4 decimals.
...
Regards
Michel

[Updated on: Wed, 14 November 2012 01:13]

Report message to a moderator

Re: Guide me in writing Case Statement [message #570699 is a reply to message #570669]

Wed, 14 November 2012 08:02

joy_division
Messages: 4267
Registered: February 2005
Location: East Coast USA

Senior Member

srikanth_d5 wrote on Wed, 14 November 2012 01:02

to_date('31-MAR-2003','dd-mon-yy')

format mask does not match data.

Report message to a moderator

Re: Guide me in writing Case Statement [message #570700 is a reply to message #570699]

Wed, 14 November 2012 08:17

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

Senior Member
Account Moderator

Sure, and I blindly copied and pasted his condition.
Anyway, it is the least problem he will have with his datatypes.

Regards
Michel

Report message to a moderator

Previous Topic:	oracle tunning
Next Topic:	Get the week of the Year (First week with starting with first SUNDAY of year)

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri May 24 03:52:42 CDT 2013

Total time taken to generate the page: 0.14854 seconds