Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
BARC’s The BI Survey 19 makes it official. BI users love Qlik. GET REPORT
Highlighted
makximenkoa
New Contributor III

SQL Load Silently Fails

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

;

2 Replies
vikky
Contributor III

Re: SQL Load Silently Fails

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
Valued Contributor II

Re: SQL Load Silently Fails

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