Where condition in resident load with "outer join"

[Anonymous]

Hi,

I have a question. Can we use,

Load field name, field name1, field name2 from TableA;

Load * from Table B;

left join

Load * from Table C;

Outer Join (Table A)

Load field name, field name1, field name2, field name3

Resident Table B

where not exists (field name);

Can we use the "where not exists" in resident load and join this table to already loaded table?

[#2]

You can do that.

For you to do a outer join to B from C, please use the following format.

left join (B)

load * resident C;

And then when you want to outer join to A, use the following syntax

outer join(A)

load .......

resident B

where not exists ( <previously loaded field>, <field being loaded>);

Make sure <previously loaded field> and <field being loaded> are having different field names. Use aliasing.

Thanks,

Ram

[jaimeaguilar]

Hi,

I don't know what is your expected result, but I think that script will run without errors,

regards

[Anonymous]

Hi Ramkumar,

Thanks for your response. What if i have the same field name. Can i use (where not exists (common field name)?

[#5]

Hi Vamshi, it is just for readability. You can still use the same name.

[#6]

you can also specify one argument, where it implies the field name is common.

[Anonymous]

I have this script,

Table A:

Load Fieldname1, Fieldname2.......Fieldname48

from Table A;

Table B:

Load Fieldname1, Fieldname2.......FieldnameXX

From Table B;

Left join

Load Fieldname1, Fieldname2.......FieldnameYY

from Table C;

Outer join (Table A)

Load *

Resident Table B

where not exists (Fieldname1);

Store TableA into TableA.qvd (qvd);

So, Finally, there are 48 fields in table A and 52 fields in Table B (formed by joining Table B and Table C). These 48 fields are common in both tables. Say, for suppose, i have 7814 x 48 in Table A. I should get more number of rows after i run this script (because it will bring in the extra rows and 4 extra columns from table B).

But i am still getting 7814x48 and the TableA.qvd is not updating.

But when i use the following script, it is working.

Table A:

Load Fieldname1, Fieldname2.......Fieldname48

from Table A;

Table B:

Load Fieldname1, Fieldname2.......FieldnameXX

From Table B;

Left join

Load Fieldname1, Fieldname2.......FieldnameYY

from Table C;

Store TableB into TableB.qvd(qvd);

Drop TableB;

Outer join (Table A)

Load *

From Table B.qvd(qvd)

where not exists (Fieldname1);

Store TableA into TableA.qvd (qvd);

It is giving me 9509x52 in the final table and the TableA.qvd (qvd) is updating to the latest.

So, i want to know can we use the outer join to already loaded table using resident and where condition?