I have an Oracle table for which I am creating a Master File Description. I used the CREATE SYNONYM function in the WebFOCUS Server to create the MFDs and ACXs. My problem is that the Date/Time fields in the Oracle table are a NUMERIC(15) format, not a DATE. The Oracle Date/Time field contains the number of seconds elapsed since Jan 01 1970 (Oracles base Date?).

What is the best way (with the least amount of DEFINEs) to describe this field in an MFD and make the correct date appear on reports?

I can divide by total seconds in a day (60*60*24) to get the number of days elapsed and then assign this to a smart date field. I then add 25203 days to adjust the smart date because the FOCUS base date is Dec 31 1900. I got 25203 by subtracting Dec 31 1900 from Jan 01 1970.

I am having issues with this method because if I try to do a WHERE clause with the DEFINEd Date field, WebFOCUS will either pass incorrect SQL to Oracle OR it will NOT pass the SQL and process the selection itself which we all know would require passing all the records back to WebFOCUS (very inefficient)

Any ideas are greatly appreciated!

(Please see my signature information below for which version of WebFOCUS is our PRD environment.)

Not sure Mickey as I don't use Oracle currently, but have you thought about changing the date selected into seconds since base and using that in your SQL selection? If you have many modules that require this a standard include would suit.

I know that sounds like teaching Granny etc. but when we're focused on a problem we often forget to look from another angle!!

Tony, Thanks for the suggestion. I was hoping to be able to just write a WHERE clause comparing the DATE portion only instead of having to deal with the TIME piece. When converting FOCUS Dates to number of seconds, the time will always be 12:00:00 am. I am using the current date for comparison to find records from the Oracle table. Instead of saying is Oracle Date EQ the current date, I need to say is the Oracle Date GE the current date and LE tomorrows date. I can get the results I am looking for but it is messy. I was hoping for a more elegant solution that pushes the WHERE clauses to Oracle.

I need to fiddle with it more.

Any other suggestions from anyone?

Well, here is what I came up with. I created to field definitions with a format of P16 in the master for the Oracle table fields that contain the seconds. This is what the CREATE SYNONYM chose for the formats so I left them as is.

FIELD=ASGND_SCNDS ,C300751500 ,P16 ,P8 ,$
FIELD=RSLVD_SCNDS ,C300751700 ,P16 ,P8 ,$

I then added DEFINE fields to the master to create Smart Date format fields. I call a FUNCTION that takes seconds as an input and gives a Smart Date as an output.

DEFINE ASGND_DT/MDYY =ACTLDATE(ASGND_SCNDS); $
DEFINE RSLVD_DT/MDYY =ACTLDATE(RSLVD_SCNDS); $

The FUNCTION is defined by using the DEFINE FUNCTION syntax. WebFOCUS 5.1 added the DEFINE FUNCTION feature. This is the first time I had a real application for this feature and it works quite nicely. I put this DEFINE FUNCTION code in the profile for the application so it is setup and available for each request.

DEFINE FUNCTION ACTLDATE (ORA_SCNDS/P16)
FOC_SCNDS/P16=ORA_SCNDS + ((60*60*24) * 25203);
FOC_DATE/MDYY=FOC_SCNDS / (60*60*24);
ACTLDATE/MDYY=FOC_DATE;
END

The down side is that all the records need to be brought over to WebFOCUS in order to process the WHERE clause on the Smart Date fields. If response time becomes unreasonable I will convert the date being selected to total seconds and change my WHERE clause to make it translate into the SQL passed to Oracle.

I also have a case open with IBI to see if there is a better way to do this.

Neat solution Mickey!! but, as you state, it still needs the data into WebFOCUS first so the SQL will be iffy efficiency wise.

Like you I have yet to find a use for the function definitions but now you have mentioned this one perhaps I can see a few solutions to problems that have been bugging me for a while.

Good luck with your app!

T

Mickey,

Maybe I'm missing something here, but the standard Oracle DATE format is, in fact, a Date-Time field (just like MS SQL*Server) and stores time data down to the second. According to my Oracle manual,

quote:

Date data is stored in fixed-length fields of seven bytes each, corresponding to century, year, month, day, hour, minute, and second.

If you do a standard CREATE SYNONYM against a standard Oracle DATE field you get an ACTUAL and FORMAT of HYYMDS.

It sounds to me like you are using a non-standard format to store your date-time info. In this situation, my recommendation would be to convert the data itself into standard Oracle DATE format. If that is out of the question, then the next approach I would try would be to create a view at the Oracle level which materializes a standard Oracle DATE field.

EricH

EricH,

Unfortunately, this is an Off the Shelf application that uses Oracle (and other RDBMS) to store it's data. I, nor my organization, designed the database. The format in the table is NUMBER(15) not DATE so the SYNONYM created field definitions with P16, P8 appropriately. Creating a view might be a possibility but I will need to discuss that with our DBAs.

Thanks!

One thing that would help me is if I could somehow tell WebFOCUS in the MFD to apply the todate() Oracle function to the field in the SQL it passes to Oracle when I reference the field. One of the "other" reporting tools that we use allows you to define the SQL that should be sent to the RDBMS when a field is referenced, such as applying the todate() function. This allows us to do something like DEFINEing an ELAPSED_DAYS field which is equal DATE1 - DATE2 and push the calculation off to Oracle as opposed to pulling DATE1 and DATE2 values back to FOCUS and then calculating. WebFOCUS works fine in the way it does something like this but being able to push as much of the work over to the RDBMS is more efficient.