
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Creating and Dropping temp tables

SQL
TEMPTEST:
USE AA
SELECT TOP 1000
,[mktId]
,[effDt]
,[termDt]
,[termReasonCd]
,[uniqueNbr]
FROM [Ntwk];
SQL
TEMPTEST2:
USE AA
SELECT TOP 1000
FROM [NtwkMst] ;
SQL
USE AA
SELECT TOP 1000 [effDt]
,[termDt]
,[termReasonCd]
,[AA]
FROM TEMPTEST2
INNER JOIN TEMPTEST ON TEMPTEST2.AA = TEMPTEST.AA;
DROP TABLE TEMPTEST;
DROP TABLE TEMPTEST2
Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
modify your script to look as written below
ODBC CONNECT32 TO xxxxxx
TEMPTEST:
SQL
//USE AA
SELECT TOP 1000
[AA]
,[mktId]
,[effDt]
,[termDt]
,[termReasonCd]
,[uniqueNbr]
FROM [Ntwk];
inner join(TEMPTEST)
TEMPTEST2:
SQL
//USE AA
SELECT TOP 1000
[AA],
[BB]
FROM [NtwkMst];
//DROP TABLE TEMPTEST;
//DROP TABLE TEMPTEST2;
DROP FIELDS [mktId], [uniqueNbr];

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
and the error is?
try this (remove use..., if needed put the db before the table, move tempest)
and let we know
ODBC CONNECT32 TO xxxxxx;
TEMPTEST:
SQL
SELECT TOP 1000
[AA]
,[mktId]
,[effDt]
,[termDt]
,[termReasonCd]
,[uniqueNbr]
FROM [Ntwk];

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
modify your script to look as written below
ODBC CONNECT32 TO xxxxxx
TEMPTEST:
SQL
//USE AA
SELECT TOP 1000
[AA]
,[mktId]
,[effDt]
,[termDt]
,[termReasonCd]
,[uniqueNbr]
FROM [Ntwk];
inner join(TEMPTEST)
TEMPTEST2:
SQL
//USE AA
SELECT TOP 1000
[AA],
[BB]
FROM [NtwkMst];
//DROP TABLE TEMPTEST;
//DROP TABLE TEMPTEST2;
DROP FIELDS [mktId], [uniqueNbr];

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@autopilot - Your solution worked, Now it's about understamding the difference between Qlikview and SQl syntax. Thanks!

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
My previous comment is the most straight-forward solution based on what you provided...however, below will provide some explanation for how to perform the load from temp table approach you originally outlined using qlikview.
You were definitely in the right direction from a logic perspective....just a few areas related to syntax were off.
If you load data in qlikview using the LOAD or SQL Select statements the table resides in the qlikview app memory. To access the table later in the script (for any number of potential reasons), you must use
LOAD...RESIDENT <tablename>;
Here's an example.
// use load statement to create temp table 1 from source qlikview data file (i.e. qvd)
TempTable1:
LOAD
field1,
field2,
field3
from source.qvd(qvd);
// use sql select statement to create temp table 2 and then
// left join to temp table 1 on TempTable1.field1 = TempTable2.sql_field1 by aliasing the field names to be the same
left join (TempTable1)
TempTable2:
SQL Select
sql_field1 as field1,
sql_field2,
sql_field3
from dbo.TempTable;
// use load..resident statement to reload temp table 1 as a new table called Table1
// also alias field name to illustrate simple example of reloading an existing "temp" table
Table1:
LOAD
field1 as Field1,
field2 as Field2,
field3 as Field3,
sql_field2 as SQL_Field2,
sql_field3 as SQL_Field3
RESIDENT
TempTable1;
// remove temp table 1 from qlikview memory as it is no longer needed
drop table TempTable1;

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
I wanted to provide the solution and then take time to write the explanation....which I added in a reply a minute ago.
Let me know if that doesn't make sense. For whatever reason I can't seem to get the community editor to paste the text with the colors for load script so you may want to copy paste into a qv app for better readability.
