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

[#1]

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);

[felipedl]

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.

[#3]

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.

[felipedl]

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.

[kevinduck · Accepted solution]

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);

[#6]

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.

[#7]

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!

[kevinduck]

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

Thanks,

Kevin