Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I try to execute a strored procedure in a Loadscript. The procedure needs a paramter @Timestamp which has the type "datetime".
Since the @Timestamp parameter must be calculated at runtime, I try to solve this with a variable "loadstart" in the script.
I read several hints from other discussion, but I couldn't get the correct syntax for the statement.
Can someone help me?
Syntax of my last attempt:
LOAD loadstart;
SQL SELECT loadstart=DATEADD(d, -1, GETDATE());
LOAD Ident,
Loop,
RecNo() AS AutoIncrement,
Identifier,
Result AS Result,
ResultBeschreibung AS Fehler,
Result & ' - ' & ResultBeschreibung AS "Fehlercode - Text",
WorkCount,
Timestamp AS Zeitstempel,
Artikel AS Artikelnummer,
Stationskennung AS Testerkennung,
Adapter AS Adapter,
Nest,
WP_Pos_Nr,
LetzterDurchlauf;
SQL EXEC 'Linerecprod.Linerecprod.[sp_FT1HeaderData] @Timestamp = "$(loadstart)" ' ;
Only when typing in the timestamp directly as a string (see below) the statement works properly:
LOAD Ident,
Loop,
RecNo() AS AutoIncrement,
Identifier,
Result AS Result,
ResultBeschreibung AS Fehler,
Result & ' - ' & ResultBeschreibung AS "Fehlercode - Text",
WorkCount,
Timestamp AS Zeitstempel,
Artikel AS Artikelnummer,
Stationskennung AS Testerkennung,
Adapter AS Adapter,
Nest,
WP_Pos_Nr,
LetzterDurchlauf;
SQL EXEC Linerecprod.Linerecprod.[sp_FT1HeaderData] @Timestamp='09.17.2015 00:00:00';
Hi Thomas,
Could you try with two parameters? I nearly always use two to select a range of dates and defime the procedure along these lines.
USE [CLARITY];
GO
SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO
/*
=====================================
Author: Gethyn Owen
Create date: 07/09/2015
Description: Susceptibility Tests
For AmSurv Report
=====================================
*/
CREATE PROCEDURE [dbo].[SP_GO_AmSurve_SUSCEPTIBILITY] (@Start VARCHAR(50), @End VARCHAR(50))
AS
DECLARE
@StartDate DATETIME,
@EndDate DATETIME
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
SET @StartDate = EPIC_UTIL.EFN_DIN (@Start)
SET @endDate = EPIC_UTIL.EFN_DIN (@End)
SELECT DISTINCT
(V_LAB_RESULTS.SPECIMEN_ID + '|') + (CAST (CLARITY_ORGANISM.ORGANISM_ID AS VARCHAR (18))) AS SORT,
Cast(CLARITY_COMPONENT.ABBREVIATION as VarChar(9)) AS [Antibiotic Code],
CONVERT (DATETIME, CONVERT (VARCHAR (10), V_LAB_RESULTS.LAST_VERIFIED_DATETIME, 112)) AS LastVerifiedDate,
CASE
WHEN ZC_SUSCEPT.ABBR IS NOT NULL
THEN ZC_SUSCEPT.ABBR
ELSE
CASE
WHEN RES_MICRO_SUSC.SUSC_INTERPRETATN_C IS NULL
THEN 'U'
WHEN ZC_SUSCEPT.SUSCEPT_C IS NULL
THEN ((('U') + ' [') + CAST (RES_MICRO_SUSC.SUSC_INTERPRETATN_C AS VARCHAR (255))) + ']'
WHEN ZC_SUSCEPT.ABBR IS NULL
THEN ((('U') + ' [') + CAST (ZC_SUSCEPT.SUSCEPT_C AS VARCHAR (255))) + ']'
ELSE ((CAST (ZC_SUSCEPT.ABBR AS VARCHAR (255)) + ' [') + CAST (ZC_SUSCEPT.SUSCEPT_C AS VARCHAR (255))) + ']'
END
END AS RES_MICRO_SUSC_group
FROM CLARITY.dbo.RES_MICRO_CULTURE RES_MICRO_CULTURE
LEFT OUTER JOIN CLARITY.dbo.CLARITY_ORGANISM CLARITY_ORGANISM
ON (RES_MICRO_CULTURE.CULT_ORG_ID = CLARITY_ORGANISM.ORGANISM_ID)
LEFT OUTER JOIN CLARITY.dbo.RES_DB_MAIN RES_DB_MAIN
ON (RES_DB_MAIN.RESULT_ID = RES_MICRO_CULTURE.RESULT_ID)
FULL OUTER JOIN CLARITY.dbo.RES_MICRO_SUSC RES_MICRO_SUSC
ON (RES_MICRO_SUSC.CULTURE_ID = RES_MICRO_CULTURE.RESULT_ID)
AND (RES_MICRO_SUSC.UNIQUE_ORGANISM_ID = RES_MICRO_CULTURE.LINE)
LEFT OUTER JOIN CLARITY.dbo.V_LAB_RESULTS V_LAB_RESULTS
ON (RES_DB_MAIN.RESULT_ID = V_LAB_RESULTS.RESULT_ID)
LEFT OUTER JOIN CLARITY.dbo.CLARITY_COMPONENT CLARITY_COMPONENT
ON (RES_MICRO_SUSC.SUSC_ANTIBIOTIC_ID = CLARITY_COMPONENT.COMPONENT_ID)
LEFT OUTER JOIN CLARITY.dbo.METHOD_INFO METHOD_INFO
ON (RES_MICRO_SUSC.SUSC_TEST_METH_ID = METHOD_INFO.METHOD_ID)
RIGHT OUTER JOIN CLARITY.dbo.SPEC_DB_MAIN SPEC_DB_MAIN
ON (RES_MICRO_SUSC.SPECIMEN_ID = SPEC_DB_MAIN.SPECIMEN_ID)
LEFT OUTER JOIN CLARITY.dbo.TEST_MSTR_DB_MAIN TEST_MSTR_DB_MAIN
ON (RES_MICRO_SUSC.SUSC_TEST_ID = TEST_MSTR_DB_MAIN.TEST_ID)
LEFT OUTER JOIN CLARITY.dbo.ZC_SUSCEPT ZC_SUSCEPT
ON (RES_MICRO_SUSC.SUSC_INTERPRETATN_C = ZC_SUSCEPT.SUSCEPT_C)
LEFT OUTER JOIN CLARITY.dbo.RTYPE_DB_MAIN RTYPE_DB_MAIN
ON (RES_MICRO_SUSC.RESULT_TYPE_ID = RTYPE_DB_MAIN.RESULT_TYPE_ID)
WHERE (RES_DB_MAIN.RES_VAL_STATUS_C = 9
AND RES_MICRO_SUSC.CULTURE_ID IS NOT NULL
and CONVERT (DATETIME, CONVERT (VARCHAR (10), V_LAB_RESULTS.LAST_VERIFIED_DATETIME, 112)) Between @StartDate and @EndDate
AND RES_MICRO_SUSC.RESULT_ID Is NOT NULL)
AND CLARITY_COMPONENT.ABBREVIATION <> 'ADNR'
AND CASE
WHEN ZC_SUSCEPT.ABBR IS NOT NULL
THEN ZC_SUSCEPT.ABBR
ELSE
CASE
WHEN RES_MICRO_SUSC.SUSC_INTERPRETATN_C IS NULL
THEN 'U'
WHEN ZC_SUSCEPT.SUSCEPT_C IS NULL
THEN ((('U') + ' [') + CAST (RES_MICRO_SUSC.SUSC_INTERPRETATN_C AS VARCHAR (255))) + ']'
WHEN ZC_SUSCEPT.ABBR IS NULL
THEN ((('U') + ' [') + CAST (ZC_SUSCEPT.SUSCEPT_C AS VARCHAR (255))) + ']'
ELSE ((CAST (ZC_SUSCEPT.ABBR AS VARCHAR (255)) + ' [') + CAST (ZC_SUSCEPT.SUSCEPT_C AS VARCHAR (255))) + ']'
END
END <> 'U'
END
GO
My two parameters, @Start and @End can be entered as a date, timestamp or a relative period, ie T-5 for a 5 days ago, WB-1 for first day of last week. I have a function (EPIC_UTIL.EFN_DIN)which converts them to dd/mm/yyyy hh:mm:ss and saves them as @StartDate and @EndDate and then they get applied in the where clause of the sql code.
The code for the function is like this:
USE [CLARITY];
GO
SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO
CREATE FUNCTION [EPIC_UTIL].[EFN_DIN]
(
@i_vDateString VARCHAR(255)
)
RETURNS datetime
AS
BEGIN
-- Variables
DECLARE @vDate AS varchar(255)
DECLARE @dDate AS datetime
DECLARE @vDay as varchar(255)
DECLARE @vMonth as varchar(255)
DECLARE @vYear as varchar(255)
DECLARE @iSecs as int
DECLARE @iDays as int
DECLARE @iInstant as bigint
IF @i_vDateString is NULL RETURN NULL
-- Set to Upper case and deleting spaces
-- Be careful when entering numbers separated like "t + 2 4"
SET @vDate = upper(replace(@i_vDateString,' ',''))
-- Format Checking
-- Format DTE.xxxxx
IF @vDate LIKE 'DTE.%'
return DATEADD(dd,FLOOR(CONVERT(numeric(18,2),SUBSTRING(@vDate,5,LEN(@vDate)-4))),'18401231')
-- Format DAT.xxxxx
IF @vDate LIKE 'DAT.%'
return DATEADD(dd,121531 - CEILING(CONVERT(numeric(18,2),SUBSTRING(@vDate,5,LEN(@vDate)-4))),'18401231')
-- Format INST.xxxxx
IF @vDate LIKE 'INST.%'
BEGIN
SET @iInstant=CONVERT(bigint,SUBSTRING(@vDate,6,LEN(@vDate)-5))
SET @iDays=@iInstant/86400 --Get the days out of the instant
SET @iSecs=@iInstant % 86400 --Get the seconds out of the instant that are less than a day
return DATEADD(ss,@iSecs,DATEADD(dd,@iDays,'18401231'))
END
-- Format mmddyyyy
IF @vDate LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'
return CONVERT(datetime,SUBSTRING(@vDate,1,2)+'/'+SUBSTRING(@vDate,3,2)+'/'+SUBSTRING(@vDate,5,4))
-- Date accepted by T-SQL
IF CHARINDEX('-',@i_vDateString)=5
RETURN DATEADD(dd,0, DATEDIFF(dd,0,CONVERT(datetime,@i_vDateString,120)))
else
IF ISDATE(@i_vDateString)<>0
RETURN DATEADD(dd,0, DATEDIFF(dd,0,CONVERT(datetime,@i_vDateString)))
-- Relative to day interval
IF PATINDEX('T%',@vDate) = 1 --Today
BEGIN
SET @vDate=SUBSTRING(@vDate,2,LEN(@vDate))
IF @vDate LIKE '[+-]_%' -- Add or substract days
RETURN DATEADD(dd,0, DATEDIFF(dd,0,DATEADD(d,CAST(@vDate as int),getdate())))
ELSE IF @vDate='' RETURN DATEADD(dd,0, DATEDIFF(dd,0,getdate())) -- Today
ELSE RETURN NULL
END
-- Relative to time
IF PATINDEX ('N%',@vDate)= 1 -- Now
BEGIN
SET @vDate=SUBSTRING(@vDate,2,LEN(@vDate))
IF @vDate LIKE '[+-]_%' --Add or substract minutes
RETURN DATEADD(mi,CAST(@vDate as int),getdate())
ELSE IF @vDate='' RETURN getdate() --NOW
ELSE RETURN NULL
END
-- Month interval
-- Month BEGINNING
IF PATINDEX('MB%',@vDate) = 1
BEGIN
SET @dDate=DATEADD(d,-day(getdate())+1,getdate()) -- Set the beginning of the month
RETURN DATEADD(dd,0, DATEDIFF(dd,0,DATEADD(m,CAST(SUBSTRING(@vDate,3,LEN(@vDate)) as int),@dDate)))
END
-- Month Ending
IF PATINDEX('ME%',@vDate) = 1
BEGIN
SET @dDate=DATEADD(d,-day(getdate())+1,getdate()) -- Set the beginning of the month
RETURN DATEADD(dd,0, DATEDIFF(dd,0,DATEADD(d,-1,DATEADD(m,CAST(SUBSTRING(@vDate,3,LEN(@vDate)) as int)+1,@dDate))))
END
IF PATINDEX('M_%',@vDate) = 1 --Month
RETURN DATEADD(dd,0, DATEDIFF(dd,0,DATEADD(m,CAST(SUBSTRING(@vDate,2,len(@vDate)-1) as int),getdate())))
-- Relative to week interval
IF PATINDEX('WB%',@vDate) = 1 --Week beginning
BEGIN
SET @dDate = getdate() + 1 - DATEPART(dw, getdate()) + CAST(SUBSTRING(@vDate,3,LEN(@vDate)) as int)*7 -- Set the beginning of the week
RETURN DATEDIFF(dd,0,@dDate)
END
IF PATINDEX('WE%',@vDate) = 1 --Week ending
BEGIN
SET @dDate = getdate() + 7 - DATEPART(dw, getdate()) + CAST(SUBSTRING(@vDate,3,LEN(@vDate)) as int)*7 -- Set the ending of the week
RETURN DATEDIFF(dd,0,@dDate)
END
IF PATINDEX('W_%',@vDate) = 1 --Week
RETURN DATEADD(dd,0, DATEDIFF(dd,0,DATEADD(ww,CAST(SUBSTRING(@vDate,2,LEN(@vDate)-1) as int),getdate())))
-- Relative to year interval
IF PATINDEX('YB%',@vDate) = 1 --Year beginning
BEGIN
SET @dDate=CAST('01/01/' + CAST(year(GETDATE()) as varchar(4)) as datetime) -- Set the beginning of the year
RETURN DATEADD(yy,CAST(SUBSTRING(@vDate,3,LEN(@vDate)) as int),@dDate)
END
IF PATINDEX('YE%',@vDate) = 1 --Year ending
BEGIN
SET @dDate=CAST('01/01/' + CAST(year(GETDATE()) as varchar(4)) as datetime) -- Set the beginning of the year
RETURN DATEADD(d,-1,DATEADD(yy,1+CAST(SUBSTRING(@vDate,3,LEN(@vDate)) as int),@dDate))
END
IF PATINDEX('Y_%',@vDate) = 1 --Year
RETURN DATEADD(dd,0, DATEDIFF(dd,0,DATEADD(yy,CAST(SUBSTRING(@vDate,2,LEN(@vDate)-1) as int),getdate())))
-- M,D,YYYY
IF PATINDEX('_%,_%,_%',@vDate) = 1
BEGIN
SET @vMonth = LEFT(@vDate,CHARINDEX(',',@vDate)-1) -- get the month
SET @vDate = SUBSTRING(@vDate,LEN(@vMonth)+2,LEN(@vDate)) -- Advance the date to read
SET @vDay = LEFT(@vDate,CHARINDEX(',',@vDate)-1) -- get the day
SET @vYear = SUBSTRING(@vDate,CHARINDEX(',',@vDate)+1,LEN(@vDate))
RETURN CONVERT(datetime,@vMonth +'/'+@vDay+'/'+@vYear)
END
-- Omit year
IF PATINDEX('_%/_%',@vDate) = 1 and (isdate(@vdate)=1 or isdate(@vdate+'/'+datename(yyyy,getdate()))=1)
RETURN CONVERT(datetime,SUBSTRING(@vDate,1,CHARINDEX('/',@vDate)-1)+'/'+SUBSTRING(@vDate,CHARINDEX('/',@vDate)+1,len(@vDate))+'/'+datename(yyyy,getdate()))--Add the year
IF PATINDEX('_%._%',@vDate) = 1 and (isdate(@vdate)=1 or isdate(@vdate+'/'+datename(yyyy,getdate()))=1)
RETURN CONVERT(datetime,SUBSTRING(@vDate,1,CHARINDEX('.',@vDate)-1)+'/'+SUBSTRING(@vDate,CHARINDEX('.',@vDate)+1,len(@vDate))+'/'+ datename(yyyy,getdate()))--Add the year
IF PATINDEX('_%,_%',@vDate) = 1 and (isdate(replace(@vdate,',','/'))=1 or isdate(replace(@vdate,',','/')+'/'+datename(yyyy,getdate()))=1)
RETURN CONVERT(datetime,SUBSTRING(@vDate,1,CHARINDEX(',',@vDate)-1)+'/'+SUBSTRING(@vDate,CHARINDEX(',',@vDate)+1,len(@vDate))+'/'+ datename(yyyy,getdate()))--Add the year
-- Default
ELSE
RETURN NULL
RETURN @dDate
END
GO
I would then have this in QlikView:
SQL EXEC CLARITY.dbo.SP_GO_AmSurv_SUSCEPTIBILITY 'MB-1', 'ME-1';
This would then run the procedure and bring in the data for the whole of the last calendar month.
Could you do similar and have your @Start parameter as the earliest time that you want to start from perhaps YB-5 for the year start five years ago and have the @End as T-1 for yesterday?
Gethyn.
Before the LOAD expression, ensure that the variable loadstart is formatted correctly:
Let loadstart = Date(loadstart, 'MM.DD.YYYY hh:mm:ss');
The the SQL statement is:
SQL EXEC Linerecprod.Linerecprod.sp_FT1HeaderData @Timestamp = '$(loadstart)' ;
The suggestions still do not work.
I have the strong suspicion that the variable "loadstart" has no value when the stored procedure is executed.
Is it possible that the statement to fill "loadstart" isn't executed before the stored procedure is executed?
Or is the value of "loadstart" not available within the script, only in the application?
The order of the statements in the script is the following:
LOAD loadstart;
SQL SELECT loadstart=DATEADD(d, -1, GETDATE());
LOAD Ident,
Loop,
RecNo() AS AutoIncrement,
Identifier,
Result AS Result,
ResultBeschreibung AS Fehler,
Result & ' - ' & ResultBeschreibung AS "Fehlercode - Text",
WorkCount,
Timestamp AS Zeitstempel,
Artikel AS Artikelnummer,
Stationskennung AS Testerkennung,
Adapter AS Adapter,
Nest,
WP_Pos_Nr,
LetzterDurchlauf;
SQL EXEC 'Linerecprod.Linerecprod.[sp_FT1HeaderData] @Timestamp = "$(loadstart)" ' ;
Hi Thomas,
Try defining the variable at the beginning of the QlikView script (where the system formats are defined) like this:
SET ThousandSep=',';
SET DecimalSep='.';
SET MoneyThousandSep=',';
SET MoneyDecimalSep='.';
SET MoneyFormat='£#,##0.00;-£#,##0.00';
SET TimeFormat='hh:mm:ss';
SET DateFormat='DD/MM/YYYY';
SET TimestampFormat='DD/MM/YYYY hh:mm:ss[.fff]';
SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';
LET loadstart = DayStart(Now()-1);
Sorry, but using two parameters is not an option. I cannot change the stored procedure.
But I'm also convinced that this will not be the solution.
All the attempts lead to an empty string when referencing $(loadstart).
Something must be wrong with the reference or the value of loadstart is not available when executiong the script.
Putting this at the beginning of the QlikView script will set loadstart to yesterdays date, which you can check in setting and Variable Overview after you run the script.
LET loadstart = DayStart(Now()-1);
Could it be that your loadstart is a VarChar and your stored procedure is expecting DateTime?
Hi Gethyn,
this is a promising attempt.
Now "loadstart" is filled, but now I get the following type mismatch for the datetime format:
Is there a possibility to CAST the datetime format from '17.09.2015 00:00:00' to '09.17.2015 00:00:00' which is accepted in the EXEC statement ?
Hi Thomas,
Use this instead:
LET loadstart = TimeStamp(DayStart(Now()-1), 'MM.DD.YYYY hh:mm:ss');
Success!!
Great, now it works.
Thanks to All for your Support!
And now I know that the result of an SQL statement is not available within the script.