topic Re: Using temp tables in a SQL query load. When go to store the table it errors saying table not found. in QlikView

Using temp tables in a SQL query load. When go to store the table it errors saying table not found.

Wed, 25 Nov 2020 16:16:04 GMT

I had a Crystal report that I have been using for a while which used some SQL which uses 3 temp tables to get the data needed then combines them at the end to get the final product. I took the SQL and tried plopping it into Qlikview but when I go to store the data in a QVD it states the table isn't found. Any ideas on why this wouldn't work? The user in the connection string is the same user I run the Crystal report with.

Temp1:

NoConcatenate

LOAD *;

SQL

if OBJECT_ID('tempdb.dbo.#PWDXS', 'U') IS NOT NULL DROP TABLE #PWDXS

if OBJECT_ID('tempdb.dbo.#PALG', 'U') IS NOT NULL DROP TABLE #PALG

if OBJECT_ID('tempdb.dbo.#PWMD', 'U') IS NOT NULL DROP TABLE #PWMD

select *

into #PWDXS

from

(SELECT distinct

rank () over (partition by dbo.PAT_ENC.PAT_ENC_CSN_ID order by cmed2.NAME DESC) as medrank,

dbo.PATIENT.PAT_NAME,

dbo.PATIENT.PAT_ID,

dbo.PATIENT.PAT_MRN_ID,

dbo.PAT_ENC.PAT_ENC_CSN_ID,

dbo.PAT_ENC.CONTACT_DATE,

dbo.CLARITY_SER.PROV_NAME,

dbo.clarity_ser.PROV_ID,

dbo.CLARITY_DEP.DEPARTMENT_NAME,

dbo.clarity_dep.DEPARTMENT_ID,

cmed2.NAME as Medication_name

FROM

dbo.PATIENT

LEFT OUTER JOIN dbo.PAT_ENC ON (dbo.PATIENT.PAT_ID=dbo.PAT_ENC.PAT_ID)

LEFT OUTER JOIN dbo.PAT_ENC_DX ON (dbo.PAT_ENC.PAT_ENC_CSN_ID=dbo.PAT_ENC_DX.PAT_ENC_CSN_ID)

LEFT OUTER JOIN dbo.CLARITY_EDG ON (dbo.PAT_ENC_DX.DX_ID=dbo.CLARITY_EDG.DX_ID)

LEFT JOIN dbo.EDG_CURRENT_ICD10 ON (dbo.CLARITY_EDG.DX_ID=dbo.EDG_CURRENT_ICD10.DX_ID )

LEFT OUTER JOIN dbo.CLARITY_DEP ON (dbo.PAT_ENC.DEPARTMENT_ID=dbo.CLARITY_DEP.DEPARTMENT_ID)

LEFT OUTER JOIN dbo.CLARITY_SER ON (dbo.CLARITY_SER.PROV_ID=dbo.PAT_ENC.VISIT_PROV_ID)

left outer join ORDER_MED omed2 on omed2.PAT_ENC_CSN_ID=dbo.PAT_ENC.PAT_ENC_CSN_ID

left outer join clarity_medication cmed2 on cmed2.MEDICATION_ID=omed2.MEDICATION_ID

WHERE

(

dbo.EDG_CURRENT_ICD10.CODE IN ( 'j01.0','j01.00','j01.01','j01.1','j01.10','j01.11','j01.2','j01.20','j01.21','j01.3','j01.30','j01.31','j01.4','j01.40','j01.41','j01.8','j01.80','j01.81','j01.9','j01.90','j01.91', 'j32.0', 'j32.1', 'j32.2', 'j32.4', 'j32.8', 'j32.9', 'j34.9', 'r09.82', 'r09.81', 'r09.89', 'j34.1', 'j34.89' )

and

( dbo.CLARITY_DEP.RPT_GRP_TWO = 'PRIMARY CARE' )

AND

( dbo.PAT_ENC.ENC_TYPE_C IN (50,101,3) )

AND

( dbo.PAT_ENC.APPT_STATUS_C IN (2,6,7) )

AND

(((cmed2.THERA_CLASS_C = '1001' and cmed2.PHARM_CLASS_C in ('1','2','3','4','5','8'))

or cmed2.name like '%sulfamethoxazole-trimethoprim%' or cmed2.name like '%CEFTRIAXONE%'))

AND

omed2.ORDER_CLASS_C <> 3

AND

dbo.PAT_ENC.CONTACT_DATE BETWEEN dateadd("year",datediff("year", 0,getdate()),0) AND getdate()

)) tbl1

