Business Objects SQL generator. Generally, how does it work? Why did it do this:

Question

I created a report that pulled 1 object from the Purchase_order table (PO_REF) and one from the ORG_ZONES table (ZONE_NAME).

Why would BO generate a query that joined to a 3rd table, ORGANIZATION for no reason?

Note that the ORGANIZATIONS table is related to both the PURCHASE_ORDER and ORG_ZONES table, so in a sense, if you were to look at an table relationship diagram, you would see a circle of links---if that is relevant!

Here is the resulting SQL:

SELECT   
  PRELAY.PURCHASE_ORDER.PO_REF,
  PRELAY.ORG_ZONES.ZONE_NAME
FROM
  PRELAY.PURCHASE_ORDER,
  PRELAY.ORG_ZONES,
  PRELAY.ORGANIZATION
WHERE
  ( PRELAY.ORG_ZONES.ORG_ID=PRELAY.ORGANIZATION.ORG_ID  )
  AND  

( PRELAY.PURCHASE_ORDER.SUPPLIER_ORG_ID=PRELAY.ORGANIZATION.ORG_ID(+)
AND PRELAY.ORGANIZATION.ORG_ID >1  )
  AND  ( PRELAY.ORG_ZONES.ZONE_ID=PRELAY.PURCHASE_ORDER.REMITTO_ZONE_ID  

)

Why did the BO SQL generator generate SQL that includes the ORGANIZATION table if I am not selecting from that table and there is otherwise a direct link between the two tables that I am pulling from?

Is this an example of a real bad SQL generation tool? Is there a way to avoid this-or only at the individual report level?

Answer 1

It's hard to say without reviewing the Business Objects universe directly. You could create a separate organization zones table in the universe and create separate classes and objects that reference the tables. This would avoid the bad SQL generation, but add a little more complexity for end users when trying to decide which zone to use in a report.