I reviewed the Universe Designer 4.0 documentation for those of us not going to the new Information Design Tool and was reminded of the importance of correctly configuring the Universe parameters for your database. this topic focuses on the use of ANSI92-SQL and GROUP BY settings and also applies to the 3.X platform.
While its still from the last century, I love ANSI-92 SQL. It took a while for me to get it back then, but now I hate it when its not setup on a Universe to use it. I am somewhat still amazed we are still mostly using ANSI89-SQL which was defined when Madanna and Prince were still on the top the charts.
In reviewing the settings I was reminded that to take advantage of ANSI 92 there are multiple settings you need to be knowledgeable.
The first if of course ANSI92 which is set to NO by default and needs to get an override of YES.
FILTER_IN_FROM. This feature that allows the outer join filter to be in the FROM clause can be set at the join properties level. However it can also be set to YES in the universe parameters so that it defaults to “All objects in FROM”.
The second is INNERJOIN_IN_WHERE which has to be added if you want to use it. Allows you to force the system to generate SQL syntax with all the inner joins in the WHERE clause whenANSI92 is set to yes. This is sometimes a nice way to show that query is not using any outer joins as all the joins being innter will revert to being in the Where clause.
SELFJOINS_IN_WHERE can also override the ON clause to move back to the WHERE clause even if ANI92 is set to YES.
If you dont have Aggregate values (i.e.e SUM, SVG) in your measure objects that will force a GROUPBY on your queries then you better consider theDISTINCT_VALUES parameter. The key thing to remember is that its only invoked if and when the option “Do not retrieve duplicate rows” isactive in your report. If you want GROUP BY instead of DISTINCT then you have to override the default it in the Universe Paramters.
The last thing I will mention is that there are other settings that are database specific stil managed inthe PRM files. I think that many new designers who never had to manage the PRM file in 6.X and earlier may not even know where that file is and what it does.
But thats for another post.