topic Re: SQL Temp tables in QlikView

SQL Temp tables

Anonymous — Tue, 23 Feb 2016 20:55:04 GMT

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

Re: SQL Temp tables

jagan — Wed, 24 Feb 2016 03:14:54 GMT

Hi,

I am not what you are trying to do in the SQL Query you posted, you are loading the same table 5 times into a single table and didn't used any where conditions and also you are joining the tables based on the same columns. I think you can directly load

Select*

FROM Table1;

There should be no difference.

Regards,

Jagan.

Re: SQL Temp tables

jonathandienst — Wed, 24 Feb 2016 05:09:15 GMT

Is this just an example, or is that your real script? Assuming it is NOT your real SQL script, then there is no reason why you cannot execute a statement using temp tables in QV - everything between the SQL keyword and the next semi-colon is passed directly to the SQL server. Its possible that the user name (or the connection string) has read-only rights.

You may need to check the Open Databases in Read and Write mode (the Settings tag in the bottom of the script editor).

Re: SQL Temp tables

vikasmahajan — Wed, 24 Feb 2016 06:51:48 GMT

Instead of Sql Scripting load directly into qlikview use joins.

Vikas

Re: SQL Temp tables

ramasaisaksoft — Wed, 24 Feb 2016 06:53:01 GMT

Hi John,

You called same table 5 times why? r u want to rename any columns in tables

any way my idea is just

Table1:

Load * from table1;

Tab2:

Load *

Resident Table1;

...etc

and about your conditions you already have same data in all tables are same so join output same data will come.

--> Some times you may be have data like sheet 1 2013 year data, sheet 2 2014 year data ...etc with same fields so u need to call 5 tables individually in qlikview, if u use Excel as source

but if u saved the data into database table you can call only once, in Qlikview automatically ur application speed will increase.

Re: SQL Temp tables

pooja_sn — Wed, 24 Feb 2016 07:14:20 GMT

Is there any error message you are getting?

Because I used an ODBC connection to connect to SQL and my Qlikview script has SQL query that includes temp tables, It works fine for me.

Re: SQL Temp tables

Anonymous — Wed, 24 Feb 2016 13:31:04 GMT

testing:

sql

SELECT *

into #testingtable

FROM VTl

select

[ACT_DT]

,[IT_DT]

from #testingtable

;

LOAD [ACT_DT]

,[IT_DT]

Resident testing

**Error testing table not found.

Re: SQL Temp tables

Anonymous — Wed, 24 Feb 2016 13:40:50 GMT

The script was just an example. I need to understand the syntax. For example the below query runs in SQL but not in Qlikview...(I checked the settings tag and I'm good there)

//**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

Re: SQL Temp tables

Anonymous — Wed, 24 Feb 2016 13:42:43 GMT

Yes my table does not exist.

//**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

Re: SQL Temp tables

luciancotea — Wed, 24 Feb 2016 14:17:24 GMT

The answer is: use stored procedure that returns a dataset.

Call the stored procedure from QlikView and done.

Re: SQL Temp tables

pooja_sn — Wed, 24 Feb 2016 14:21:51 GMT

End your SQL script with a semicolon (;)

Re: SQL Temp tables

Anonymous — Wed, 24 Feb 2016 14:30:53 GMT

Can you join store procedures?

Re: SQL Temp tables

luciancotea — Wed, 24 Feb 2016 14:34:40 GMT

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,

Re: SQL Temp tables

jonathandienst — Wed, 24 Feb 2016 14:42:13 GMT

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.

Re: SQL Temp tables

Anonymous — Wed, 24 Feb 2016 19:04:25 GMT

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.

Re: SQL Temp tables

kevinduck — Sat, 07 Jan 2017 01:40:27 GMT

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.

Re: SQL Temp tables

Anonymous — Mon, 09 Jan 2017 15:34:39 GMT

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

SET NOCOUNT ON

Re: SQL Temp tables

zilonelion — Wed, 17 Jan 2024 11:57:17 GMT

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;