Alias Tables for diamond relationships

[Bob Cergol]

While the following is not really a true example of a diamond relationship, the impact is the same, and this sort of scenario is a fairly common occurrence in the database in my system:

Customers relate to Sales Reps (Jccust.Jcreps_Idno --> Jcreps.Idno)

Orders relate to Customers (Jcjobs.Cust_Idno --> Jccust.Idno)

Orders relate to Sales Reps, and can be a different rep than the order's customer relates to, (Jcjobs.Jcreps_Idno --> Jcorep.Idno) Jcorep being an alias of table Jcreps.

I'm playing around with defining in relations in DAI and wondering how I would do this -- in case I find I need to. In VDF you absolutely need to since the relationships tend to be "hard-coded".

I am also thinking it would be really nice for VDF developers to have a feature that would automatically generate all the relations in DAI that are defined in a workspace database or all the tables in a DF filelist.

Bob

[Carsten Sørensen]

Hi Bob,

For "relationship definitions" used for linking (i.e. Dynamic AI relations - drill-to and subform inserts) I think you would set up each relationship individually and pick the ones that you need - however it might be that you would have to drill-several times to get to the information. Some scenarios could carry higher level filter parameters as well - restricting to the information needed - depending on the data-sources used though.

However it might be that you can set up the hierarchy using join-builder and get the complete picture in one data-source. Like a "waterfall" type of definition I guess..

Is there a way where you can give me a sample of some data? E.g.: a few sample records in Excel - then I'll be happy to investigate the best way. You can send me the sample data on cs@cintac.com.

Thanks!

Best regards,
Carsten

[Eddy Kleinjan]

Hi Bob,

You can handle this situation in Dynamic by include the base table (your alias table) twice in the query. When using the Dynamic AI Join Builder, you include the table twice and base the join on the underlying tables.

In case you would like to use a Dynamic AI SQL View, then you need to define alias names for the tables that occur more than once. Like:

Code: Select all

SELECT
    ...
FROM
    Jcjobs
    LEFT OUTER JOIN Jccust ON Jcjobs.Cust_Idno = Jccust.Idno
    LEFT OUTER JOIN Jcreps ON Jccust.Jcreps_Idno = Jcreps.Idno
    LEFT OUTER JOIN Jcreps Jcorep ON Jcjobs.Jcreps_Idno = Jcorep.Idno


Note the last LEFT OUTER JOIN where the Jcreps table is included again, but under the (alias) name Jcorep.

Hope this helps.

Eddy Kleinjan

[Bob Cergol]

Thanks Eddy,

That's pretty simple and basically the same as I'm used to.