Home » SQL & PL/SQL » SQL & PL/SQL » SQL Query
SQL Query [message #420690] Tue, 01 September 2009 09:40 Go to next message
mr.rajeshyadav
Messages: 48
Registered: November 2007
Member
what is wrong in this query i could not figure out

select (
CASE
WHEN to_number(to_char(sysdate), 'dd') = 28 THEN 0
WHEN to_number(to_char(sysdate), 'dd') = 30 THEN 1
ELSE 2
END
) days from dual


Thanks In advance,
Rajesh.
Re: SQL Query [message #420691 is a reply to message #420690] Tue, 01 September 2009 09:48 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
mr.rajeshyadav wrote on Tue, 01 September 2009 16:40
what is wrong in this query

Wrongly positioned parenthesis. 'dd' shall be format for TO_CHAR function, not TO_NUMBER.
SQL> select (
  2  CASE
  3  WHEN to_number(to_char(sysdate, 'dd')) = 28 THEN 0
  4  WHEN to_number(to_char(sysdate, 'dd')) = 30 THEN 1
  5  ELSE 2
  6  END
  7  ) days from dual;

      DAYS
----------
         2

1 row selected.

SQL> 
Alternatively, you may use EXTRACT function (as it returns number).
Re: SQL Query [message #420693 is a reply to message #420690] Tue, 01 September 2009 09:56 Go to previous messageGo to next message
mr.rajeshyadav
Messages: 48
Registered: November 2007
Member
Thank you
After posting here i checked once again i have rectified
Thank you once again
Re: SQL Query [message #420695 is a reply to message #420690] Tue, 01 September 2009 09:58 Go to previous message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Before posting your next question, please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), 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.
Use SQL*Plus and copy and paste your session, do not just ask what's wrong we have no syntax analyzer transplanted in our brain.

Regards
Michel

[Updated on: Tue, 01 September 2009 09:59]

Report message to a moderator

Previous Topic: ORA-01847: day of month must be between 1 and last day of month error
Next Topic: PERFORMANCE TUNING
Goto Forum:
  


Current Time: Sun Dec 11 06:29:00 CST 2016

Total time taken to generate the page: 0.07398 seconds