INNER JOIN syntax

[Anonymous]

Hello my fellow QV enthusiasts.  I am having a time trying to figure out how to convert the SQL query (from Teradata) below into a QlikView load script.  My issue is that one of the tables, table1, has about 100 million records in it so it isn't feasible for me to load the entire table and then do a inner join to table 2.  Any ideas as to how this could be rewritten for QlikView I would greatly appreciate.

SELECT                   table1.first_name
                                ,table1.last_name
                                ,table1.date
                                ,table1.birthdate        
                                          
FROM                     TABLE_A table1
INNER JOIN     TABLE_B table2

ON                            table2.first_name = table1.first_name
                                AND table2.date = table1.date       
                                AND table1.valid = 1
                                AND table1.expir_dt =DATE'9999-12-31'                                                       
WHERE table2.wae_prd_dt = DATE'2016-08-01';

[Anonymous]

1. Load Table 2 first and make inner join  Table1 , So, All table1 records are not loaded!!

2. Try with KEEP

Are you having TPT connector to extract teradata data, so it will be fast!?

[Anonymous]

I am unaware of the TPT connector.  Is it available to personal edition users?

[#4]

NO matter if you have many million of records YOUR problem its you JOIN first question:

Why Do you Join by Column DATE and First Name??? Do you have other Column to JOIN for example IdPovider or IdVendor, IdEmployment  etc, etc.

Check your Columns to JOIN!!!

Regards

[Digvijay_Singh]

May be like this -

1. First load Table1 and create new QVD of table 1.

2. Load  Table 1 from QVD and load table 2 with inner join and where clause like -

LOAD *

From Table1QVD

Inner join

Load *

From Table2

Where exists(expir_dt&'-'&valid,'9999-12-31-1')

You may need to create new field in table 1 to combine both of your fields which need comparison in exists in case it doesn't work as above, also date formatting needs to be verified.

[Peter_Cammaert]

Any reason why you want to forcibly move away from a SQL JOIN? Since the data doesn't need to be transferred before the JOIN, IMHO a SQL JOIN would offer at least a big performance advantage.