Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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

6 Replies
biester
Specialist

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

johnw
Champion III

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)
;

biester
Specialist

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

Not applicable
Author

Hello,

that helps!
Thanks a lot!
Thilo

Not applicable
Author

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





biester
Specialist

Hi,

I'd say you cannot use "where exists" in the SQL clause; you must use it in the LOAD-clause.

Rgds,
Joachim