SQL Query In QlikView Script

dineshm030 — Wed, 09 Jun 2021 09:53:46 GMT

Hi All,

I am trying to execute the SQL query in QlikView Script. The below error is showing in QlikView as well as same query is running SQL Server.

SQL
DECLARE @StartDimDate date = '$(@StartDimDate)';
SQL
DECLARE @CutoffDimDate date = DATEADD(DAY, -1, DATEADD(YEAR, 5, '$(@StartDimDate)'));
SQL
WITH seq(n) AS
(
SELECT 0 UNION ALL SELECT n + 1 FROM seq
WHERE n < DATEDIFF(DAY, '$(@StartDimDate)', @CutoffDimDate)
),
d(d) AS
(
SELECT DATEADD(DAY, n, '$(@StartDimDate)') FROM seq
),
src AS
(
SELECT
Date_Key = CAST(CONVERT(varchar(8),d,112) AS int),
TheDate = CONVERT(date, d),
TheDay = DATEPART(DAY, d),
Day_of_Week_Eng = DATENAME(WEEKDAY, d),
TheWeek = DATEPART(WEEK, d),
TheISOWeek = DATEPART(ISO_WEEK, d),
TheDayOfWeek = DATEPART(WEEKDAY, d),
Calendar_Month = DATEPART(MONTH, d),
TheMonthName = DATENAME(MONTH, d),
TheQuarter = DATEPART(Quarter, d),
Calendar_Year = DATEPART(YEAR, d),
TheFirstOfMonth = DATEFROMPARTS(YEAR(d), MONTH(d), 1),
TheLastOfYear = DATEFROMPARTS(YEAR(d), 12, 31),
TheDayOfYear = DATEPART(DAYOFYEAR, d),
Days_In_Month = MAX(DATEPART(DAY, d)) OVER (PARTITION BY DATEPART(YEAR, d), DATEPART(MONTH, d))
FROM d
);
SELECT * into #Dim_Date FROM src
ORDER BY TheDate
OPTION (MAXRECURSION 0);

Re: SQL Query In QlikView Script

NadiaB — Mon, 19 Jul 2021 19:02:47 GMT

Hi @dineshm030

Have you tried with a different diver? I would probably try the SQL server native one with ODBC DSN.

https://docs.microsoft.com/en-us/sql/connect/odbc/download-odbc-driver-for-sql-server?view=sql-server-ver15

The following article shows the odbc dsn part:

How to test an ODBC and OLEDB Connection without Qlik Products SQL Server
https://support.qlik.com/articles/000085290

Then you just need to create an ODBC connection from QlikView.

Hope it helps.

topic SQL Query In QlikView Script in QlikView

SQL Query In QlikView Script

Re: SQL Query In QlikView Script