I am trying to load data from 2 different databases and I would like to use the results from the first database table to limint the records selected from the second database. Can I do some type of resident load? Thanks.
FROM SOURCE.TABLE C
WHERE C.VAR1 > '00000000' ;
ODBCCONNECT TO DATASOURCE2;
A.VAR4, B.VAR5, D.VAR6
FROM RESIDENT TABLE1 C
JOIN SOURCE.TABLEB B ON SUBSTR(B.SEQUENCE,1,10)=C.SEQUENCE
JOIN SOURCE.TABLEA A ON A.SEQUENCE=SEQUENCE
JOIN SOURCE.TABLEA D ON D.SEQUENCE_NUM = B.SEQUENCE_NUM
WHERE C.VAR1 > '00000000' AND D.VAR6 IS NOT NULL ;
It's not exactly clear what you're trying to do... A few notes that may help. First, when you load data from two databases, before you connect to the 2nd database, there shuold be a statement DISCONNECT. Second, when you load from a resident table, the syntax is tablename: LOAD ... ... RESIDENT PreviouslyLoadedTable WHERE...;
Basically I am trying to use the results from the 1st table to limit the number of rows I select from the 2nd table. I was hoping to not have to pull in all the records from the 2nd table then do a join on the 2 tables. The below does work but I have to first pull in all the records from the 2nd data source.