insert using Case When [message #408473] |
Tue, 16 June 2009 06:26  |
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 #408619 is a reply to message #408555] |
Wed, 17 June 2009 01:40   |
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 #408736 is a reply to message #408621] |
Wed, 17 June 2009 07:36  |
 |
Kevin Meade
Messages: 2103 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
|
|
|