Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

SQL Stored Procedure

I have a sp called SP_Qlikview that returns columns A, B and C so in my script I have the following:

Load

A, B, C

Sql exec SP_Qlikview.

Question is what table does this load into in the script as when i run the reload the table viewer doesn't show anything.  In addition, in my script preceding this statement as a number of concatendate load statements for three fact tables.

Thanks

21 Replies
Not applicable
Author

there is rhyme and reason to the If 1=0 and WHere 1=0. It ensures that this code is never executed. But it holds a direct result out of the procedure. so make that structure the same as the temp table.

Not applicable
Author

Hi

Could you include an example in the above.  Are you replacing the '' with the actual field name from the temptable?

Not applicable
Author

Here is the SP content

CREATE PROCEDURE [dbo].[CostQlikView]

AS

BEGIN

DECLARE curFP CURSOR FAST_FORWARD LOCAL FOR

   SELECT TOP 6 FinancialPeriod

   FROM PLC_CostBuckets

   GROUP BY FinancialPeriod

   ORDER BY FinancialPeriod DESC

DECLARE @fp datetime

IF OBJECT_ID('tempdb..#CostA') IS NOT NULL

   DROP TABLE #CostA;

CREATE TABLE #CostA ([Class]      varchar(200)

                    ,[Financial Period]        datetime

                              )

OPEN curFP

FETCH NEXT FROM curFP INTO @fp

WHILE @@FETCH_STATUS = 0

BEGIN

   INSERT INTO #CostA ([Class],[Financial Period])

   EXEC dbo.Report_Cost @FinancialPeriodParam = @fp, @GroupGroup = 'All', @Service = 'All'

  

   UPDATE #CostA SET [Financial Period] = @fp WHERE [Financial Period] IS NULL

   FETCH NEXT FROM curFP INTO @fp

END

CLOSE curFP

DEALLOCATE curFP

Colin-Albert

You need a semicolon at the end of the A,B,C line, and SQL line

     Costs:

     Load A, B, C ;

     sql exec ABC_CostQlikView ;

You also need to enable "Open database in read write mode" in the connection settings if using stored procedures.

rw.JPG.jpg

Not applicable
Author

Ive done that but after reload the Costs do not appear still in the list of tables to select from in the select fields.  I dont understand why that is? 

Not applicable
Author

Any thoughts/help on this please?

Not applicable
Author

any help on this please?

Colin-Albert

Have you looked at the log file QlikView creates when reloading the document. This will show the actual sql query that is executed and how many rows are returned.

daveamz
Partner - Creator III
Partner - Creator III

You have to set nocount on  in your SP

CREATE PROCEDURE [dbo].[CostQlikView]

AS

SET NOCOUNT ON

BEGIN

DECLARE curFP CURSOR FAST_FORWARD LOCAL FOR

   SELECT TOP 6 FinancialPeriod

   FROM PLC_CostBuckets

   GROUP BY FinancialPeriod

   ORDER BY FinancialPeriod DESC

DECLARE @fp datetime

IF OBJECT_ID('tempdb..#CostA') IS NOT NULL

   DROP TABLE #CostA;

CREATE TABLE #CostA ([Class]      varchar(200)

                    ,[Financial Period]        datetime

                              )

OPEN curFP

FETCH NEXT FROM curFP INTO @fp

WHILE @@FETCH_STATUS = 0

BEGIN

   INSERT INTO #CostA ([Class],[Financial Period])

   EXEC dbo.Report_Cost @FinancialPeriodParam = @fp, @GroupGroup = 'All', @Service = 'All'

  

   UPDATE #CostA SET [Financial Period] = @fp WHERE [Financial Period] IS NULL

   FETCH NEXT FROM curFP INTO @fp

END

CLOSE curFP

DEALLOCATE curFP

David

Not applicable
Author

Thats seems to have done something.

However, when i do reload the script I get a OLEDB read failed error .  Please advise?