The users want to define the joins between tables in answers, when doing adhoc queries. These views are used for real-time reporting and were sitting on top of EBS tables. This requirement was driven by the fact that most of these views could be joined in more than 2 ways depending of the business scenario. In a typical design we would create alias tables to correspond to each of the joins. But in our case the users were not able to define the joins in advance and there is a possibility of the view definitions changing over a period of time. To sum it up the users wanted to simulate a SQL tool with answers, sort of.
In our case View 1 and View 2 could be joined up to 4 different ways and there were quite a few views like that, making the whole aliasing approach cumbersome. One of the developers in the team came up with an idea, though it’s not the perfect solution, but definitely simple and worth mentioning.
First up we extended all the views in the database by adding a dummy column and the values were always 1.
View 1
| Col1 | Col2 | Col3 | Col4 | Dummy |
| 1 | ||||
| 1 |
View2
| Col1 | Col2 | Col3 | Col4 | Dummy |
| 1 | ||||
| 1 |
The physical layer in the RPD would have the join between the views as View1.dummy = View2.Dummy. There is nothing special about the BMM layer, the usual and the same with the presentations layer.
Now comes the answers part. If I were to run a query between the 2 views without any filters in the query the results would not make any since the join is View1.dummy = View2.Dummy.
Let’s say for query 1 I want to join on View1.col1 = View2.col1. Here are the steps.
Put a filter on View1.Col1. (View1.Col1 = value1)
Convert that filter to SQL
Equate View1.Col1 to View2.Col2 in the filter SQL.
This will have your physical queries appended by 2 join conditions 1) which is defined in the RPD(View1.dummy = View2.Dummy) and 2) View1.col1 = View2.col1 .
You could define any joins in a similar fashion. One serious limitation is that the joins are always inner joins.
Posts
