ANSI 92 = No in BO R4.2

building semantic layer
1

Hi all, I have a terrible question!

BACKGROUND:
I have a client who has moved from a BO R2 server to a BO R4.2, and their reports have changed what records are being returned. After investigation, it seems that the old reports were still relying on ANSI 89 joins, and now with R4.2 enforcing ANSI 92, the reports are handling the filters differently.

For example:

Report 1 (ANSI 89) Result objects: [Matter Name] [Event Name] filters: Where [Event Name] In “X;Y;Z”
SQL generated:

SELECT MATTER.NAME, EVENT.NAME 
                           FROM MATTER, EVENT 
                           WHERE MATTER.MATTER_KEY =* EVENT.MATTER_KEY 
                           AND EVENT.NAME IN ("X:,"Y","Z")

Results: All Matters, and for those matters which have an event, the events too.

Report 1 (ANSI 92) Result objects: [Matter Name] [Event Name] filters: Where [Event Name] In “X;Y;Z”
SQL generated:

SELECT MATTER.NAME, EVENT.NAME 
                            FROM MATTER 
                            LEFT OUTER JOIN EVENT ON MATTER.MATTER_KEY = EVENT.MATTER_KEY 
                            WHERE EVENT.NAME IN ("X:,"Y","Z")

Results: Only those matters which have an event.

Problem: the Query’s filters have become overall filters on the query, rather than just on the table the object comes from. Removing the query filter and adding it as a quick filter along with “or [Event Name] is null” isn’t working either; the blank events are still not appearing (more research here needed).

I have made it clear that ANSI89 is ancient and should not be used anymore, however they need to run reports while they are updating all their old reports to handle a new filtering style.

QUESTION:
There is a universe parameter ANSI_92, which used to control the syntax used. It appears that this parameter is being ignored in BO R4.2. Is there anywhere at the server level I could re-enable this parameter in their prod environment, while they re-write their reports using ANSI92 safe structuring (AKA multiple queries) in Dev?

river-wind (BOB member since 2007-07-24)

2

erik.stenson :us: (BOB member since 2012-07-30)

3

Thanks, I didn’t find that thread. I’ll give this a try. Since these are .unv files migrated from R2, can I edit them with the IDT? I do have the ANSI92=NO flag in the Universe Designer (File…Parameters)

river-wind (BOB member since 2007-07-24)

4

So the IDT doesn’t seem to apply here. The ANSI_92 flag is set to NO, to no effect; the system is overriding it. There is also a FILTER_IN_FROM option which should in theory get the desired results, but setting that to YES seems to also have no effect, the option being overridden by the system.

Unclear why these options seem to not do anything in 4.2, and if it’s possible to make them work in this version or not.

river-wind (BOB member since 2007-07-24)