Home » SQL & PL/SQL » SQL & PL/SQL » Case Statement not working in Insert statement (Oracle 10g,Unix)
Case Statement not working in Insert statement [message #337845] Fri, 01 August 2008 06:00 Go to next message
learn
Messages: 3
Registered: August 2008
Junior Member
Hi All

Please find the below two codes
1. Select with case statement
2. Same case statement in Insert statement.

First one works fine, whereas the same case statement is not working in Insert statement.


I am using Oracle 10g. Can you please help me out.

SELECT BRTH_DT,
(CASE BRTH_DT WHEN TO_DATE('31-DEC-07')
THEN NULL
ELSE TO_CHAR(BRTH_DT,'MM/DD/YYYY')
END)
FROM CUST_DTL;

Above code works fine.

-----------------------------------------------------------------

INSERT INTO CUST_MST_DTL(BRTH_DT)
SELECT (CASE
WHEN (BRTH_DT) LIKE '12/31/2008'
THEN NULL
ELSE TO_CHAR(BRTH_DT,'MM/DD/YYYY')
END)
FROM CUST_DTL;

Case statement in Insert is not working. I am getting ora-01843: not valid month error
Re: Case Statement not working in Insert statement [message #337847 is a reply to message #337845] Fri, 01 August 2008 06:14 Go to previous messageGo to next message
Michel Cadot
Messages: 64131
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Repeat: a string is NOT a date, a string is NOT a date, a string is NOT a date.

Use TO_DATE function.

Before posting next time, 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) and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Regards
Michel
Re: Case Statement not working in Insert statement [message #337859 is a reply to message #337845] Fri, 01 August 2008 06:38 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Quote:
First one works fine, whereas the same case statement is not working in Insert statement.


In order for this to be true, you would have to have two identical case statements.

You have:
CASE BRTH_DT WHEN TO_DATE('31-DEC-07')
THEN NULL
ELSE TO_CHAR(BRTH_DT,'MM/DD/YYYY')
END
and
CASE
WHEN (BRTH_DT) LIKE '12/31/2008'
THEN NULL
ELSE TO_CHAR(BRTH_DT,'MM/DD/YYYY')
END


They're not quite the same, are they.
Re: Case Statement not working in Insert statement [message #337861 is a reply to message #337859] Fri, 01 August 2008 06:46 Go to previous messageGo to next message
learn
Messages: 3
Registered: August 2008
Junior Member
JRowBottom

Sorry for that, Initially, I had identical case statement, since that one did not work, I tried other date format and value (12/31/2008) and ('31-DEC-2007')possibilities in my TOAD and then posted here.

Re: Case Statement not working in Insert statement [message #337862 is a reply to message #337861] Fri, 01 August 2008 06:47 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You need to explicitly specify a format mask when you convert a string to a date. If you don't, you've got a bug waiting to happen.
CASE BRTH_DT WHEN TO_DATE('31-DEC-07','DD-MON-RR)
THEN NULL
ELSE TO_CHAR(BRTH_DT,'MM/DD/YYYY')
END
Re: Case Statement not working in Insert statement [message #337880 is a reply to message #337845] Fri, 01 August 2008 08:03 Go to previous messageGo to next message
joy_division
Messages: 4642
Registered: February 2005
Location: East Coast USA
Senior Member
Of course, you make all the to_date and to_chars consistent, but that still doesn't excuse the problem of inserting a string into a DATE column.

Again, it's all assumption due to the lack of a proper description from the original poster. I had to assume BRTH_DT is a DATE, yet a character string is being inserted.

Maybe Oracle should consider making chapter 1 of the concepts manual about how to properly use TO_DATE and TO_CHAR Wink
Re: Case Statement not working in Insert statement [message #337938 is a reply to message #337845] Fri, 01 August 2008 11:49 Go to previous messageGo to next message
learn
Messages: 3
Registered: August 2008
Junior Member
Hi All

Thanks for giving suggestions. I found the bug and fixed it.
Below is the correct code.

INSERT INTO CUST_MST_DTL(BRTH_DT)
SELECT CASE BRTH_DT
WHEN TO_DATE('7/20/2008','MM/DD/YYYY')
THEN NULL
ELSE BRTH_DT
END
FROM CUST_DTL;
Re: Case Statement not working in Insert statement [message #337940 is a reply to message #337938] Fri, 01 August 2008 11:54 Go to previous message
Michel Cadot
Messages: 64131
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
But you still don't read the guidelines and format your post.
You found help here, please follow our rules.

Regards
Michel
Previous Topic: creating and running a trigger
Next Topic: Ratio_to_report analytical function
Goto Forum:
  


Current Time: Wed Dec 07 14:26:57 CST 2016

Total time taken to generate the page: 0.17300 seconds