Home » SQL & PL/SQL » SQL & PL/SQL » ora-01722 when using to_number (merged)
ora-01722 when using to_number (merged) [message #268740] |
Wed, 19 September 2007 09:42  |
sandudana
Messages: 48 Registered: March 2006
|
Member |
|
|
Hello everybody,
Database version: Oracle 10gR2
I have this views:
CREATE OR REPLACE VIEW V_ESAMI
(MATRICOLA, SCD_DWT_ANA_STD, C_INS, AA_ESAME, C_SESSIONE,
DT_ESAME, VOTO, F_LODE, GIUDIZIO, AA_FREQ,
ANNO_CORSO, UNIV_SOST, C_CDL_STD, C_IND_STD, C_CDL_INS,
TIPO_ESAME, SOST_ESAME, PROVENIENZA, D_CUSTOM1, D_CUSTOM2,
CFU, ACORSO_RIF, COD_PST_RIF, N_FC_RIF, COD_ATENEO,
STATO_ESA, COD_ERR_ESA)
AS
select "MATRICOLA","SCD_DWT_ANA_STD","C_INS","AA_ESAME","C_SESSIONE",
"DT_ESAME","VOTO","F_LODE","GIUDIZIO","AA_FREQ","ANNO_CORSO","UNIV_SOST",
"C_CDL_STD","C_IND_STD","C_CDL_INS","TIPO_ESAME",
"SOST_ESAME","PROVENIENZA","D_CUSTOM1","D_CUSTOM2","CFU",
"ACORSO_RIF","COD_PST_RIF","N_FC_RIF","COD_ATENEO","STATO_ESA","COD_ERR_ESA"
from dwm.DWT_ESAMI
union all
select "MATRICOLA",to_number("SCD_DWT_ANA_STD"),"C_INS","AA_ESAME","C_SESSIONE",
to_date("DT_ESAME",'yyyymmdd'),to_number(voto),"F_LODE","GIUDIZIO",
"AA_FREQ",to_number("ANNO_CORSO"),"UNIV_SOST",
"C_CDL_STD","C_IND_STD","C_CDL_INS","TIPO_ESAME",
"SOST_ESAME","PROVENIENZA","D_CUSTOM1","D_CUSTOM2",to_number("CFU"),
to_number("ACORSO_RIF"),"COD_PST_RIF",to_number("N_FC_RIF"),"COD_ATENEO",
"STATO_ESA","COD_ERR_ESA"
from dwh.DWT_ESAMI_SOST_RAW
union all
select "MATRICOLA","SCD_DWT_ANA_STD","C_INS","AA_ESAME","C_SESSIONE","DT_ESAME","VOTO",NULL, "GIUDIZIO","AA_FREQ","ANNO_CORSO",NULL,
"C_CDL_STD","C_IND_STD","C_CDL_INS",NULL,
NULL, "PROVENIENZA","D_CUSTOM1","D_CUSTOM2",NULL,
NULL,NULL,NULL,NULL,NULL,NULL
from dwm.DWT_ESAMI_NEG
union all
select "MATRICOLA",to_number("SCD_DWT_ANA_STD"),"C_INS","AA_ESAME","C_SESSIONE",to_date("DT_ESAME",'yyyymmdd'),to_number(voto), NULL, "GIUDIZIO","AA_FREQ",to_number("ANNO_CORSO"),NULL,
"C_CDL_STD","C_IND_STD","C_CDL_INS",NULL,
NULL, "PROVENIENZA","D_CUSTOM1","D_CUSTOM2",NULL,
NULL,NULL,NULL,NULL,NULL,NULL
from dwh.DWT_ESAMI_NEG_SOST_RAW;
In tables DWT_ESAMI e DWT_ESAMI_NEG, columns VOTO e CFU are of type NUMBER.
In tables DWT_ESAMI_SOST_RAW e DWT_ESAMI_NEG_SOST_RAW, columns VOTO e CFU are of type VARCHAR2, this is why I used TO_NUMBER to convert them to NUMBER.
When I do an 'select * from v_esami' in SQL or TOAD everything is OK.
Why I try to create a view, or another table or a materialzed view from V_ESAMI:
create or replace view v_test as select * from v_esami;
create table t_test as select * from v_esami;
... I get ora-01722 error
If I do a
create or replace view v_test as select matricola from v_esami;
the view is created.
As sson as I add the filds CFU e VOTO, I get the ora-01722.
Why?
Any help will be very appreciated.
Thank you!
Daniela
|
|
|
|
|
ora-01722 when using to_number (formatted version) [message #268744 is a reply to message #268740] |
Wed, 19 September 2007 10:16   |
sandudana
Messages: 48 Registered: March 2006
|
Member |
|
|
Hello everybody,
Sorry for the previous unformatted post . Here it is the formatted version.
Database version: Oracle 10gR2
I have this view:
CREATE OR REPLACE VIEW V_ESAMI
(MATRICOLA, SCD_DWT_ANA_STD, C_INS, AA_ESAME, C_SESSIONE,
DT_ESAME, VOTO, F_LODE, GIUDIZIO, AA_FREQ,
ANNO_CORSO, UNIV_SOST, C_CDL_STD, C_IND_STD, C_CDL_INS,
TIPO_ESAME, SOST_ESAME, PROVENIENZA, D_CUSTOM1, D_CUSTOM2,
CFU, ACORSO_RIF, COD_PST_RIF, N_FC_RIF, COD_ATENEO,
STATO_ESA, COD_ERR_ESA)
AS
select "MATRICOLA","SCD_DWT_ANA_STD","C_INS","AA_ESAME","C_SESSIONE",
"DT_ESAME","VOTO","F_LODE","GIUDIZIO","AA_FREQ","ANNO_CORSO","UNIV_SOST",
"C_CDL_STD","C_IND_STD","C_CDL_INS","TIPO_ESAME",
"SOST_ESAME","PROVENIENZA","D_CUSTOM1","D_CUSTOM2","CFU",
"ACORSO_RIF","COD_PST_RIF","N_FC_RIF","COD_ATENEO","STATO_ESA","COD_ERR_ESA"
from dwm.DWT_ESAMI
union all
select "MATRICOLA",to_number("SCD_DWT_ANA_STD"),"C_INS","AA_ESAME","C_SESSIONE",
to_date("DT_ESAME",'yyyymmdd'),to_number(voto),"F_LODE","GIUDIZIO",
"AA_FREQ",to_number("ANNO_CORSO"),"UNIV_SOST",
"C_CDL_STD","C_IND_STD","C_CDL_INS","TIPO_ESAME",
"SOST_ESAME","PROVENIENZA","D_CUSTOM1","D_CUSTOM2",to_number("CFU"),
to_number("ACORSO_RIF"),"COD_PST_RIF",to_number("N_FC_RIF"),"COD_ATENEO",
"STATO_ESA","COD_ERR_ESA"
from dwh.DWT_ESAMI_SOST_RAW
union all
select "MATRICOLA","SCD_DWT_ANA_STD","C_INS","AA_ESAME",
"C_SESSIONE","DT_ESAME","VOTO",NULL, "GIUDIZIO","AA_FREQ",
"ANNO_CORSO",NULL,
"C_CDL_STD","C_IND_STD","C_CDL_INS",NULL,
NULL, "PROVENIENZA","D_CUSTOM1","D_CUSTOM2",NULL,
NULL,NULL,NULL,NULL,NULL,NULL
from dwm.DWT_ESAMI_NEG
union all
select "MATRICOLA",to_number("SCD_DWT_ANA_STD"),"C_INS","AA_ESAME","C_SESSIONE",
to_date("DT_ESAME",'yyyymmdd'),to_number(voto), NULL, "GIUDIZIO","AA_FREQ",
to_number("ANNO_CORSO"),NULL,
"C_CDL_STD","C_IND_STD","C_CDL_INS",NULL,
NULL, "PROVENIENZA","D_CUSTOM1","D_CUSTOM2",NULL,
NULL,NULL,NULL,NULL,NULL,NULL
from dwh.DWT_ESAMI_NEG_SOST_RAW;
In tables DWT_ESAMI e DWT_ESAMI_NEG, columns VOTO e CFU are of type NUMBER.
In tables DWT_ESAMI_SOST_RAW e DWT_ESAMI_NEG_SOST_RAW, columns VOTO e CFU are of type VARCHAR2, this is why I used TO_NUMBER to convert them to NUMBER.
When I do a
in SQL or TOAD everything is OK.
Why I try to create a view, or another table or a materialized view from V_ESAMI:
create or replace view v_test as select * from v_esami;
create table t_test as select * from v_esami;
... I get ora-01722 error
If I do a
create or replace view v_test as select matricola from v_esami;
the view is created.
As soon as I add fields CFU e VOTO, I get the ora-01722.
Why?
Any help will be very appreciated.
Thank you!
Daniela
[Updated on: Wed, 19 September 2007 12:37] by Moderator Report message to a moderator
|
|
|
|
Re: ora-01722 when using to_number (formatted version) [message #268753 is a reply to message #268745] |
Wed, 19 September 2007 10:39   |
sandudana
Messages: 48 Registered: March 2006
|
Member |
|
|
Database version: Oracle 10.2.0.2.0
I have this view:
CREATE OR REPLACE VIEW V_ESAMI
(MATRICOLA, some_fields_1, VOTO,CFU, some_fields_2)
AS
select "MATRICOLA", some_fields_1,
"VOTO","CFU",
some_fields_2
from dwm.DWT_ESAMI
union all
select "MATRICOLA", some_fields_1,
to_number(voto) "VOTO",to_number("CFU") "CFU",
some_fields_2
from dwh.DWT_ESAMI_SOST_RAW
union all
select "MATRICOLA", some_fields_1,
"VOTO", NULL "CFU",
some_fields_2
from dwm.DWT_ESAMI_NEG
union all
select "MATRICOLA",some_fields_1,
to_number(voto) "VOTO", NULL "CFU",
some_fields_2
from dwh.DWT_ESAMI_NEG_SOST_RAW;
In tables DWT_ESAMI e DWT_ESAMI_NEG, columns VOTO e CFU are of type NUMBER.
In tables DWT_ESAMI_SOST_RAW e DWT_ESAMI_NEG_SOST_RAW, columns VOTO e CFU are of type VARCHAR2, this is why I used TO_NUMBER to convert them to NUMBER.
When I do a
in SQL or TOAD everything is OK.
Why I try to create a view, or another table or a materialized view from V_ESAMI:
create or replace view v_test as select * from v_esami;
create table t_test as select * from v_esami; ... I get ora-01722 error
I know that ora-01722 means invalid number, cause: Quote: | The attempted conversion of a character string to a number failed because the character string was not a valid numeric literal
| . I know the error is because of the CFU and VOTO fields.
I just want to understand why is behaving like this, because (as stated above) when doing select * from v_esami; or select cfu, voto from v_esami I get no errors!
Thank you!
Daniela
|
|
|
Re: ora-01722 when using to_number (formatted version) [message #268922 is a reply to message #268753] |
Thu, 20 September 2007 02:07  |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
When you do a SELECT * FROM .... in TOAD or SQL Developer, it doesn't get all the records - it just gets the first 100 or so rows.
Try doing select * from v_esami order by 1 desc this should fetch all the rows in order to do the sort.
I suspect that that will error.
You could also try something like this. Create a function to check if a value is a number:CREATE OR REPLACE FUNCTION is_number(p_vc in varchar2) return varchar2 as
v_num number;
begin
v_num := to_number(p_vc);
return 'Y';
exception
when others then
return 'N';
end;
/
Then use this function to check the data in the tables DWT_ESAMI_SOST_RAW and DWT_ESAMI_NEG_SOST_RAW - one of these must have a value in it that errors when converted into a number.
SELECT VOTO
,is_number(voto)
,CFU
,is_number(cfu)
FROM DWT_ESAMI_SOST_RAW
WHERE is_number(voto) = 'N' or is_number(cfy) = 'N'
SELECT VOTO
,is_number(voto)
,CFU
,is_number(cfu)
FROM DWT_ESAMI_NEG_SOST_RAW
WHERE is_number(voto) = 'N' or is_number(cfy) = 'N'
|
|
|
Goto Forum:
Current Time: Sat Feb 15 02:27:21 CST 2025
|