В Oracle Business Intelligence Enterprise Edition не так давно появилась замечательная возможность вызывать родные функции СУБД. Это можно сделать с помощью следующих функций:

• EVALUATE – позволяет вызывать любую скалярную функцию (возвращающую одно значение) СУБД;
• EVALUATE_AGGR- позволяет вызывать агрегирующие функции СУБД;
• EVALUATE_PREDICATE- позволяет вызывать функции СУБД, которые возвращают булевы значения.

Синтаксис этих функций следующий:

• EVALUATE(’[schema].[package].[function](%1,%2,…)’ as TYPE,param1,param2,…)
• EVALUATE_AGGR(’[schema].[package].[function](%1,%2,…)’ as TYPE,param1,param2,…)
• EVALUATE_PREDICATE(’[schema].[package].[function](%1,%2,…)’,param1,param2,…)

В документации предлагают следующие примеры использования этих функций:
SELECT e.lastname,sales.revenue,EVALUATE(’dense_rank() over(order by %1 )’,sales.revenue) FROM sales s, employee e;
SELECT year.year, sales.qtysold, EVALUATE_AGGR(’sum(%1)’, sales.quantity) From SnowFlakeSales;
SELECT year, Sales as DOUBLE,CAST(EVALUATE(’OLAP_EXPRESSION(%1,’’LAG(units_cube_sales, 1, time, time LEVELREL time_levelrel)’’)’, OLAP_CALC) AS DOUBLE) FROM "Global".Time, "Global"."Facts - sales" WHERE EVALUATE_PREDICATE(’OLAP_CONDITION(%1, ’’LIMIT time KEEP ’’’’1’’’’, ’’’’2’’’’, ’’’’3’’’’, ’’’’4’’’’ ’’) =1’, OLAP_CALC) order by year;

Следует отдельно отметить, что никто не запрещает использовать свои собственные функции. Приведу ниже пример.

Пример.

Создаем пакет с одной функцией в некоторой схеме:

create or replace package my_pkg is
function my_func(v in varchar2,n in number,d in date) return varchar2;
end;
/
create or replace package body my_pkg is
function my_func(v varchar2,n number,d date) return varchar2 is
begin
return ’varchar2 = ’ || v || ’ number = ’ || to_char(n) || ’ date =’ || to_char(d,’dd mon yyyy’);
end;
end;
/

Открываем Answers и создаем отчет:

Смотрим результаты:

Итак, подведем итоги:

• В функциях EVALUATE можно использовать свои собственные функции;
• В качестве параметров для функции можно константы следующих типов: текст, число, дата;
• В качестве параметров для функции можно использовать колонки, переменные;
• Результат работы функции можно преобразовать к разным типам;

Если возникает ошибка «Union of non-compatible types» значит вы неправильно передаете параметры. Возможные решения проблемы:

• Текстовый параметр или дата должны быть указаны в одинарных кавычек;
• Числовой параметр должен быть указан либо в одинарных кавычках (СУБД должна уметь корректно преобразовать его в число), либо без кавычек, но тогда десятичным разделителем должны быть точка (необходимо установить в СУБД для сессии соответствующий параметр или использовать формат по умолчанию);
• Даты должны быть указаны в формате, который СУБД может корректно распознать (необходимо установить в СУБД для сессии соответствующий параметр или использовать формат по умолчанию).

Метки: BIEE, Oracle