topic Re: SQL Load Silently Fails in QlikView

SQL Load Silently Fails

makximenkoa — Thu, 07 Jun 2018 19:25:03 GMT

Hello,

I am having issues with loading a SQL statement in QlikView desktop. The script runs without issues but there doesn't seem to be any data in the document after the load. Below is a very similar sql statement inside the load script. Any help would be greatly appreciated.

TEST:


LOAD *;
SQL


SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
DECLARE @StartDate NVARCHAR(25) 
DECLARE @EndDate NVARCHAR(25)
SET @StartDate ='01-01-2017'
SET @EndDate = '02-01-2017'


---
DECLARE @vStartDate as date
DECLARE @vEndDate as date 
SET @vStartDate = @StartDate
SET @vEndDate = @EndDate     


SELECT 
ht.HSP_ACCOUNT_ID
INTO #MAINSELECT1 
FROM hsp_transactions ht
INNER JOIN HSP_ACCOUNT ha on ht.HSP_ACCOUNT_ID=ha.HSP_ACCOUNT_ID     
WHERE ht.tx_type_ha_c = 1 AND  ht.tx_post_date >= @vStartDate and ht.tx_post_date < @vEndDate
and ht.primary_plan_id = 1
GROUP BY 
ht.HSP_ACCOUNT_ID
OPTION (RECOMPILE)  


------
SELECT 
har1.HSP_ACCOUNT_ID as HAR
,har1.TOT_CHGS
,loc.loc_name as Location




FROM HSP_ACCOUNT har1  
inner join #MAINSELECT1 m on har1.HSP_ACCOUNT_ID = m.HSP_ACCOUNT_ID
left outer join clarity_loc loc on (har1.loc_id = loc.loc_id)


ORDER  by 
har1.hsp_account_id
,har1.TOT_CHGS
,loc.LOC_NAME




OPTION (RECOMPILE)
DROP TABLE #MAINSELECT1
;

Re: SQL Load Silently Fails

vikramv — Thu, 07 Jun 2018 23:07:49 GMT

Try converting the functions and variable to be in ANSII sql ....sometimes sql server statements are not identified and executed as per what they are purposed for.

Ex: Convert the statements like

DECLARE @StartDate NVARCHAR(25) , DECLARE @EndDate NVARCHAR(25) , SET @StartDate ='01-01-2017' , SET @EndDate = '02-01-2017',

CAST('01-01-2017' as CHAR ) or CAST('01-01-2017' as DATE )

Similarly try to avoid these statements "OPTION (RECOMPILE)"

Make these kind of changes and test with limited load.

Or best thing is do not do formatting (like type conversions) at the first load and make all formatting after your initial load.

Re: SQL Load Silently Fails

olivierrobin — Fri, 08 Jun 2018 07:19:15 GMT

hello

are you sure your dates are in the right format for your query ?

if the load doesn't produce any error, that could mean there is nothing to retrieve corresponding to your query