
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Union or Concatenate w/out Duplicates
Hello,
I have 2 tables with list values in separate databases (Oracle and MySQL).
I want to load both lists into 1 table in QV (V9).
I can do so using 'concatenate' - fine.
But I want to eliminate duplicates from the list .. how can I achieve this?
Example:
List1:
ID, Name, Source
1, A, Oracle
2, B, Oracle
3, C, Oracle
List2:
ID, Name, Source
1, A, MYSQL
8, X, MYSQL
9, Y, MYSQL
The goal is:
MyList:
ID, Name, Source
1, A, Oracle
2, B, Oracle
3, C, Oracle
8, X, MYSQL
9, Y, MYSQL
How can I achieve a concatenate w/out having element 'A' duplicated?
Thanks for any help,
Thilo


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi Thilo,
if I'm getting you right, then just try as described in the manual:
Tab1:
select Name, Number from Persons.csv;
add load Name, Number from NewPersons.csv where not exists(Name);
I'm posting a demo here. Hope that helps.
Rgds,
Joachim


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Add load actually means something different, a mistake I used to make routinely. ADD only makes a difference during partial reloads. I don't know the details because I don't do partial reloads. The example still works because the default of the load IS concatenation, but I like to be explicit when I'm concatenating, specifying both concatenate and the table I'm concatenating to. That way there's no confusion, and nothing goes wrong if I insert some script in the middle.
In any case, Joachim's suggestion of concatenation plus where not exists is the approach I would use in this case. I'd just do it slightly differently:
MyList:
LOAD * INLINE [
ID, Name, Source
1, A, Oracle
2, B, Oracle
3, C, Oracle
];
CONCATENATE ([MyList])
LOAD * INLINE [
ID, Name, Source
1, A, MYSQL
8, X, MYSQL
9, Y, MYSQL
]
WHERE NOT EXISTS(ID)
;


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi,
of course John is right and of course it should not be "add" but "concatenate" - I just focussed on the "where not exists" and didn't notice the "add". That cometh from copy and paste ...
Sorry for my mistake,
Rgds,
Joachim

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hello,
that helps!
Thanks a lot!
Thilo

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi John,
The samesoultion given by you here ,i tried,but to my bad luck am not getting the expected result rather am getting some error in "where exists " clause.
Difference is only amfetching data from oracle database.
My script is
//using concatenate
concatenate
([tab3])
load
VALUE,"SCENARIO_NUM";
sql
select * from
ADEXAUSER."TEST_ADR_ACT_CDLPS";
where exists ("SCENARIO_NUM");
Please let me know where am missing.
Thanks,
Mahasweta


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi,
I'd say you cannot use "where exists" in the SQL clause; you must use it in the LOAD-clause.
Rgds,
Joachim
