Home » SQL & PL/SQL » SQL & PL/SQL » Guide me in writing Case Statement
Guide me in writing Case Statement [message #570666] Tue, 13 November 2012 23:45 Go to next message
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
Re: Guide me in writing Case Statement [message #570668 is a reply to message #570666] Tue, 13 November 2012 23:53 Go to previous messageGo to next message
Michel Cadot
Messages: 57605
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
Re: Guide me in writing Case Statement [message #570669 is a reply to message #570668] Wed, 14 November 2012 00:02 Go to previous messageGo to next message
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
Re: Guide me in writing Case Statement [message #570672 is a reply to message #570669] Wed, 14 November 2012 01:13 Go to previous messageGo to next message
Michel Cadot
Messages: 57605
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 Go to previous messageGo to next message
joy_division
Messages: 4453
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.
Re: Guide me in writing Case Statement [message #570700 is a reply to message #570699] Wed, 14 November 2012 08:17 Go to previous message
Michel Cadot
Messages: 57605
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
Previous Topic: oracle tunning
Next Topic: Get the week of the Year (First week with starting with first SUNDAY of year)
Goto Forum:
  


Current Time: Thu Apr 17 06:01:25 CDT 2014

Total time taken to generate the page: 0.10022 seconds