Using function in SQL select Query

[abeyphilip]

Hi,

In Qlikview load script, I am trying to use a user defined function in a SQL select query.

Something like this:

SQL select field1,

user_function(field2) as derived_field2

from table;

But I keep on getting error - QVX_UNEXPECTED_END_OF_DATA:

Is there any limitation in above usage - user_function(field2)

The query runs fine in sql and the qlikview user has execute rights on the function.

Appreciate any help on this.

Regards,

Abey

[Peter_Cammaert]

This user_function is not a QlikView script function, is it?

Is the account that is specified in the CONNECT statement allowed to execute this user_function?

Peter

[abeyphilip]

Thanks Peter for the reply.

user_function is a custom function created in the source Oracle database.

Yes, the user used to connect have the execute privileges on the function.

Regards,

Abey

[maxgro]

I just tried and it seems possible to use function; my oracle function is very, very simple

OLEDB CONNECT32 TO [Provider=OraOLEDB.Oracle.1;Persist Security Info=True;User ID=xxxxxx;Data Source=dev11;Extended Properties=""] (XPassword is xxxxxxxxx);

a:

select

    deptno,

    func(deptno) as newfield,

    dname

from test.dept;

CREATE OR REPLACE FUNCTION  func (dept   number)

RETURN varchar2 IS

BEGIN

  RETURN 'aaaaaa';

END func;