topic Re: how to use temp table in sql queries in QlikView

how to use temp table in sql queries

Tue, 18 Nov 2014 21:00:49 GMT

Hello,

I want to limit the results from one table on the 'Firm_Key' value. I also want to use the same limitation on several other tables, but which do not have the column 'Firm_Key'. The tables do share an 'Account_Index' column. All my attempts give errors. Thank you for your help.

tblFiltered:

SQL SELECT * FROM Table1 WHERE Firm_Key='myFilterValue';

SQL SELECT * FROM Table2 as A
INNER JOIN tblFiltered as B ON A.Account_Index = B.Account_Index;

Re: how to use temp table in sql queries

maxgro — Tue, 18 Nov 2014 21:57:17 GMT

tblFiltered:

SQL SELECT * FROM Table1 WHERE Firm_Key='myFilterValue';

inner join (tblFiltered)
SQL SELECT * FROM Table2 as A;

Qlik will inner join tblFiltered (Table1 in SQL) and the second table (Table2 in SQL) using the field(s) with same name, Account_Index

or you want 2 tables?

Re: how to use temp table in sql queries

Wed, 19 Nov 2014 03:01:29 GMT

Thanks, - the query seems to take longer than it should seems to be retrieving everything in Table2 for each item in tblFiltered. How do I retrieve only the values from Table2 that have an Account_Index value found in tblFiltered? When I write:

SQL SELECT * from Table2 as A WHERE A.Account_Index = tblFiltered.Account_Index;

I get an error for trying to use 'tblFiltered'.

My basic problem is I don't understand when and how I can use that table name of tblFiltered.

Also - yes, how would you script it for separate tables? I have a Table3, and a Table4 that I also want only to bring in rows where a matching Account_Index value appears in my tblFiltered version of Table1.

Re: how to use temp table in sql queries

Wed, 19 Nov 2014 13:27:27 GMT

Also - I'm trying another method, but I get an 'ODC Read Failed' error..

SQL SELECT * FROM (SELECT Account_Index FROM Prosurv.dbo.R_Acct WHERE Firm_Key='RJFS' AND Unit_Key='ACX') as A,
Prosurv.dbo.Account_Holder as B WHERE A.Account_Index = B.Account_Index;

Re: how to use temp table in sql queries

Wed, 19 Nov 2014 13:52:09 GMT

table1:

SQL SELECT *

FROM Table1

WHERE Firm_Key='myFilterValue';

table2:
SQL SELECT *

FROM Table2

where exists(Account_Index);

Re: how to use temp table in sql queries

Wed, 19 Nov 2014 14:09:53 GMT

This gives a syntax error.
Also - All of Table2 has the field 'Account_Index', but I just want results whose 'Account_Index' value matches to an item in Table1.

Re: how to use temp table in sql queries

Wed, 19 Nov 2014 14:14:45 GMT

Hi,

tblFiltered:

SQL SELECT * FROM Table1 WHERE Firm_Key='myFilterValue';

inner join(tblFiltered)

SQL SELECT * FROM Table2;

Re: how to use temp table in sql queries

maxgro — Wed, 19 Nov 2014 14:15:48 GMT

maybe

table1:

SQL

SELECT * FROM Table1 WHERE Firm_Key='myFilterValue';

table2:

load *

where exists(Account_Index);

SQL

SELECT * FROM Table2;

Re: how to use temp table in sql queries

Wed, 19 Nov 2014 14:21:49 GMT

Thanks - that was suggested earlier, but there are too many results. My tblFiltered only has 50 results, and the other table is returning thousands, but I only want the 50 that also appear in tblFiltered. Their common field is 'Account_Index'. This works superfast and easy in SQL Studio. I don't get what the problem is here.

Re: how to use temp table in sql queries

Wed, 19 Nov 2014 14:24:57 GMT

edit:

yes this does work. I don't know why it takes 20 minutes to run, though, when SQL Server processes the same query almost instantly. Thank you.

Re: how to use temp table in sql queries

Wed, 19 Nov 2014 14:28:02 GMT

Sorry forgot I can't use the where exists directly on a SQL select:

table1:

SQL SELECT *

FROM Table1

WHERE Firm_Key='myFilterValue';

table2:

load

where exists(Account_Index);

SQL SELECT *

FROM Table2 ;

Where exists first looks to see if a field has been prevouly loaded called Account_Index (which in this case has been in table one), it then looks to see if the Account_Index field exists in the new table and only brings back values where they match to table 1.

Re: how to use temp table in sql queries

jagan — Wed, 19 Nov 2014 14:32:38 GMT

Hi,

Try like this

tblFiltered:

SQL SELECT * FROM Table1 Tab1

INNER JOIN Table2 Tab2 ON Tab2.KeyField = Tab.KeyField AND Firm_Key='myFilterValue'

INNER JOIN tblFiltered as B ON A.Account_Index = Tab2.Account_Index;

In select specify field names using in Dashboard.

Even it is slow, create indexes on the key fields.

Replace keyfield with your key fields.

Hope this helps you.

Regards,

Jagan

Re: how to use temp table in sql queries

Wed, 19 Nov 2014 15:26:26 GMT

Ok, I was wrong above, yes this does work. I don't know why it takes 20 minutes to run, though, when SQL Server processes the same query almost instantly. Thank you.