2 Replies Latest reply: May 13, 2011 6:07 AM by Christian Conejero RSS

    how can introduce parameters in a consult from qlikview

      how can introduce parameters in a consult from qlikview

        • how can introduce parameters in a consult from qlikview
          Erich Shiino

          Can you give more details on what you need?

           

          If you mean an sql query, for example, you can use native sql syntax:

           

          SQL select * from TABLE1 where FIELD1 = "VALUE1";

          • how can introduce parameters in a consult from qlikview
            Christian Conejero

            Hi,

            By using the EXECUTE command in an SQL statement it's possible to execute stored procedures, if the user is granted access to do so on the server. Here are two examples:

            Executing sp with input / output parameters

            SQL
            declare @var3 int
            execute sp_myProcedure 5,3, @var3 output
            Select @var3 as result;


            Executing sp returning a result set
            SQL Execute CustOrderHist 'CustomerNo';

            ORACLE:

            In Oracle, you can call a stored function with a syntax like:

            var result number
            exec :result := pkg_ims_status.ext_checkdbstatus()

             

            The function will return a value to be stored in the variable result.

            You can call the same function in QlikView using an ordinary SQL SELECT-statement like:

            SQL SELECT
            pkg_ims_status.ext_checkdbstatus() as status;

             


            Running a stored procedure without returning any recordset:

            Use the function CALL to specify the name of the stored procedure.

            Example (Launches XYZ with the two parameters 2 & 5):

            sql call XYZ ( 2 , 5 );

             

            This works for both ODBC and OLE DB. However, when opening the connection using OLE DB, make sure to add (mode is write) before the finishing semi colon in the connection string.

             

             
            Note1:
            You need to make sure that you activated "Open Databases in Read and Write mode" in QlikView. This is done in the script editor on the tab "Settings".

            Asegúrate de que está activado Open DataBase en modo de lectura y escritura ()

            Note2:
            If using OLEDB as a provider, you will also need to add the parameter "mode is write" in the OLEDB-connection:
            CONNECT TO [Provider=SQLOLEDB.1;Persist Security Info=True;Initial Catalog=Northwind;Data Source=mySQL;](mode is write);