I would like to know how to load data from a second ODBC source where the first load has extracted the ID numbers of records that I want in the second load script.
LOAD "Field_1" AS ID, "Field_2" AS ID_REF, "Field_1"&"Field_2" as Unique_ID, "Field_3" as Task_Date ;
SET NULLDISPLAY='#Null'; SQL SELECT * FROM ODBC."TABLE_NAME_A" WHERE ("Field 3" >= '01-DEC-2008') ;
Then subsequently on TAB_2 I want the script to load fields where Unique_ID is equal to the Unique_ID's loaded in the first part of the script because Field_3 doesn't exist in the 2nd ODBC source and I don't want to load everything in from that table. A bit like a join in Access where the record are equal. BTW, I want the nulls to be like that so I can find data inconsistencies.
LOAD "Field_1" AS ID, "Field_2" AS ID_REF, "Field_1"&"Field_2" as Unique_ID, ;
SET NULLDISPLAY='#Null'; SQL SELECT * FROM ODBC."TABLE_NAME_B" WHERE (TAB_2 "Field_1"&"Field_2" = TAB_1 "Field_1"&"Field_2") ;
Thank you for any guidance and code adjustments you can give. Many thanks Greenee
TAB_2: LOAD "Field_1" AS ID, "Field_2" AS ID_REF, "Field_1"&"Field_2" as Unique_ID WHERE EXISTS(Unique_ID,"Field_1"&"Field_2") ; SET NULLDISPLAY='#Null'; SQL SELECT * FROM ODBC."TABLE_NAME_B" ;
Or just join the tables together in SQL, again untested and almost certainly with syntax errors, and doing an inner join which may not be what you wanted:
TAB_2: LOAD ID ,ID_REF ,date(date#(Task_Date,'DD-MMM-YYYY')) as Task_Date ,blah_blah_blah SQL SELECT A.Field_1 as ID , A.Field_2 as ID_REF , A.Field_3 as Task_Date , B.blah_blah_blah as blah_blah_blah FROM ODBC.TABLE_NAME_A A , ODBC.TABLE_NAME_B B WHERE A.Field_3 >= '01-DEC-2008' AND B.Field_1 = A.Field_1 AND B.Field_2 = A.Field_2 ;
the first sugestion not at all, the second doesn't seem to work either. The second load sequence doesn't reference the first set of data that has been collected. When I do a limited load of 10 lines, the second script gets a different 10 lines/ID's that probably do exist in the original tables, but that isn't what I want.
I'm not sure how much more I can help. Your two choices, as I see them, are some sort of where exists(...) or doing the desired join in SQL. If the syntax I gave you is incorrect (likely), or is being translated to the real example incorrectly, or if a limited load causes its own problems, I can't test it, so can't really fix it. I could put together an example with inline loads, but the syntax on those differs some from the syntax on SQL, and I can't mimic the second method at all, so it probably wouldn't be particularly helpful.