In our database we have a Record status code field which contains ‘A’ and ‘D’ as two flags to identify whether a record is an active record or an inactive record. We have made a Universe which will be used by the end users to create the Adhoc reports.
But the user wants to view only the data for the active records.
Is there any option or any other way we can filter out the Inactive records without using the condition objects or the where clause of the Object. Because this will involve the end user to drag that filter sperately.
Also, creating a condition in the where clause of all the objects is a very tedious and a time consuming task. Hence wanted to avoid that.
I have also thought of an option of creating the views on all the tables with the condition as, Record Status Code = ‘A’. But this has been kept as the last option, as I want to do something at the Universe Level only.
I have used the stubby join on the tables, in the Universe, but I am still facing some problem.
For example, there are two tables, MPB_PRDCTN_PRCDR (Production Procedure) and MPB_BOM (Bill Of Materials), which have an outer join between them as mentioned below:
MPB_PRDCTN_PRCDR.MPB_BOM_ID=MPB_BOM.MPB_BOM_ID(+)
I have also placed a Stubby join on both these tables, with the following conditions:
MPB_PRDCTN_PRCDR.RCRD_STS_CD != ‘D’
MPB_BOM.RCRD_STS_CD != ‘D’
When we drag objects from indivdual tables the query works fine and the corresponding stubby join conditions appear in the where clause of the generated SQL. It gets all the records which do not have Record Status codes as ‘D’, including NULL.
But when we drag objects made from both the tables, the where clause of the generated SQL contains the Join condition and both the Stubby Join conditions seperated by the logical AND operator. Due to the AND operator there are some scenarios in which the records will get missed.
For example, the records which contain the Record Status code of Production Procedure as ‘A’ and that of Bill Of Materials as ‘D’ or vice versa, will be missed in the report. These records are also required in the report and they will come only if we have an OR condition between the two Stubby Join Conditions.
Can anyone please let me know the solution to make this logical operator as ‘OR’ or any other workaround to avoid these missing rows.
As mentioned in my first message, this Universe is being made for Adhoc reporting. And creating this kind of object needs the end users to be trained technically to understand the need of this field in our database.
The records with the Record status Code as ‘D’ are deleted from the source system but are maintained in our database for audit purposes. Hence the End users want only the records present in the source system,which means that they want only the records with Status codes as ‘A’.
Hence we do not want to create the condition objects. Can you please guide us some way without the condition objects, specifying condition in the where clause of the object or creating the views.
Actually, if we drag the objects from individual tables, I want only the ‘A’ records, which is being fulfilled by the Stubby Join.
But when we drag objects from two or more tables I do not want those rows in which the record status codes of both or all tables are ‘D’.
For example, the tables, MPB_PRDCTN_PRCDR (Production Procedure) and MPB_BOM (Bill Of Materials) are joined together. So If we drag the objects from both the tables we need to get all the records except the ones for which the record status codes of both the tables is ‘D’.
I tried using the Stubby Joins but when the query generates, it places the following condition in the where clause due to which we receive only the records in which the record status codes of both the tables are ‘A’:
MPB_PRDCTN_PRCDR.MPB_BOM_ID=MPB_BOM.MPB_BOM_ID(+)
AND
MPB_PRDCTN_PRCDR.RCRD_STS_CD != ‘D’
AND
MPB_BOM.RCRD_STS_CD != ‘D’
The logic would work if we have an OR condition between MPB_PRDCTN_PRCDR.RCRD_STS_CD != ‘D’ and MPB_BOM.RCRD_STS_CD != ‘D’.
Can you please guide me, if there is some way to place that OR condition in the Stubby join, or some other work around in the Universe which can resolve this issue without using the Consition Objects , Object where clause and Views.
Also, I tried placing a restriction on the tables, using manage access restrictions, but that also gave the same problem by giving those conditions with an AND clause.
I have tried it, and I think what you suggested would work if we drag objects from both the tables, because the required conditions are there in the join and when we drag objects from both the tables the join will be utilized and we will get the required conditions in the generated query.
But the problem will occur if we drag objects only from any one of the tables. because since we are placing the conditions on the join,dragging objects from individual tables will not get that condition.
Is there any way we can get the required rows using both the scenarios, as in dragging objects from individual tables or from two or moretables.
When creating the stubby join, click on MPB_BOM then click on a table to the right of it that it isn’t joined to. Click on right Outer join box, then change what is to the right of the equal sign to ‘A’. When you click OK, your stubby join will have a circle on it to indicate that is an outer stubby join.
(BOB member since 2004-01-29)
(BOB member since 2005-01-06)
(BOB member since 2007-12-11)
(BOB member since 2005-01-06)
(BOB member since 2003-09-17)