I am trying to understand the rules BO uses when generating SQL. Below is the SQL generated (Select 1 and Select 2). Essentially, my question is why does BO break up this request into two statements when thier FROM’s, WHERE’s, and GROUP BY’s are identical? BO seems to want to handle each aggregate measure object independently. From my request, I expected one statement which would be the superset of the two SELECT clauses. Am I doing something wrong?
The relationships between the tables are as follows:
SELECT1
SELECT
S_OHEADER.SALESORDERNO,
LEDGER.ACCOUNTNO,
SUM(S_OHEADER.DOLLARTOTAL)
FROM
S_OHEADER,
LEDGER,
G_LSUMMARY
WHERE
( LEDGER.ACCOUNTNO=G_LSUMMARY.ACCOUNTNO ) AND ( S_OHEADER.ENTITY_CODE=G_LSUMMARY.ENTITY_CODE and S_OHEADER.SALESORDERNO=G_LSUMMARY.ACCOUNTNO )
AND S_OHEADER.ENTITY_CODE = ‘TSS’
AND G_LSUMMARY.ENTITY_CODE = ‘TSS’
GROUP BY
S_OHEADER.SALESORDERNO,
LEDGER.ACCOUNTNO
SELECT2
SELECT
S_OHEADER.SALESORDERNO,
LEDGER.ACCOUNTNO,
SUM(G_LSUMMARY.DEBITS_01 + G_LSUMMARY.DEBITS_02 + G_LSUMMARY.DEBITS_03 + G_LSUMMARY.DEBITS_04 + G_LSUMMARY.DEBITS_05 + G_LSUMMARY.DEBITS_06 + G_LSUMMARY.DEBITS_07 + G_LSUMMARY.DEBITS_08 + G_LSUMMARY.DEBITS_09 + G_LSUMMARY.DEBITS_10 + G_LSUMMARY.DEBITS_11 + G_LSUMMARY.DEBITS_12) FROM
S_OHEADER,
LEDGER,
G_LSUMMARY
WHERE
( LEDGER.ACCOUNTNO=G_LSUMMARY.ACCOUNTNO ) AND ( S_OHEADER.ENTITY_CODE=G_LSUMMARY.ENTITY_CODE and S_OHEADER.SALESORDERNO=G_LSUMMARY.ACCOUNTNO )
AND S_OHEADER.ENTITY_CODE = ‘TSS’
AND G_LSUMMARY.ENTITY_CODE = ‘TSS’
GROUP BY
S_OHEADER.SALESORDERNO,
LEDGER.ACCOUNTNO
This is an option in the Designer module. The option is ‘Multiple SQL statements for each measure’. BusinessObjects will then sycronize the results from each query. This is why you see two different queries in the SQL window of the Query Panel.
Robert
Schmidt Interactive Software, Inc.
We now post Document and ReportScript examples!!
I am trying to understand the rules BO uses when generating SQL. Below is the SQL generated (Select 1 and Select 2). Essentially, my question is why does BO break up this request into two statements when thier FROM’s, WHERE’s, and GROUP BY’s are identical? BO seems to want to handle each aggregate measure object independently. From my request, I expected one statement which would be the superset of the two SELECT clauses. Am I doing something wrong?
I am trying to understand the rules BO uses when generating SQL. Below is the SQL generated (Select 1 and Select 2). Essentially, my question …
Actually, you can get wrong results if you use one single query depending on the cardinalities of joins in your database. BusinessObjects takes this into account (other tools do NOT!) by creating multiple queries and syncing or joining the results locally.
Try to run a single query and look at the results (can be done by unchecking the option in Universe parameters/SQL-tab).
Then look at the results, they will be different…
Walter
DI Walter Muellner
Delphi Software GmbH, Vivenotgasse 48, A-1120 Vienna / Austria Tel: +43-1-8151456-12, Fax: +43-1-8151456-21 e-mail: w.muellner@delphi.at, WEB: http://www.delphi.at
I have found that utilizing the option to generate multiple select statements has caused incorrect results. What happened: As I added additional measure objects the results returned partial results. The partial results was related to the additional column I added and not the row count.
Business Objects Tech Support has my case but in the meantime the only way around this was to change the universe parameters to not generate multiple select statements. I would advise doing this. My query now works fine, no matter how many measure objects are selected.
Thanks-
Pam
_________________________________________________________ DO YOU YAHOO!?
Get your free @yahoo.com address at http://mail.yahoo.com
What my knowledge reg this, in the universe you have different fact tables. and they are in different contexts. If you select tables/views from different contexts for a same report then in SQL Query portion it is showing more than 1 quries.
(BOB member since 2002-06-25)