Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello all
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.
example
TAB_1:
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')
;
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")
;
I think this, but untested:
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
;
Dohh, that's what I do at the moment in SQL, I didn't realise I could do the same in QV, will test later.
Many thanks John
Hi Again
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.
Any other ideas please?
I have attached the file so you can see in detail what I am trying to do.
Many thanks.
Greenee
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.
Thanks John