Question:
I do insert /*+APPEND*/ into
new_table select <convert datatypes here> from
old_table;
but the right types is DATE and
in old table the types is VARCHAR ,
The problem is when i do select
with convert to columns i get this error
select to_date(time_opened,'MM
DD YYYYhh24:mi:ss') from tec.tec_t_archive;
*
ERROR at line 1:
ORA-01858: a non-numeric
character was found where a numeric was expected
I see much DATE format in this
columns like this
MM DD YYYYhh24:mi:ss
MM/DD/YYYY hh24:mi:ss
YYYY/MM/DD hh24:mi:ss
How I can see that all formats
to date in this columns -
And how can to do insert with
decode to all tables ?
Answer from Manfred:
I'm afraid that you have to analyze your data,
probably you can select distinct
substr(TIME_OPENED,1,8) to see all possible formats.
ORA-01858: a non-numeric character
was found where a numeric was expected
Cause: The input data to be
converted using a date format model was incorrect.
The input data did not contain a number where a
number was required by the format model.
Action: Fix the input data or the
date format model to make sure the elements match in
number and type. Then retry the operation.
The
2nd part is more difficult. I would write a function
where I'm looking for a scheme in each found format
and return a code which you can decode.
|
CODE
|
|
CREATE FUNCTION data_test (check_var
varchar2) return number
as
rc number;
BEGIN
if substr(check_var,3,1) = '/' then
return 1 end if;
if substr(check_var,3,1) = ' ' then
return 2 end if;
if ....
else return 99;
end if;
END;
/
|
and then:
|
CODE
|
|
insert /*+APPEND*/ into new_table select
...,
decode(data_test(TIME_OPENED),
1,to_date(TIME_OPENED,'MM/DD/YYYY
hh24:mi:ss'),
2,to_date(TIME_OPENED,'MM DD YYYY
hh24:mi:ss'),
...,
to_date('12319999','MMDDYYYY') )
--<default date> or null
;
|
..or something like this... |
and include the URL for the page.
