why is this join not working?

[amien]

ODBC CONNECT TO DB2;
LOAD FileNumber, Zone,
 Value;
SQL SELECT *
FROM BLA1;

LEFT JOIN

ODBC CONNECT TO DB2;
LOAD AccountNumber AS FileNumber,
 Contact,
 VoiceMail,
 Busy;
SQL SELECT *
FROM BLA2;

this should work .. but when i make a pivot table with Zone as dimension and a COUNT(FileNumber) as expression.. when i still have an empty zone with a count(FileNumber). Those Filenumber are the ones that are in BLA2 but not in BLA1 .. i only need all records from DB1 and the additional values of DB2 if match on Filenumber. in SQL LEFT JOIN should work .. but i dont know why this is not working

[Oleg_Troyansky · Accepted solution]

Amien wrote:
ODBC CONNECT TO DB2;
LOAD FileNumber, Zone,
 Value;
SQL SELECT *
FROM BLA1;
ODBC CONNECT TO DB2;
LEFT JOIN
LOAD AccountNumber AS FileNumber,
 Contact,
 VoiceMail,
 Busy;
SQL SELECT *
FROM BLA2;

the code above should work - the ODBC CONNECT needs to precede Left Join. If it's connecting the the same database, it's even redundant. The rest of the syntax looks OK.

Oleg

[Anonymous]

If you're connecting to a different database, there should be the DISCONNECT statement before the second connect.
I never tried to skip DISCONNECT - may be it is not really necessary (?)

[amien]

perfect .. thanks al!

[amien]

according to the manual, the connection is automaticly disconnected when a new CONNECT job is executed