where tbl1.medrank=1

create index idx_PWDXS_pat_id on #PWDXS(PAT_ID)

create index idx_PWDXS_CSN on #PWDXS(PAT_ENC_CSN_ID)

select *

INTO #PALG

from

( SELECT

rank () over (partition by ALLERGY.PAT_ID order by ALLERGY.ALLERGEN_ID DESC) as allrank,

ALLERGY.DESCRIPTION Allergy_Description,

ALLERGY.ALLERGEN_ID,

ALLERGY.DATE_NOTED Allergy_Date,

ALLERGY.PAT_ID,

ALLERGY.SEVERITY_C

FROM

dbo.PATIENT

LEFT OUTER JOIN ALLERGY ON (dbo.PATIENT.PAT_ID=ALLERGY.PAT_ID)

WHERE

(

ALLERGY.ALRGY_STATUS_C = 1

AND

ALLERGY.ALLERGEN_ID IN ( 9006, 3436, 16726, 26953, 12768, 3437, 11396, 5070, 6206, 12757, 5071, 12127, 25, 12129, 5075, 12128, 10539, 10954 )

)) tbl1

where tbl1.allrank=1

create index idx_PALG_pat_id on #PALG(PAT_ID)

select *

into #PWMD

from

(SELECT distinct

rank () over (partition by dbo.PAT_ENC.PAT_ENC_CSN_ID order by cmed.NAME DESC) as medrank,

dbo.PATIENT.PAT_NAME,

dbo.PATIENT.PAT_ID,

dbo.PATIENT.PAT_MRN_ID,

dbo.PAT_ENC.PAT_ENC_CSN_ID csn1,

dbo.PAT_ENC.CONTACT_DATE date1,

enc2.PAT_ENC_CSN_ID csn2,

enc2.CONTACT_DATE date2,

cmed.NAME as Medication_name

FROM

dbo.PATIENT

left join #PWDXS pat_w_dx on pat_w_dx.PAT_ID= dbo.PATIENT.PAT_ID

LEFT JOIN dbo.PAT_ENC ON (pat_w_dx.PAT_ENC_CSN_ID=dbo.PAT_ENC.PAT_ENC_CSN_ID)

left outer join Pat_Enc enc2 on enc2.PAT_ID=dbo.PATIENT.PAT_ID and enc2.PAT_ENC_CSN_ID<>dbo.PAT_ENC.PAT_ENC_CSN_ID and enc2.CONTACT_DATE>=DATEADD(dd,-45,dbo.pat_enc.contact_date) and enc2.CONTACT_DATE<dbo.pat_enc.contact_date

left outer join ORDER_MED omed on omed.PAT_ENC_CSN_ID=enc2.PAT_ENC_CSN_ID

left outer join CLARITY_MEDICATION cmed on (cmed.MEDICATION_ID=omed.MEDICATION_ID)

WHERE

(

omed.ORDER_CLASS_C <>3

and ((cmed.THERA_CLASS_C = '1001' and cmed.PHARM_CLASS_C in ('1','2','3','4','5','8'))

or cmed.name like '%sulfamethoxazole-trimethoprim%' or cmed.name like '%CEFTRIAXONE%'

or cmed.name like '%CLINDAMYCIN%' or cmed.name like '%CEFTRIAXONE%')

) ) tbl1

where tbl1.medrank=1

order by tbl1.PAT_NAME

create index idx_PWMD_pat_id on #PWMD(PAT_ID)

create index idx_PWMD_CSN on #PWMD(csn1)

select

pat_w_dx1.PAT_ID,

pat_w_dx1.PAT_NAME,

pat_w_dx1.PAT_MRN_ID,

pat_w_dx1.PROV_NAME,

pat_w_dx1.PROV_ID,

pat_w_dx1.DEPARTMENT_NAME,

pat_w_dx1.DEPARTMENT_ID,

pat_w_dx1.PAT_ENC_CSN_ID,

pat_w_dx1.CONTACT_DATE,

pat_w_dx1.medication_name

from #PWDXS pat_w_dx1

left outer join #PALG pat_all1 on pat_all1.PAT_ID=pat_w_dx1.PAT_ID

