4 Replies Latest reply: Nov 16, 2012 3:59 PM by Keith Montoya RSS

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

      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.