| Author |
Retrieving Child Record without Matching Parent Record?
|
|
| MikeTheBike@tpgi.com.au 2005-02-23, 8:55 pm |
| Hi,
I am using C5.5W (not ABC) and have created a View with the child file
as the Primary file and joining the Parent file as the secondary file
with the 2 field key defined for the relationship(using the fields from
the child file to complete the Join) and projecting the 2 fields of the
parent file. Then if I use a filter to check for blank('') values in
the parent fields I get no records returned. If I use the NULL function
or compare the fields to 0 I get records returned that do have a
matching Parent as well as the one record that I was expecting in the
first place.
Is it possible to create a View to do this with TopSpeed files.
Thanks, Michael Holberton
| |
| MikeTheBike@tpgi.com.au 2005-02-24, 3:55 am |
| Here's the code I have tried:
vwItemLoc VIEW(Item)
JOIN(loc:kCustomer, itm:ITM_CUST, itm:ITM_LOC)
PROJECT(loc:LOC_CUST, loc:LOC_NUM)
END
END
! vwItemLoc{PROP:Filter} = 'NULL(loc:LOC_CUST) OR NULL(loc:LOC_NUM)'
vwItemLoc{PROP:Filter} = 'loc:LOC_CUST = '' OR loc:LOC_NUM ='''
I want to find Item records that have a non existent Customer/Location
combination.
| |
|
| Clarion does a LeftOuter Join as a default.
So you need to do the reverse.
Make Customer your Primary Table.
Then on your secondary table if blank or zero you want to include it.
Jim Mumford
| |
| MikeTheBike@tpgi.com.au 2005-02-26, 3:55 am |
| > So you need to do the reverse.
>
> Make Customer your Primary Table.
I need to find the Item records whose Customer/Location combination
does not appear in the Location Table.
> Then on your secondary table if blank or zero you want to include it.
If I had the Location table as the Primary table and the Item table as
the secondary table then wouldn't I only get Item records with matching
Customer/Location combination in the Location table?
|
|
|
|