Loading a column from two databases

[#1]

So, I'm trying to load data from two different databases, and can't figure it out.

We'll say DB1 and DB2, which both have the field Serial Number, but DB1 has the field Customer Name and DB2 has the field Expiration Date.

What I want is to select all three columns, but only those with a serial number in DB1. I have to do it in the database query, as DB1 has ~300k rows, while DB2 has 10 million.

I can't figure out how to only get the serial numbers and expiration dates from DB2 where the serial number is in DB1.

Help?

[rwunderlich]

You need a $() around the variable ref:

WHERE    serialnumber IN($(vSerials))

-Rob

[Sokkorn]

Hi,

You may try this

[DB1]:

LOAD Distinct

    Concat([Serial Number],',')    AS [SerialNo];

Select [Serial Number] From DB1.Table1;

Let varSerial = Peek('SerialNo',0,'DB1');

Drop Table [DB1];

[DB2]:

LOAD

    [Serial Number],

    [Customer Name],

    [Expiration Date];

SELECT

    [Serial Number],

    [Customer Name],

    [Expiration Date]

FROM DB2.Table2 Where [Serial Number] IN ($(varSerial));

Regards,

Sokkorn