left outer join #PWMD pat_wmed on pat_wmed.csn1=pat_w_dx1.PAT_ENC_CSN_ID

where pat_wmed.Medication_name is NULL

and (pat_all1.Allergy_Description is NULL or pat_all1.Allergy_Date > pat_w_dx1.CONTACT_DATE)

order by pat_w_dx1.PAT_NAME;

STORE * from Temp1 into ..\EPIC\DataSources\DXTemp.QVD (qvd);

Re: Using temp tables in a SQL query load. When go to store the table it errors saying table not found.

felipedl — Mon, 02 Oct 2017 20:36:54 GMT

Hi Ben,

Do you get any tables at all from those SQL statements?

Press CTRL+T to see the data model after the app reloads.

Felipe.

Re: Using temp tables in a SQL query load. When go to store the table it errors saying table not found.

Tue, 03 Oct 2017 12:44:28 GMT

Nope, no tables. I wasn't sure if it was the temp tables causing that or not. I am fairly new to Qlikview but have built a few things and never had any problems. It just seemed like that was the piece I hadn't used before so I may be going down the wrong path.

Re: Using temp tables in a SQL query load. When go to store the table it errors saying table not found.

felipedl — Tue, 03 Oct 2017 12:57:18 GMT

I would test the selects statemente individually. I believe the selects are not being executed correctly and not creating any tables, but that's a wild guess.

Felipe.

Re: Using temp tables in a SQL query load. When go to store the table it errors saying table not found.

kevinduck — Tue, 03 Oct 2017 13:39:55 GMT

We do this successfully in our environment, so I know it works with SQL 2008 and QV 12.

You have 4 SELECT statements feeding into your preceding load for Temp 1. Qlik probably can't tell which one to load into the table. Try separating the creation of the # temp tables from the Qlik LOAD like this (I cut out everything between the first and last line of the SQL that creates the #temp tables):

Temp1:

NoConcatenate

LOAD *;

SQL

if OBJECT_ID('tempdb.dbo.#PWDXS', 'U') IS NOT NULL ...

...

< put the rest of your temp table code here >

...

create index idx_PWMD_CSN on #PWMD(csn1)

;

Temp1:

NoConcatenate

LOAD *;

select

pat_w_dx1.PAT_ID,

pat_w_dx1.PAT_NAME,

pat_w_dx1.PAT_MRN_ID,

pat_w_dx1.PROV_NAME,

pat_w_dx1.PROV_ID,

pat_w_dx1.DEPARTMENT_NAME,

pat_w_dx1.DEPARTMENT_ID,

pat_w_dx1.PAT_ENC_CSN_ID,

pat_w_dx1.CONTACT_DATE,

pat_w_dx1.medication_name

from #PWDXS pat_w_dx1

left outer join #PALG pat_all1 on pat_all1.PAT_ID=pat_w_dx1.PAT_ID

left outer join #PWMD pat_wmed on pat_wmed.csn1=pat_w_dx1.PAT_ENC_CSN_ID

where pat_wmed.Medication_name is NULL

and (pat_all1.Allergy_Description is NULL or pat_all1.Allergy_Date > pat_w_dx1.CONTACT_DATE)

order by pat_w_dx1.PAT_NAME;

STORE * from Temp1 into ..\EPIC\DataSources\DXTemp.QVD (qvd);

Re: Using temp tables in a SQL query load. When go to store the table it errors saying table not found.

Tue, 03 Oct 2017 15:41:50 GMT

So I backed it off to just running the first select with no temp table and that worked. As soon as I dropped the "into #PWDXS" back into it and put a select * from #PWDXS at the end it started throwing the error again. I have been talking with one of our other techies here and he suggested dropping it into a stored procedure and then try calling that. I may have to give that a shot.

Re: Using temp tables in a SQL query load. When go to store the table it errors saying table not found.

Tue, 03 Oct 2017 17:56:33 GMT

I did do the stored procedure and it worked but I went ahead and gave this a shot as well. This did the trick. Thanks!

Re: Using temp tables in a SQL query load. When go to store the table it errors saying table not found.

kevinduck — Tue, 03 Oct 2017 18:26:11 GMT

I'm glad this worked Kindly mark as "Answered" to help others searching for the same/similar issue.

Thanks,

Kevin