Union or Concatenate w/out Duplicates

[#1]

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

[biester]

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]

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]

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

[#5]

Hello,

that helps!
Thanks a lot!
Thilo

[#6]

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]

Hi,

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

Rgds,
Joachim