As often in practice the real world is more complex:6.1 EVALUATE Function
This function is intended for scalar and analytic
calculations.
Syntax: EVALUATE('DB_Function(%1)', {
Comma separated Expression})
Example: SELECT
e.lastname,sales.revenue,EVALUATE('dense_rank()
over(order by %1
)',sales.revenue) FROM sales s, employee e;
Good practice is to tell the EVALUATE function which type of return value it can expect:
EVALUATE('DB_Function(%1)' as returntype, {Comma separated Expression}). If you omit this it might sometimes complain that it can't concatenated values from a different type.
Another trap is when you use columns from several tables for the input variables, be sure that they have a direct join. Else OBIEE will try to join them underwater which can lead to unwanted Cartesian products.
Can You Please Explain More on these EVALUATE functions like where it can be used, predicates and aggregates.
ReplyDeleteThanks
Hi Raghev,
ReplyDeleteThe EVALUATE and EVALUATE_AGGR functions can be used to execute a function on the underlying database. These can be on a rowlevel (SIN(COLUMN_X)) and on a aggregate level (SUM(COLUMN_X) over (COLUMN_Y)) The syntax is completly shipped to the underlying database and is database specific.
regards
John
How I can see EVALUATE Function start from Oracle BI version 10.1.3.3.
ReplyDeleteIn this case EVALUATE Function not use in Oracle BI SE One 10.1.3.2.1. Is it true???