I'm pretty sure I ran into this before and got some kind of answer from these very forums, but I can't seem to find that entry...

The problem I'm running into is that I have a (largish) data file from which I want to return a subset based on what permissions we gave the user who's looking at the data. To achieve this, we create a few small FOCUS files with, for example, the departments someone is allowed to view data about and the product lines concerning them. That results in a query like this:

JOIN INNER DEPARTMENTID IN SALES TO DEPARTMENTID IN FOCCACHE/DEPARTMENTSET END
JOIN INNER PRODUCTLINEID IN SALES TO PRODUCTLINEID IN FOCCACHE/PRODUCTLINESET END
TABLE FILE SALES
PRINT *
END

The problem is that the result still contains all the records from SALES, not at all limited to those departments and product lines in the other tables!

Why is this and how do I make WF behave properly WRT inner joins?

Let's add an example. Say we have:

TABLE FILE CAR
PRINT CAR
WHERE CAR EQ 'TOYOTA' OR 'DATSUN' OR 'JAGUAR' OR 'TRIUMPH';
ON TABLE HOLD AS LIMITCAR FORMAT FOCUS INDEX CAR
END

TABLE FILE CAR
PRINT COUNTRY
WHERE COUNTRY EQ 'JAPAN' OR 'W GERMANY';
ON TABLE HOLD AS LIMITCOUNTRY FORMAT FOCUS INDEX COUNTRY
END

JOIN INNER CAR IN CAR TO CAR IN LIMITCAR AS J0 END
JOIN INNER COUNTRY IN CAR TO COUNTRY IN LIMITCOUNTRY AS J1 END
TABLE FILE CAR
PRINT CAR
BY COUNTRY
END

The expected output is:

 COUNTRY | CAR
---------+--------
 JAPAN   | DATSUN
         | TOYOTA

After all, JAGUAR and TRIUMPH are not in LIMITCOUNTRY, while for W GERMANY no cars are in LIMITCAR.

However, the actual output is:

 COUNTRY   | CAR 
-----------+------------
 ENGLAND   | JAGUAR 
           | JENSEN 
           | TRIUMPH 
 FRANCE    | PEUGEOT 
 ITALY     | ALFA ROMEO 
           | MASERATI 
 JAPAN     | DATSUN 
           | TOYOTA 
 W GERMANY | AUDI 
           | BMW 

Looking at those results... Isn't it just GERMANY nowadays, since they took down the wall in Berlin and all? And isn't JENSEN from Australia?

This message has been edited. Last edited by: Wep5622,

Try it this way

quote:

SET ASNAMES = ON
TABLE FILE CAR
PRINT CAR
WHERE CAR EQ 'TOYOTA' OR 'DATSUN' OR 'JAGUAR' OR 'TRIUMPH';
ON TABLE HOLD AS LIMITCAR FORMAT FOCUS INDEX CAR
END

TABLE FILE CAR
PRINT COUNTRY AS CNTRY
WHERE COUNTRY EQ 'JAPAN' OR 'W GERMANY';
ON TABLE HOLD AS LIMITCOUNTRY FORMAT FOCUS INDEX COUNTRY
END

JOIN INNER CAR IN CAR TO CAR IN LIMITCAR AS J0 END
JOIN INNER COUNTRY IN CAR TO CNTRY IN LIMITCOUNTRY AS J1 END
TABLE FILE CAR
PRINT CAR
BY CNTRY
WHERE COUNTRY EQ CNTRY
END

The INNER keyword is a relatively new addition to the "traditonal" join syntax. I suspect the JOIN is being treated as a where-based ("conditional") join, with no condition specified -- hence the result is a cartesian product.

Try the conditional-join syntax, with the equality condition explicated in a WHERE clause.

quote:

Originally posted by RSquared:
Try it this way

quote:

SET ASNAMES = ON
TABLE FILE CAR
PRINT CAR
WHERE CAR EQ 'TOYOTA' OR 'DATSUN' OR 'JAGUAR' OR 'TRIUMPH';
ON TABLE HOLD AS LIMITCAR FORMAT FOCUS INDEX CAR
END

