[edit to add 4.8.5 - but looking to upgrade so SQL Joins won't be a viable answer unless they are back w/ the next release ( please! ).
I want to create a compound join:
Table1.ORDERDATE >= TABLE2.LUDATE
Table1.ORDERDATE <= TABLE2.LUDATE **
Since there's no "between" option in the operator drop-down I assumed I could create something like the above.
However... nope: There is already another join between the same tables. You can add a filter to the existing join instead.
I can write SQL to do this, but it seems like a MV is a step further than it ought to be.
** I understand this is effectively a many-to-many join, but this is a case where I *want* that behavior. Can I make it happen w/out a MV?
The error message doesn't look specific to a potential many-to-many, just multiple join conditions against the same table - but kicking around a bit seems to actually be an error when it's the same table *and* column?
Incorta supports a range join with an equal condition. The constraint is for letting us know that the range join may be expensive. If the logic does not have an equal join condition, we can make one by defining a constant value as formula column and join on it. The way you create the range join is correct. There is no BETWEEN operator in defining joins. Just define two criteria, one "greater than" and the other "less than".
There should only be one join defined between two tables. If there are multiple join paths between two tables, we will need to analyze the requirement and create aliases if necessary.
Please know that the range join supported by Incorta is still a strict parent - child join. The two tables joined will have a parent table and a child table and the number of rows generated via this join will be the same as the child table. If we expect the join result produce more records than either of the table since there is no true child or parent, we will need to model this as a M:M join case and a MV can be used.
Here are some examples:
A table has a ORDER DATE and we need to join to a PERIOD table with PERIOD START DATE and PERIOD END DATE. Since an order can only fall into one period, we can use the range join. Even though that the PERIOD table may have periods for different divisions under different PERIOD TYPE, we can use Incorta range join as long we define period type as a join condition in the join.
ORDER.ORDER_DATE >= PERIOD.PERIOD_START_DATE AND ORDER.ORDER_DATE <= PERIOD.PERIOD_END_DATE AND 'OPERATIONAL' = PERIOD.PERIOD_TYPE
We will use PERIOD as the parent table and use ORDER as the child table in such case. A range join does not have to be a Many-to-Many join.
Here is another example,
An ASSET HISTORY table with the EFFECTIVE START DATE and EFFECTIVE END DATE as historical data are tracked in the table. A PERIOD table has PERIOD START DATE and PERIOD END DATE. The analytical requirement is to show the ASSET count by periods. The design is to build a snapshot of the asset for every period. The join criteria looks like this
ASSET.EFFECTIVE_START_DATE <= PERIOD.PERIOD_END_DATE AND ASSET.EFFECTIVE_END_DATE >= PERIOD.PERIOD_END_DATE
Since an asset history record may span across multiple periods, ASSET table is not a child table in such case. Since multiple assets may exist in the same period, this is a Many-to-Many case. Under the current release ( Incorta 4.x), we will use MV to create a bridge table in this case. The bridge table will build the grain of the data at the ASSET for each EFFECTIVE PERRIO level. This will be the child table and join back to PERIOD and to ASSET HISTORY.