Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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);