Where Not Exists

[#1]

Hi all,

I have a table of data that contains transactions with an record ID field and a parent ID field. There is a parent-child relationship between some of the records (some parents have child records) but not all (some records don't have children).

I want to use where not exists to exclude the parent records that have children in the script, whilst keeping those records that don't have children.

Im using

LOAD

     *

Resident Tablename Where Not Exists (PARENTID,RECORDID);

So this should check whether the RECORDID exists in any of the PARENTID rows and exclude the record if it does. If the RECORDID does not exist anywhere in the PARENTID field then it should load it.

When I run this I get an error saying Out of virtual / logical memory.

Any ideas?

Dan

[#2]

Hi Dan,

I am not aware of the table size or memory its running on, but if you have PARENTID,RECORDID in the same table, in the resident load if you add isnull(RECORDID) condition you would get all PARENTID without RECORDID and then apply where not exists because PARENTID can have more than one RECORDID. This would atleast reduce resident load size.

Kiran.

[#3]

Hi.

Perhaps the problem is because the field names of the new table match the names of the table of Parents and that creates an automatic join. Try to assign an alias, p.e.:

Parent:

LOAD * Inline

[PARENTID, PAR_DUMMY

1, A

2, B

3, C

4, D];

Child:

LOAD * Inline

[RECORDID, CHI_DUMMY

1, A

1, B

5, C

5, D

2, E];

Parent_no_Child:

LOAD

          PARENTID           AS PNC_PARENTID,

          PAR_DUMMY               AS PNC_DUMMY

Resident Parent

Where not Exists(RECORDID,PARENTID);