Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

how to use temp table in sql queries

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;

Tags (2)
1 Solution

Accepted Solutions
MVP
MVP

Re: how to use temp table in sql queries

maybe

table1:

SQL

SELECT * FROM Table1 WHERE Firm_Key='myFilterValue';


table2:

load *

where exists(Account_Index);

SQL

SELECT * FROM Table2;

12 Replies
MVP
MVP

Re: how to use temp table in sql queries

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?

Not applicable

Re: how to use temp table in sql queries

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.

Not applicable

Re: how to use temp table in sql queries

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;

Not applicable

Re: how to use temp table in sql queries

table1:

SQL SELECT *

FROM Table1

WHERE Firm_Key='myFilterValue';

table2:
SQL SELECT *

FROM Table2

where exists(Account_Index);

Not applicable

Re: how to use temp table in sql queries

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.

Not applicable

Re: how to use temp table in sql queries

Hi,

tblFiltered:

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

inner join(tblFiltered)


SQL SELECT * FROM Table2;

MVP
MVP

Re: how to use temp table in sql queries

maybe

table1:

SQL

SELECT * FROM Table1 WHERE Firm_Key='myFilterValue';


table2:

load *

where exists(Account_Index);

SQL

SELECT * FROM Table2;

Not applicable

Re: how to use temp table in sql queries

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.

Not applicable

Re: how to use temp table in sql queries

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.

Community Browser