Materialized view and the JULIAN DATE

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
 
I noticed that your test query has this function name [JDETODATE] but your function definition shows [JDE2DATE]. Is it possible you have yet another conversion function out there?
 
I am not as familiar with newer versions of Oracle as I'd like to be, but can you overload a function like that without putting it in a package? I believe the answer is no, in which case, you will only be using one of those functions.

Moreover, if your function is returning a string to a date field, the database will implicitly convert it to date. If the character string is not in the default date format of the database, the database will most likely throw that error, telling you that the day of the month is not valid. Does that help at all?
 
The problema is only when the field is equal to "0". In all other cases it works good. I don't understand why it works if i try to use these function manually but not if I try to them in the materialized view!. This is strange, isn't it?

Emiliano
 
From a Datawarehouse standpoint - it might be better to leave the data
intact (don't monkey with a convert) and create a 'Date Table'... The Date
Table would contain the all the JDE Dates from here to eternity (or from the
beginning of golive to when you expect to turn off the company's lights) and
MM/DD/YYYY conversion (and or any others).

From a DW standpoint - it simplifies a whole bunch of things. Instead of
providing the Table to the users, provide the View and replace the JDE Date
with the 'converted date' from the Date Table... on JDE Tables that contain
'lots of dates' you would link to the Date Table for each case....

You call - ... I call it simplicity... but, then... there is... probably...
a resone I am not a DBA of a zillion table-row DW.

(db)
--
 
Dear DRBohner,
it could be really a good solution.
If I underastood well, I have to create a table convertion, like for instance:

JDATE|CDATE
-----------
104306|01/11/2004
104307|02/11/2004
104308|03/11/2004
104309|04/11/2004
104310|05/11/2004

then I will create a VIEW which refers to the materialized view except for the DATA field:

SELECT RPDAG, CDATE AS RPDIVJ
FROM PRODDTA.F74412 JOIN PRODDTA.JDATECONV
ON RPDIVJ = JDATE;

it should works. Anyway I've the following question:

how I can create a DATE table conversion easly?
I mean; I already created the table:

CREATE TABLE PRODDTA.JDATECONV (
JDATE NUMBER(6),
CDATE DATE
);

but I cannot spent my whole life inserting each rows one by one:

INSERT INTO PRODDTA.JDATECONV VALUES (104306, '01-NOV-2004');
INSERT INTO PRODDTA.JDATECONV VALUES (104307, '02-NOV-2004');
INSERT INTO PRODDTA.JDATECONV VALUES (104308, '03-NOV-2004');
INSERT INTO PRODDTA.JDATECONV VALUES (104309, '04-NOV-2004');
INSERT INTO PRODDTA.JDATECONV VALUES (104310, '05-NOV-2004');

There is any automatic procedure to do that?

Thanks for your precious help.

Emiliano
 
[ QUOTE ]
INSERT INTO PRODDTA.JDATECONV VALUES (104306, '01-NOV-2004');
INSERT INTO PRODDTA.JDATECONV VALUES (104307, '02-NOV-2004');
INSERT INTO PRODDTA.JDATECONV VALUES (104308, '03-NOV-2004');
INSERT INTO PRODDTA.JDATECONV VALUES (104309, '04-NOV-2004');
INSERT INTO PRODDTA.JDATECONV VALUES (104310, '05-NOV-2004');

There is any automatic procedure to do that?

[/ QUOTE ]
<font class="small">Code:</font><hr /><pre>
for i in 100001 to 200001 loop

INSERT INTO PRODDTA.JDATECONV VALUES (i,jde2date(i));

end loop;
</pre><hr />
 
Hi,

trying this solution I've find out the matter.
The convertion function did not work because I had another field with the julian data = 0 and I did not know.
I solved the problem converting all data fields using the JDE2DATE funciton and DECODE to set to NULL the julian data in case it's equal to 0, like for instance:

TO_DATE(DECODE(JDE2DATE(RPDGJ), ' ',NULL ,JDE2DATE(RPDGJ)), 'DD/MM/YY') AS RPDGJ

thanks to evrybody for your support.

Emiliano
 
Back
Top