SQL Load Silently Fails

[makximenkoa]

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
;

[vikramv]

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',

to

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.

[olivierrobin]

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