TABLE FILE CAR
PRINT COUNTRY AS CNTRY
WHERE COUNTRY EQ 'JAPAN' OR 'W GERMANY';
ON TABLE HOLD AS LIMITCOUNTRY FORMAT FOCUS INDEX COUNTRY
END

JOIN INNER CAR IN CAR TO CAR IN LIMITCAR AS J0 END
JOIN INNER COUNTRY IN CAR TO CNTRY IN LIMITCOUNTRY AS J1 END
TABLE FILE CAR
PRINT CAR
BY CNTRY
WHERE COUNTRY EQ CNTRY
END

Something similar is what I ended up doing as a workaround (I added dummy columns with a fixed value to the limit-tables). I was hoping that I was just overlooking some way to make WF treat inner joins as inner joins...

quote:

JOIN INNER DEPARTMENTID IN SALES TO DEPARTMENTID IN FOCCACHE/DEPARTMENTSET END
JOIN INNER PRODUCTLINEID IN SALES TO PRODUCTLINEID IN FOCCACHE/PRODUCTLINESET END

quote:

The problem is that the result still contains all the records from SALES, not at all limited to those departments and product lines in the other tables!

When you do a one-to-one JOIN to a FOCUS data source WebFOCUS will automatically handle it as a short-path/OUTER JOIN (apparently the INNER keyword is not making WebFOCUS change its mind as to how to process those JOINs) so it may make sense that all records from SALES will be displayed, whether or not they have matching records in the cross-reference tables, that is, short-path records are still included in the request.


I can see 2 possible approaches:

1) Add a WHERE condition to filter out short-path records (WHERE DEPARTMENTSET.DEPARTMENTID NE MISSING for instance)

2) If you're sure that entries in DEPARTMENTSET and PRODUCTLINESET are *unique*, that is, you can safely JOIN SALES to those without the risk of "multiplying" records then just JOIN to ALL/MULTIPLE which will make WebFOCUS automatically treat it as an actual INNER join.

JOIN INNER DEPARTMENTID IN SALES TO MULTIPLE DEPARTMENTID IN FOCCACHE/DEPARTMENTSET END
JOIN INNER PRODUCTLINEID IN SALES TO MULTIPLE PRODUCTLINEID IN FOCCACHE/PRODUCTLINESET END

Hopefully that'll work.

I think this should do it:

  
-* File wep01.fex
SET ASNAMES=FOCUS
TABLE FILE CAR
PRINT CAR AS RAC
WHERE CAR EQ 'TOYOTA' OR 'DATSUN' OR 'JAGUAR' OR 'TRIUMPH';
ON TABLE HOLD AS LIMITCAR FORMAT FOCUS INDEX CAR
END

TABLE FILE CAR
PRINT COUNTRY AS CTRY
WHERE COUNTRY EQ 'JAPAN' OR 'W GERMANY';
ON TABLE HOLD AS LIMITCOUNTRY FORMAT FOCUS INDEX COUNTRY
END

JOIN CTRY IN LIMITCOUNTRY TO COUNTRY IN CAR AS J1 
JOIN CAR IN LIMITCOUNTRY TO ALL RAC IN LIMITCAR AS J0 

SET ALL=OFF
TABLE FILE LIMITCOUNTRY
PRINT RAC
BY CTRY
END

Someone posted a very detailed analysis of JOIN on the forum, and I think covered this.

quote:

When you do a one-to-one JOIN to a FOCUS data source WebFOCUS will automatically handle it as a short-path/OUTER JOIN (apparently the INNER keyword is not making WebFOCUS change its mind as to how to process those JOINs) so it may make sense that all records from SALES will be displayed, whether or not they have matching records in the cross-reference tables, that is, short-path records are still included in the request.

