I have two tables with a one to many relationship. For explanation purposes, let's say the primary table is CLAIM and the related table with multiple related records is CHARACTERISTIC. I need to work with a recordset that includes all CLAIM records that do NOT have a particular characteristic.
While I suspect the solution will involve mutliple steps, I haven't had any luck working with HOLD files. In any event, no matter how I think about it, I find myself needing to perform some sort of join that would return only those CLAIM records that do NOT have a match in the other table.
My limited experience is making this a tough nut to crack, though I expect the solution will turn out to be simple.
I think your best bet is to use the MATCH command in this case. It is well described in the FOCUS manual. This command will easily get you the records that appear in your first file without any corresponding records in the second. Here is a sample syntax from the manual:
MATCH FILE EMPLOYEE
AFTER MATCH HOLD OLD‑NOT‑NEW
This syntax will yield the relationship you want.
I'm looking at the MATCH command as you suggest. Everything I'm seeing applies to MAINTAIN procedures. Will this work in other applications?
Match works with hold files as well. You want to ensure you get only those without the data, so a request might look as follows Note does not exclude multiple records necessarily, a proof of concept. I used the write to reduce to one record per key.
table file xyz
where code eq 'abc'
on table hold as hold1
-*hold1 has all key records which have the child
table file xyz
where code ne 'abc'
by key on table hold as hold2
-*hold2 has all the records not the child
match file hold2
after match hold as hold3 old-not-new
-* hold3 has all the records in hold2 not in hold1
table file hold3
print code by key
Jonathan, read up on SET ALL = PASS, SET ALL=ON and SET ALL = OFF in (which manual? i can't find it)
You'll do your join
SET ALL = PASS
JOIN field in CLAIM TO ALL field IN CHARACTERISTIC AS J!1
then your filters, or screening statements, will be on the characteritic, which is in the GUEST file, not the host.
The setting ALL=PASS allows you to filter on the GUEST file. You don't use it if you're filtering on the HOST file.
IBI's Renee Teatro gives a great presentation on this subject, if you can arrange to get a copy, or see her at summit.
SET ALL=PASS doesn't work on relational files.
If you are using an RDBMS you will want to do a MATCH.
You can find MATCH explained in the Creating Reports With WebFOCUS Language Manual - Chapter 15 Merging Data Sources.
You have discovered that there are 2 MATCH commands in Focus. One is used in MODIFY and MAINTAIN to maintain Focus databases. But the other is used in the reporting environment. It is extremely useful, and is more of a MATCH/MERGE where data can be thought of as being merged horizontally, rather than vertically.
The problem that you are encountering is that there are multiple characteristics and you are looking for something that isn't there. Whether JOIN or MATCH, they work better when we are looking for something specific. In this case, if you go to the JOIN file and say show me anything that is NE 'ABC', and a particular claim has ABC and DEF, he will still show up on the report. So, here is my suggestion. First, create a HOLD file of the CLAIMS that do not have that characteristic (no matter how many characteristics they do have.
DEFINE FILE CHAR
FLAG/I5 = IF CHARACTERISTIC EQ '&VALUE' THEN 1 ELSE 0;
TABLE FILE CHAR
WRITE FLAG NOPRINT BY CLAIM_NO
WHERE TOTAL FLAG EQ 0
ON TABLE HOLD AS WHO_I_WANT
JOIN CLAIM_NO IN WHO_I_WANT TO CLAIM_NO IN CLAIMS AS JOIN1
TABLE FILE WHO_I_WNAT
(Faster if you can create that HOLD file in the same format as CLAIM (ie HOLD FORMAT DB2 ,etc.)
Alternatively, if there are not a lot of claims,
HOLD AS WHOIWANT FORMAT ALPHA
TABLE FILE CLAIM
WHERE CLAIM_ID IN FILE WHOIWANT
|Powered by Social Strata|