Home » SQL & PL/SQL » SQL & PL/SQL » insert using Case When
insert using Case When [message #408473] Tue, 16 June 2009 06:26 Go to next message
sumithaswar
Messages: 6
Registered: June 2009
Junior Member
Hi,
I have the following insert script
Insert Into MasterTable (
SELECT a,
TO_NUMBER(b),
c
FROM tempTable T
)
Now, the datatype of the second column in MasterTable is NUMBER and that in tempTable i.e column b is varchar2. I have to write a query such that if col b data can be converted using to_number then insert data whereas for invalid numbers insert NULL.

Thanks
Re: insert using Case When [message #408475 is a reply to message #408473] Tue, 16 June 2009 06:35 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is a FAQ, please search before posting. Keywords: "isnumber" or "is_number" or the like.

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 (4 decimals).

Regards
Michel
Re: insert using Case When [message #408555 is a reply to message #408473] Tue, 16 June 2009 17:58 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
On the front page of orafaq, there is a sql formatter, use it, then when you past the sql into your message, use the CODE tags to retain formatting.

As for your question, last I knew, oracle did not offer a sql function that conditionally converts strings to numbers. You will have to write your own function and use that.

A simple thing. One wonders when oracle will get around to offering a to_number_else_null function of some kind.

Good luck, Kevin
Re: insert using Case When [message #408619 is a reply to message #408555] Wed, 17 June 2009 01:40 Go to previous messageGo to next message
sumithaswar
Messages: 6
Registered: June 2009
Junior Member
thanks for the reply.

my apologies for not following the guidelines, will take care from onwards.

I got a solution in asktom and got the following function:
CREATE OR REPLACE FUNCTION Isnumeric 
     (p_string  IN VARCHAR2) 
RETURN BOOLEAN 
AS 
  l_number  NUMBER; 
BEGIN 
  l_number := p_string; 
   
  RETURN true; 
EXCEPTION 
  WHEN OTHERS THEN 
    RETURN false; 
END;

Thanks

[EDITED by LF: applied [code] tags]

[Updated on: Wed, 17 June 2009 01:46] by Moderator

Report message to a moderator

Re: insert using Case When [message #408621 is a reply to message #408619] Wed, 17 June 2009 01:44 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
my apologies for not following the guidelines, will take care from onwards.

And you didn't just 2 lines after that.
Quote:
Use code tags.
Use the "Preview Message" button to verify.

Really use this button.

Regards
Michel
Re: insert using Case When [message #408736 is a reply to message #408621] Wed, 17 June 2009 07:36 Go to previous message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
That is OK, just read up on the posting page about how to use the formatting tools. The line above where you type has a series of buttons. Hover over them and see what they do. Iin particular is the {..} button, this does the CODE tags. See how your code stays formatted with the.

As for the solutions, way to go. Good job in searching for a solution on your own. We need more people who are willing to do this.

I however prefer a version of the function that either returns the value or null for two reasons.

1) it is usuable from sql directly
2) it is usually what people want, to either get the numeric version or ignore the value.

CREATE OR REPLACE FUNCTION Isnumeric (p_string  IN VARCHAR2) return number is
  l_number  NUMBER; 
BEGIN 
  begin
     l_number := p_string; 
  EXCEPTION 
    WHEN OTHERS THEN 
     l_number := null;
  end;
  return (l_number); 
END;


I did not compile this thing so you are up to fix any syntax errors and test it.
Good luck, Kevin
Previous Topic: Spilliting row
Next Topic: Put row results to column format
Goto Forum:
  


Current Time: Tue Dec 06 10:37:29 CST 2016

Total time taken to generate the page: 0.10020 seconds