Needing some help on my issue with a query that runs fine outside of Qlikview but returns the 'Table not found' message when running in the load script.
I've read a lot of different posts on the discussion forums about stored procedures and the answers, when there is one, and I have not been able to get those solutions to work. I'm most likely missing something or misunderstanding something.
I've condensed the query for simplicity below, if my understanding is correct Qlikview is not retrieving the results.
I've included the top portion of the query so you can see what is going on with @Dateoffset. The query hits a MS SQL Server 2008 environment.
I receive 'Table not found' when I get to: STORE Data INTO ../0_QVD_Generators/Data.qvd (QVD);
I've tried updating the second to last line to look like this, EXECUTE sp_executesql @sql output
SELECT @sql as result; based on a post on the forums, but my results where the same.
Any help and guidance is greatly appreciated.
Data:
SQL
DECLARE @DateOffset AS INT
DECLARE @sql AS NVARCHAR(MAX)
SET @DateOffset = 0;
SET @sql = N''
WHILE (@DateOffset < 3)
BEGIN
IF @DateOffSet > 0 SET @sql = CONVERT(NVARCHAR(MAX), @sql) + N' Union ';
SET @sql = CONVERT(NVARCHAR(MAX), @sql) +
N'
SELECT
Z.MARKETING_NAME,
Z.EFF_DATE,
Z.END_DATE,
CASE
WHEN Z.END_DATE > DATEADD(YEAR, ' + CAST(@DateOffset AS NVARCHAR) + ', Z.EFF_DATE) AND YEAR(DATEADD(YEAR, ' + CAST(@DateOffset AS NVARCHAR) + ', Z.EFF_DATE)) <= YEAR(GETDATE()) THEN 1
ELSE 0
END AS ACTIVE,
CASE
WHEN Z.END_DATE BETWEEN DATEADD(YEAR,' + CAST(@DateOffset AS NVARCHAR) + ', Z.EFF_DATE) AND DATEADD(YEAR, (' + CAST(@DateOffset AS NVARCHAR) + ' + 1), Z.EFF_DATE) THEN 1
ELSE 0
END AS TERMINATED
FROM
( SUB SELECT HERE
)
LEFT JOIN HERE'
SET @DateOffset +=1
END
EXECUTE sp_executesql @sql;
STORE Data INTO ../0_QVD_Generators/Data.qvd (QVD);
