I have an Idt universe version XI 4.2, there is a standalone table that fetches 1 row depending on the user environment. I need to incorporate a udf (backend is Oracle) into my universe
Now,there are 4 parameters for this function of which 2 are from the standalone table remaining 2 are from the lookup tables which are from the same context. The standalone table objects cannot be used as there are no joins.
I created a derived table for the standalone table along with a dummy column called key with it’s value as 1. Then in one of the lookup tables Date table precisely, since it has less no of rows and only one column from this table is used in the report
In this table i.e. the date table mentioned above i inserted a calculated column - select 1 from dual, and then joined this table with my derived table using the key column.
The report fetches the data but i would like to know,if there is a better approach ,because the query does a full scan on the date table.
So going down the line this may not be very appropriate.
Please note that we need to use the universe and not develop a FHS report.
Does the value from this standalone table need to link to anything in the report or does it just need to be displayed on the report?
You could return the value from the stand alone table in a separate query.
The standalone table’s objects are called in the function so they cannot be used in a separate query. Also, i tried enabling catersian product but that takes very long.
I’m not familiar with out User Defined Functions work in database, especially in Oracle, but couldn’t you make the call to your value in the stand alone table from the function within the database instead of accessing the value in the universe? Seems to me it would be easier, and maybe more efficient, to have as much of the functionality of the UDF take place in the database as possible.