[focus-l] Duplicates in join 'from' field
- From: "Derek Croxton" <CDCroxton@xxxxxxxx>
- To: <focus-l@xxxxxxxxxxxxx>
- Date: Fri, 07 May 2004 09:56:16 -0400
Let's say you have two tables that you want to join, but the join field is not
unique in either table. For one value, table A has 3 rows with that value in
the join field, and table B has 4 rows. If you do an inner join in SQL, you
will get 12 rows back: one for each row in A, each one matched to all 4 rows
in B.
You can do this join in WebFocus with a join to ALL, BUT only if both tables
are in a relational database. If you have a Focus data source * for example,
if one or both of the tables was created by a hold file * then you end up with
this error:
DUPLICATES IN JOIN 'FROM' FIELD
The query still runs, but you only get 6 rows: the first row in A is matched
to all 4 rows in B, and the other 2 rows in A are returned with NO MATCHES in B.
How do other people deal with this problem? The best solution I have is to
write out the join and table file using SQL, which will return the correct
results. The problem with this is that it pretty much puts an end to using the
GUI (Report Painter), which, while no big deal to me (I always code by hand),
is a potential problem for those here who don't do WebFocus programming all the
time. If there is some "WebFocus" way of accomplishing this inner join, I
would love to know about it.
Sincerely,
Derek Croxton
Other related posts:
- » [focus-l] Duplicates in join 'from' field
