Thanks to Arjun at Homeserve USA for his persistence on exploring this option in the CorVu Data Server Manager Knowledge Library. Previously I had only used virtual tables to achieve the same capability.
In the Knowledge library (KL) you can create multiple Enterprise relationship diagrams (ERDs) containing your selected tables, and visualise the joins between them. The obvious temptation is to create additional ERDs for each reporting requirement and change the joins as needed. HOWEVER the catch is that changing the join in one ERD will impact the join in all other ERDs!! If I remove a link between two tables, it will remove it from all other ERDs, or if I edit a link to be an outer join it changes all occurrences of that link in all ERDs. This is a major problem which is sometimes not discovered until query designs have been committed.
However, we found that you can create different joins between the same tables IF you do it in different Virtual Databases (shown as green barrels) in the Knowledge library. This means CorVu is storing the linking definitions separately for each defined database. This gives us greater flexibility when creating KL ERD structures for our users.
A Virtual Database is just a customised collection of tables from the primary KL database and is only used by CorVu - it does NOT change anything in the host database. This is intended to help the query designer by presenting a limited selection of tables with approved joins in an easy to use and understandable diagrammatic form. We created two virtual databases in the KL (KL menu Database>Add Database) and added the same tables from the primary database, then defined different joins using the ERD. (The Customers and USA Customers are the same table but with a KL column restriction on the latter. If you want to know how to do this ask me in the comments and I'll do another post.)
Below you can see how in the ERD for US Sales History the SalesrepLookup table is linked to the OrderHeader table, but in the other it is not. This would not be possible if both ERDs are made on the same KL database. (BTW I also used the Hide/Show columns feature in the ERD to conceal certain columns from users e.g. the column Order_Cycle_In_Days is only visible for selection in the ERD for USA Sales)
The following picture shows the design and results panels for two queries in the CorBusiness Development interface. These are named custsales & usasales - there is an explanation below the image.
The custsales query uses tables from the 'Sales History' virtual database, which has an inner join defined between the customer and order header tables. This means any selection will return rows only where both tables have a matching custnum (where a.CustNum = b.Custnum) this is shown in the top left panel, and the results are in the Dynamart below it showing all order records with customer info. The SQL View shows the one SELECT statement created by CorVu which retrieves the data.
The usasales query uses tables from the 'USA Sales' virtual database, which has a left outer join defined between the customer and order header tables - The Link properties pop-up panel from the link shows how the Outer Join has been selected on the USA Customer table. (Note: it is not easy to identify the type of join from the link graphic, but it actually has a slightly fatter end-point!)
This means any selection will return all rows from the customers table even if there is no matching custnum in the OrderHeader table. This is shown in the top right panel, and the results are in the Dynamart below it showing all customer records with possible order info. You can also see the SQL view of the usasales query showing how it prepares 2 sub queries to collect all USA customer records first, then all order records second. The resulting temp tables are then joined to product the result. By the way - this ERD also has a link to the SalesRepLookup table so I can include the Salesperson name in my query design & results.
So there we have two different joins defined on the same tables, and two queries producing different results depending on which ERD is chosen by the query designer.
Let me know in the comments if you would like more details on anything relating to the wonderful yet underutilized CorVu Knowledge Library.
Oh and Happy New Year.
#corvu