But it isn't a one-to-one relationship. It's a one-to-zero-or-one relationship. Records in the left-hand file are not guaranteed to be in the right-hand file (in fact, the whole point is that they aren't). WebFOCUS should not choose the short-path option in these circumstances, that's an incorrect choice.

With regards to solution 2; did you actually try that? It doesn't work. You get the same results as without MULTIPLE or with UNIQUE instead of MULTIPLE, namely the whole set from the left-hand file.

In an ERD (Crow's foot method) the relationship between the files is like this:

 __________              __________
| CAR      |            | LIMITCAR |
+----------+            +----------+
| CAR      |-||------0|-|+CAR      |
| COUNTRY  |-||-\       |__________|
|__________|    |
                |        ______________
                |       | LIMITCOUNTRY |
                |       +--------------+
                \----0|-|+COUNTRY      |
                        |______________|

Meaning there is at most one CAR in LIMITCAR for every reference from CAR.CAR, and at most one COUNTRY in LIMITCOUNTRY for every reference from CAR.COUNTRY.

An INNER JOIN should only result in rows that have common keys in both files involved in the join. That's why it's called an INNER JOIN. What WebFOCUS is doing here is an OUTER JOIN, even though I explicitly stated that I wanted an INNER JOIN.

This message has been edited. Last edited by: Wep5622,

Hi,

Did you run my solution (which does work)?

JOINs between FOCUS files are much older then SQL joins, INNER or OUTER, and the behavior is a bit different.

When you use the JOIN... TO... command, FOCUS will behave as if the JOINed segment is UNIQUE, meaning that if it is not physically there, FOCUS will complete the data with blanks or zeros.

When you use the JOIN... TO ALL... command, the JOINed segment is multiple-valued and then the behavior is affected by the SET ALL command.

In the example I gave you, the first JOIN will limit the CAR file to all values of the LIMITCOUNTRY file.
The second JOIN forces the JOINed LIMITCAR file to behave as a multiple valued segment for the values of the CAR field inthe CAR file. SET ALL=OFF will force only full path records to be retrieved.

Daniel,

Our initial solution was something like what you suggested.

Initially we were limiting the results against only two tables (like in the CAR example), so inverting the join (to discard any results not matching the first file) was fairly straightforward. We must have created one of those multi-value segments inadvertently (that's focusese for a 1-n relationship, I gather?), as the subsequent join with the second limit-table still gave us correct results.

But then a third file got added shortly after and I expect more are possible. Those tables contain lists of keys that users have privileges on, and at this point that's still in flux.

I'm not confident that the multi-value segment approach will behave as an (SQL) INNER JOIN for subsequent joins. Besides that, having to invert the join this way to be able to use those multi-value segments makes the underlying idea more difficult to grasp. That's not good for future code maintenance, especially since this code concerns privileges.

To get to your initial remark; joins between FOCUS files may precede SQL joins, but certainly the INNER and LEFT_OUTER keywords came afterwards? I was under the impression that those keywords were added to be closer to the SQL syntax that people had gotten used to. If that's the case, then either those keywords are misleading, or their implementation is incorrect (which gets me back to the subject line).


Off-topic:

Since SQL provides new insights in how to handle joins and because FOCUS joins and SQL joins can coexist in (Web)FOCUS code - perhaps it's time to re-engineer how JOINs in FOCUS behave. Having two different join behaviours coexist is confusing and people are more likely to be familiar with SQL joins than with FOCUS joins. Also, it seems to me that SQL joins are more straightforward to their meaning, but that may just be because I'm used to them.

That would also be a good opportunity to expand the syntax to allow to group joins together, so that you can for example have a left join path with some inner joins on the right-hand side. For example:

JOIN
  INNER CAR IN CAR TO CAR IN LIMITCAR
  LEFT_OUTER COUNTRY IN CAR TO COUNTRY IN LIMITCOUNTRY
  INNER COUNTRY IN LIMITCOUNTRY TO COUNTRY IN CONTINENT
END

(Where every COUNTRY record is required to exist in a CONTINENT, but CARs are not necessarily required to be from a COUNTRY - which is an entirely fictive example, of course.)