Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     Excluding records in one to many join
Go
New
Search
Notify
Tools
Reply
  
Excluding records in one to many join
 Login/Join
 
<Jonathan Sutter>
posted
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.
 
Reply With QuoteReport This Post
Silver Member
posted Hide Post
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
SUM LAST_NAME
BY EMP_ID
RUN
FILE EDUCFILE
SUM COURSE_CODE
BY EMP_ID
AFTER MATCH HOLD OLD‑NOT‑NEW
END

This syntax will yield the relationship you want.
 
Posts: 44 | Location: New York City | Registered: May 23, 2004Reply With QuoteReport This Post
<Jonathan Sutter>
posted
I'm looking at the MATCH command as you suggest. Everything I'm seeing applies to MAINTAIN procedures. Will this work in other applications?
 
Reply With QuoteReport This Post
Virtuoso
posted Hide Post
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.

join....
table file xyz
where code eq 'abc'
print code
by key
on table hold as hold1
end
-*hold1 has all key records which have the child
table file xyz
where code ne 'abc'
write code
by key on table hold as hold2
end
-*hold2 has all the records not the child
match file hold2
write code
by key
run
file hold1
write code
by key
after match hold as hold3 old-not-new
end
-* hold3 has all the records in hold2 not in hold1
table file hold3
print code by key
end
 
Posts: 1317 | Location: Council Bluffs, IA | Registered: May 24, 2004Reply With QuoteReport This Post
Expert
posted Hide Post
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.
 
Posts: 3811 | Location: Manhattan | Registered: October 28, 2003Reply With QuoteReport This Post
Guru
posted Hide Post
SET ALL=PASS doesn't work on relational files.

If you are using an RDBMS you will want to do a MATCH.
 
Posts: 346 | Location: Melbourne Australia | Registered: April 15, 2003Reply With QuoteReport This Post
Guru
posted Hide Post
You can find MATCH explained in the Creating Reports With WebFOCUS Language Manual - Chapter 15 Merging Data Sources.
 
Posts: 346 | Location: Melbourne Australia | Registered: April 15, 2003Reply With QuoteReport This Post
Platinum Member
posted Hide Post
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.

HTH, Suzy
 
Posts: 124 | Location: Lebanon, New Hampshire | Registered: April 24, 2003Reply With QuoteReport This Post
Gold member
posted Hide Post
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;
END
TABLE FILE CHAR
WRITE FLAG NOPRINT BY CLAIM_NO
WHERE TOTAL FLAG EQ 0
ON TABLE HOLD AS WHO_I_WANT
END

JOIN CLAIM_NO IN WHO_I_WANT TO CLAIM_NO IN CLAIMS AS JOIN1
END
TABLE FILE WHO_I_WNAT
PRINT claiminformation
END

(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
Then,
TABLE FILE CLAIM
PRINT Claiminformation
WHERE CLAIM_ID IN FILE WHOIWANT
END
 
Posts: 60 | Location: 2 penn | Registered: May 22, 2003Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     Excluding records in one to many join

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.