topic SQL Query From Different Databases in QlikView

SQL Query From Different Databases

Anonymous — Tue, 10 Nov 2009 05:41:53 GMT

I want to write a single SQL query that pulls data in from two different servers/databases rather than doing individual queries and joining or concatenating the results. Is this possible? I don't see how to have two different connectionsactive in the script at the same time.

Example:

SQL Select tableA.*, tableB.*

from Server1.MyDatabase.tableA as tableA, Server2.MyDatabase.tableB as tableB

where tableA.Key = tableB.Key

AW:SQL Query From Different Databases

Tue, 10 Nov 2009 11:15:33 GMT

You have only one connection on the same time. You can load the first table into the memory,switch the connection,load the second table and combinate with the first. Maybe drop the first finaly.

SQL Query From Different Databases

Tue, 10 Nov 2009 15:27:34 GMT

Stepping out from QV.

If you are reading data from SQL server then you can use the Linked server technology.

http://msdn.microsoft.com/en-us/library/ms188279.aspx

SQL Query From Different Databases

Tue, 10 Nov 2009 15:56:48 GMT

The easiest is to make a loop with a change of connection between each SQL Statement

For I=1 to 2

IF I= 1 THEN Connect xxxxxxxxx END IF

IF I=2 THEN Connect xxxxxxx END IF

SQL_TABLE:

SQL Select tableA.*, tableB.*

from Server1.MyDatabase.tableA as tableA, Server2.MyDatabase.tableB as tableB

where tableA.Key = tableB.Key

next i

Something like that...

Or you copy twice the SQL Statement with a connect between each of them

CONNECT TO xxxxxx

MyTable:

SQL xxxxxxx

CONNECT TO yyyyy

Mytable:

SQL xxxxxxx

As SQL are equals, everything will be in the same table.

rgds

SQL Query From Different Databases

thanomjit — Tue, 10 Nov 2009 16:01:51 GMT

Other Simple Method: by

You can load resident 2 table and take it join in Edit script 's Qlikview

Connect to Server1.MyDatabase

tblTableA:

Load

SQL select tableA.*

From Server1.MyDatabase.tableA as tableA

Connect to Server1.MyDatabase

tblTableB:

Load

SQL select tableB.*

From Server2.MyDatabase.tableB as tableB

SQL Query From Different Databases

Anonymous — Tue, 10 Nov 2009 22:53:47 GMT

Thanks guys. I know all the standard ways to do it, which were the ones I wanted to avoid. So I guess the simple answer is No, you can't run a single query against multiple data sources at the same time.

Thanks anyway!

SQL Query From Different Databases

Tue, 10 Nov 2009 23:02:07 GMT

Just being curious here. Why do you want to avoid the standard ways? Any specific benefits?

SQL Query From Different Databases

Anonymous — Thu, 12 Nov 2009 00:12:28 GMT

It was from one of our DBAs. He lives in the MS SQL Server Stored Procedure world and was looking to leverage his SQL writing skills more than conform to QlikView scripting. LOL

SQL Query From Different Databases

Thu, 12 Nov 2009 21:23:30 GMT

hehe. LOL.

I was investigating different scenarios in my head why it would be a good idea but never thought of the most obvious one: because the developer wants to... 😄