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

[jagan]

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.

[jonathandienst]

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

[vikasmahajan]

Instead of Sql Scripting  load directly into qlikview use joins.

Vikas

[ramasaisaksoft]

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.

[pooja_sn]

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.

[Anonymous]

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.

[Anonymous]

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

[Anonymous]

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

[luciancotea]

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

Call the stored procedure from QlikView and done.