topic Re: Retrieve data from database Functions in QlikView

Retrieve data from database Functions

Wed, 22 May 2013 07:20:20 GMT

Hi All;

I have a little doubt about Qlikview extraction from FUNCTIONS defined in a database.

Is QV able to do this? I mean,when we connect to a specific database,the options to include different contents are:Tables,views,synonyms,system tables and aliases.(marked in red).Functions are not defined.

I have full connection to a databse where some functions are defined,when i try to get data from the function:

SELECT * FROM FUNCTION_NAME

Qv shows an error explainning than he cant find this table or view ( this is true because in not a table or a view)

So,that my question:

Is QV able to retrieve data from a database function and if is it,how can i do this?

Thanks in advance!

Re: Retrieve data from database Functions

Wed, 22 May 2013 08:34:19 GMT

Hi Xabi,

Qv not able to retrieve database function, so you can redo functions on script to get the same result.

I hope to help you

Re: Retrieve data from database Functions

rbecher — Wed, 22 May 2013 10:04:23 GMT

Hi Xabi,

usually a database function works on a record level:

SELECT function_name([parameter]) AS result_column FROM [TABLE|VIEW];

Or do you mean PROCEDURES returning a result set?

- Ralf

Re: Retrieve data from database Functions

Wed, 22 May 2013 14:35:37 GMT

Hi Ralf,

This is the function I use.

CREATE OR REPLACE FUNCTION DATABASE_NAME.rol_res_mapping

RETURN RoleResourceMapSet pipelined

entl_rec roleresmapview%rowtype;

CURSOR entl_cur

SELECT * FROM roleresmapview ;

BEGIN

OPEN entl_cur;

LOOP

FETCH entl_cur INTO entl_rec;

EXIT

WHEN entl_cur%NOTFOUND;

pipe row(NEW RoleResourceMap(entl_rec.A,entl_rec.B,entl_rec.C,entl_rec.D));

END LOOP;

CLOSE entl_cur;

END;

when I run select * from table(rol_res_mapping) in toad,i retrieve 4 fields (A;B;C;D) with the corresponding data.

Do you know how I can retrieve the same data in QV?Trying to redo this function in QV script?

Thanks for you help