| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Create view using CAST to change datatype
Write a function and use it in your view: my_function(OB_OID).
In the function convert OB_OID to NUMBER:
to_number(decode(OB_OID, ' ', null, OB_OID))
and have exception handler (for ORA-01722) in the function, which will
return NULL, when exception happens.
Igor Neyman, OCP DBA
ineyman_at_perceptron.com
-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Saira
Somani-Mendelin
Sent: Friday, March 19, 2004 10:35 AM
To: oracle-l_at_freelists.org
Subject: RE: Create view using CAST to change datatype
I'm not sure why I'm leaning towards CAST...(I claim ignorance!)
In any case, I tried methods, both yours and Igor's, and received the same error:
ORA-01722: invalid number
Somewhere in this table, there is a strange value. I was trying to make life simpler without having to create multiple views to deal with these data value issues (filter the view by some criteria).
So here's another question (just out of curiosity), how can I determine which value is causing me problems?
Thanks for all your help so far! I appreciate the time. Saira
-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Mercadante, Thomas F
Sent: March 19, 2004 10:10 AM
To: 'oracle-l_at_freelists.org'
Subject: RE: Create view using CAST to change datatype
Saira,
This worked:
CREATE OR REPLACE VIEW tomview
AS
SELECT
col1, col2,
cast(NVL(RTRIM(col1),0) AS NUMBER) col1_numb
FROM tomtest
Values for the TOMTEST table were (' ',1);
SELECT * FROM tomview
returns
values of (' ', 1, 0)
And I agree wih Igor - why are you using CAST rather than to_number?
Tom Mercadante
Oracle Certified Professional
 |
 |