Emiliano
Member
Dear all,
Im' experiencing some issues regarding the conversion of the JDE Julian Date and the Materialized View.
I've a Datawarehouse which consists in several Materialized View which refers to the tables in the production database.
The materialized view should convert the origianal julian date to the format dd/mm/yyyy.
For this purpose I tried to use these two functions:
#FUNCTION 1
create or replace
function jde2date( jd_date in number ) return char
is
thedate char(9) default ' ';
begin
if jd_date <> 0 then
thedate := to_char(to_date((to_char(jd_date+1900000)),'yyyyddd'));
end if;
return thedate;
end;
/
#FUNCTION2
CREATE OR REPLACE FUNCTION JDE2Date (jdedate IN NUMBER)
RETURN DATE
IS
v_dateout DATE;
v_jdedate NUMBER(6,0) :=0;
v_year NUMBER(4,0) :=0;
v_day NUMBER(3,0) :=0;
BEGIN
v_dateout := TO_DATE(19000101,'YYYY MM DD');
if (jdedate IS NOT NULL) AND (jdedate != 0) then
v_jdedate :=jdedate;
v_year := (v_jdedate / 1000) + 1900;
v_day := MOD(v_jdedate,1000) - 1;
v_dateout := TRUNC(TO_DATE(v_year,'YYYY'),'YEAR') + v_day;
end if;
RETURN (v_dateout);
END;
they work both except in case the julian date is = 0
If the Julian Date in the database table is = 0, the Refresh JOB of the materialized view return an error:
Tue Apr 24 09:39:44 2007
Errors in file /dwh/admin/dwica/udump/dwica_ora_14272.trc:
ORA-12012: error on auto execute of job 68
ORA-12008: error in materialized view refresh path
ORA-01847: day of month must be between 1 and last day of month
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 803
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 860
ORA-06512: at "SYS.DBMS_IREFRESH", line 683
ORA-06512: at "SYS.DBMS_REFRESH", line 195
ORA-06512: at line 1
It seems that the functions are not able to convert the date from julian to dd/mm/yyyy for values = 0.
This is strange because if I try to use the function manually, like for instance:
SELECT JDETODATE(RPDIVJ) AS RPDIVJ FROM PRODDTA.F74412
it works good.
Thanks a lot for your help.
Emiliano
Im' experiencing some issues regarding the conversion of the JDE Julian Date and the Materialized View.
I've a Datawarehouse which consists in several Materialized View which refers to the tables in the production database.
The materialized view should convert the origianal julian date to the format dd/mm/yyyy.
For this purpose I tried to use these two functions:
#FUNCTION 1
create or replace
function jde2date( jd_date in number ) return char
is
thedate char(9) default ' ';
begin
if jd_date <> 0 then
thedate := to_char(to_date((to_char(jd_date+1900000)),'yyyyddd'));
end if;
return thedate;
end;
/
#FUNCTION2
CREATE OR REPLACE FUNCTION JDE2Date (jdedate IN NUMBER)
RETURN DATE
IS
v_dateout DATE;
v_jdedate NUMBER(6,0) :=0;
v_year NUMBER(4,0) :=0;
v_day NUMBER(3,0) :=0;
BEGIN
v_dateout := TO_DATE(19000101,'YYYY MM DD');
if (jdedate IS NOT NULL) AND (jdedate != 0) then
v_jdedate :=jdedate;
v_year := (v_jdedate / 1000) + 1900;
v_day := MOD(v_jdedate,1000) - 1;
v_dateout := TRUNC(TO_DATE(v_year,'YYYY'),'YEAR') + v_day;
end if;
RETURN (v_dateout);
END;
they work both except in case the julian date is = 0
If the Julian Date in the database table is = 0, the Refresh JOB of the materialized view return an error:
Tue Apr 24 09:39:44 2007
Errors in file /dwh/admin/dwica/udump/dwica_ora_14272.trc:
ORA-12012: error on auto execute of job 68
ORA-12008: error in materialized view refresh path
ORA-01847: day of month must be between 1 and last day of month
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 803
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 860
ORA-06512: at "SYS.DBMS_IREFRESH", line 683
ORA-06512: at "SYS.DBMS_REFRESH", line 195
ORA-06512: at line 1
It seems that the functions are not able to convert the date from julian to dd/mm/yyyy for values = 0.
This is strange because if I try to use the function manually, like for instance:
SELECT JDETODATE(RPDIVJ) AS RPDIVJ FROM PRODDTA.F74412
it works good.
Thanks a lot for your help.
Emiliano