topic Using function in SQL select Query in QlikView

Using function in SQL select Query

abeyphilip — Thu, 09 Oct 2014 11:01:38 GMT

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

Re: Using function in SQL select Query

Peter_Cammaert — Thu, 09 Oct 2014 12:06:08 GMT

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

Re: Using function in SQL select Query

abeyphilip — Thu, 09 Oct 2014 12:20:43 GMT

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

Re: Using function in SQL select Query

maxgro — Thu, 09 Oct 2014 17:54:44 GMT

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);

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;