| 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
Hello Saira,
If I understand this well, your columns OB_OIB and IB_OID are presumed not to contain non_numeric values.
To find them, you can go along the following example.
create table test (c varchar2(10));
insert into test values (' ');
insert into test values ('1234');
insert into test values ('5678');
insert into test values ('90');
insert into test values ('a');
select c
from test
where translate(c, '0123456789 ', '...........') != RPAD('.', LENGTH(C), '.');
C
The translate functions maps every character of the first argument (column 'C' in this case), if and only if it occurs in the second argument, to the character with same position in the third argument. So, the '0' gets mapped to the first '.', the '1' to the second dot etc. Unspecified characters remain unchanged.
By mapping all allowed characters to just one known character (dot in this case) values containing other characters than the allowed character will not meet the criterium that it should contain only dots. I hope this explanation doesn't confuse you more than te example itself. It allows you to detect the values, and, if added to the select clause, of course also the primary key of the rows that cause the problem, correct them, get rid of them or exclude them from the view.
Regards, Carel-Jan
===
If you think education is expensive, try ignorance. (Derek Bok)
===
At 05:40 PM 3/19/2004, you wrote:
>I should also mention that along with the ORA-01722 error, I am also
>receiving the ORA-01002: Fetch out of sequence error.
>
>FYI, we are using version 8.1.7.4 and I am using TOAD on w2k pro.
>
>-----Original Message-----
>From: oracle-l-bounce_at_freelists.org
>[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Saira
>Somani-Mendelin
>Sent: 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
>
>----------------------------------------------------------------
>Please see the official ORACLE-L FAQ: http://www.orafaq.com
>----------------------------------------------------------------
>To unsubscribe send email to: oracle-l-request_at_freelists.org
>put 'unsubscribe' in the subject line.
>--
>Archives are at http://www.freelists.org/archives/oracle-l/
>FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
>-----------------------------------------------------------------
>
>----------------------------------------------------------------
>Please see the official ORACLE-L FAQ: http://www.orafaq.com
>----------------------------------------------------------------
>To unsubscribe send email to: oracle-l-request_at_freelists.org
>put 'unsubscribe' in the subject line.
>--
>Archives are at http://www.freelists.org/archives/oracle-l/
>FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
>-----------------------------------------------------------------
-- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------Received on Fri Mar 19 2004 - 11:09:09 CST
 |
 |