Cross-Database join between SQL Server & Teradata ODBC because one DB too big to load

[#1]

I'm new to Qlikview tool but I have a situation which I'm hoping for a solution..

I've got two tables in two very different databases that I'd like to load in Qlikview. One is SQL Server ODBC connection and the other is Teradata (TDW) connection.  There is one column from each table that has the same type of information so I'd like to bring **only** the matching values from each database into the Qlikview tool.  I know I can connect to one database, load the data, then connect to second database and load it's data, but the problem is that there are several million rows of data in the Teradata (TDW) database and it takes forever to load all the data.  What I'm hoping i could do is to limit the amount of data load by performing a cross-database inner join in Qlikview so that only the matching rows found in both SQL Server and Teradata database are returned.  I've tried something like the following inner join but it returns the following error as if I'm only connected to the last connection in my connect string instead of both Teradata and SQL Server.

ODBC CONNECT32 TO [SQL Server DB] (XUserId is XXXXXXXXXXXXXXXXXXXXX, XPassword is XXXXXXXXXXXXXXXXXX);
ODBC CONNECT32 TO [Teradata DB] (XUserId is XXXXXXXXXXXXXXXXXXXXX, XPassword is XXXXXXXXXXXXXXXXXX);

SELECT a.TicketNbr, b.Ticket_Num

FROM   TDW_base_views.Table1 a,
            SQLServer.dbo.Table2 b

WHERE  a.TicketNbr=b.Ticket_Num;

Error:

SQL##f - SqlState: 37000, ErrorCode: 4294963590, ErrorMsg: [Teradata][ODBC Teradata Driver][Teradata Database] Syntax error: expected something between the word 'dbo' and '.'.

I understand that my inner join cross-database sql may not be the right approach, and I am open to any other solutions/suggestions.  Any help would be greatly appreciated.

[rbecher]

Hi,

this is not possible in QlikView. You can only load data from one connection per statement. A possible solution would be to load all Ticket_Num from SQL Server in a QlikView table and then for-loop over the table to execute single SQL selects on Teradata with the Ticket_Num in Where Clause.

// This is an example:

Tickets_SQLServer:

SQL SELECT Ticket_Num FROM SQLServer.dbo.Table;

LET rows = NoOfRows('Tickets_SQLServer');

FOR i=0 TO $(rows)-1

     LET TicketNo = peek('Ticket_Num',$(i),'Tickets_SQLServer');

     Tickets_Teradata:

     SQL SELECT * FROM TDW_base_views.Table WHERE TicketNbr = $(TicketNo);

NEXT

- Ralf

[#3]

Dear,

you can't perform the where condition like that in qlikview..

Instead of that one you need to perform  inner join between two tables like.

first load the Table from Sql server DB.

then load the table from TeraData DB.

perform the inner join between these two tables based on the Key Column.

example:

establish the connectivity from sqlserver DB.

Table1:

Load the first table from Sqlserver DB.

establsih the conncetivity from Teradata DB

Inner Join(Table1)

load the second table from TeraData DB

thw above tables will join based on Id key.

Thanks,

Mukram.

[#4]

Thanks Ralf.  We will give that a try.

[#5]

Thanks Ralf and Mukram.  We will give these a try.