SQL Temp tables

[Anonymous]

Hello, I have a SQL query that has 5 temp tables in the script. What would be the best approach to add this script into Qlikview.

The below script is just an example. There are multiple where clauses and aggregations in each temp table.

Test:

SQL

//something like this

select *

into #temp1

from table1

select *

into #temp2

from table1

select *

into #temp3

from table1

select *

into #temp4

from table1

select *

into #temp5

from table1

select *

from table1

     inner join temp1 on table1.id = temp1.id

inner join temp1 on table1.id = temp2.id and table1.date = temp2.date

inner join temp1 on table1.id = temp3.id and table1.first = temp3.first

inner join temp1 on table1.id = temp4.id

inner join temp1 on table1.id = temp5.id

Also, Can someone explain to me why I can use a SQL temp table when I connect to Excel through an ODBC Connection but cannot use a SQL temp table through an ODBC Connection to Qlikview

//**Error testing table not found.

testing:

sql

SELECT *

into #testingtable

  FROM VTl

select  

      [ACT_DT]

      ,[IT_DT]

from #testingtable

LOAD [ACT_DT]

      ,[IT_DT]

      Resident testing

[pooja_sn]

End your SQL script with a semicolon (;)

[Anonymous]

Can you join store procedures?

[luciancotea]

Do all the logic in the stored procedure and return the result to QlikView. In the SP, you can use SQL specific features, like #temp tables,

[jonathandienst]

Are you using trusted connections or SQL Server security? Are you ODBC or OLEDB? Is is possible that the credentials for QV do not allow access to the master and tempdb databases which are necessary for temp tables.

[Anonymous]

I have tried both connections and I have changed my connection db to Master. Nothing has changed, it runs but no tables are displayed. I'm not sure if I'm using trusted connections or SQL Server security. 

[kevinduck]

After struggling with this recently, I found that if you separate the sql call that loads the temp table from the sql call that loads the data to QV, it works (at least in my environment).  Like this:

sql

SELECT *

into #testingtable

  FROM VTl

;

testing:

load *;

sql

select

      [ACT_DT]

      ,[IT_DT]

from #testingtable

;

EDIT:  I should add that I can successfully load and manipulate multiple #temp tables in the first SQL block, then retrieve the results in the second.

[Anonymous · Accepted solution]

For me, using the below in my script worked for me.

SET NOCOUNT ON

[zilonelion]

Check out this solution which has worked beautifully for me, inspired by BeASensei (https://basensei.com/software/qliksense/sql-temp-tables-in-qliksense/) 

Basically, before the load statement, write each ##temp_table query using SQL keyword (and end that query with a semicolon). And at the end, define the table, use SQL keyword and then your main select statement.

No need for SET NOCOUNT ON. See (what I mean by above) below:

LIB CONNECT TO xxxxx;

SQL

//something like this

select *

into #temp1

from table1;

SQL

select *

into #temp2

from table1;

SQL

select *

into #temp3

from table1;

SQL

select *

into #temp4

from table1;

SQL

select *

into #temp5

from table1;

Test:

LOAD *;

SQL

select *

from table1

     inner join temp1 on table1.id = temp1.id

inner join temp1 on table1.id = temp2.id and table1.date = temp2.date

inner join temp1 on table1.id = temp3.id and table1.first = temp3.first

inner join temp1 on table1.id = temp4.id

inner join temp1 on table1.id = temp5.id;