3 Replies Latest reply: May 22, 2013 10:35 AM by xabi munoz RSS

    Retrieve data from database Functions

      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.

       

      x.JPG

      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

          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
            Ralf Becher

            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

                Hi Ralf,

                 

                 

                This is the function I use.

                 

                CREATE OR REPLACE FUNCTION DATABASE_NAME.rol_res_mapping

                    RETURN RoleResourceMapSet pipelined

                  IS

                    entl_rec roleresmapview%rowtype;

                    CURSOR entl_cur

                    IS

                      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