Just a warning over a nice bug that I have just discovered (BO4.1.2 on Oracle7.3.3)
I have an object ‘ICDP Metric - Plan Cycle Time Maximum’ based on an Oracle function called ‘fnc_icdp_metrics_minmax’.
The function returns either the min or the max value from a table after doing a few calcs depending on the input parameters supplied to the function - hence the name (no need to explain anything else about this).
Anyway, the function happily compiles and is Ok to use in SQL*Plus.
The problems start when using the function based object in the query panel.
Example :-
Objects (both dimension objects)
ICDP Title
ICDP Metric - Plan Cycle Time Maximum
The correct SQL to be generated for this is
Select
MIP_ICDP.ICDP_DESC,
fnc_icdp_metrics_minmax(MIP_ICDP,ICDP_ID,‘PLAN’,‘MAX’) from
MIP_ICDP
Whereas The SQL that BO generates is
Select
MIP_ICDP.ICDP_DESC,
fnc_icdp_metrics_minmax(MIP_ICDP,ICDP_ID,‘PLAN’,‘MAX’) from
MIP_ICDP
GROUP BY
MIP_ICDP.ICDP_DESC
which of course won’t work because a) it’s incorrect! and b) the fnc_… is not a group by clause.
What appears to be happening is that BusObj checks the function name, sees that it has the 3 letters ‘max’ before a bracket and decides that it therefore needs a ‘Group By’ clause. The fact that there are no spaces before the ‘max’ and that it is not being used in a ‘reserved’ word way seems to make no difference to BusObj.
Solution
Rename the function to anything else and it’s fine.
Therefore be careful with names of underlying database objects even if they are fine in SQL*Plus etc.
I am logging this with tech support as a bug.
Regards
Jonathan
Project Leader
Global Medical, Regulatory and Product Strategy (GMRPS) IS
Listserv Archives (BOB member since 2002-06-25)