Currently upgrading from 5.1.3 to 6.1b. Using ORACLE.
The problem is that the SQL generated by the new version is different (and wrong, too) from the previous one.
For example, the query in 5.1.3 looks like this:
SELECT
TIME.DATE,
PRODUCT.PROD_LINE,
SUM(SCHEDULE.AMOUNT)
FROM
TIME,
CUSTOMER,
PRODUCT,
SCHEDULE
WHERE
PRODUCT.PROD_ID=SCHEDULE.PROD_ID
AND CUSTOMER.CUST_ID=SCHEDULE.CUST_ID
AND SCHEDULE.BUSINESS_DT=TIME.DATE
AND PRODUCT.PROD_LINE != 'UNKNOWN'
AND CUSTOMER.REGION != 'UNKNOWN'
AND TIME.DT_SEQ = 0
AND WHERE SCHEDULE.DEL_IND = 'Y'
)
GROUP BY
TIME.DATE,
PRODUCT.PROD_LINE
…whereas in 6.1b looks like:
SELECT
TIME.DATE,
PRODUCT.PROD_LINE,
SUM(Table__4.AMOUNT)
FROM
TIME,
CUSTOMER,
PRODUCT,
( SELECT PROD_ID, CUST_ID, BUSINESS_DT FROM SCHEDULE WHERE SCHEDULE.DEL_IND = 'Y' ) Table__4
WHERE
PRODUCT.PROD_ID=Table__4.PROD_ID
AND CUSTOMER.CUST_ID=Table__4.CUST_ID
AND Table__4.BUSINESS_DT=TIME.DATE
AND PRODUCT.PROD_LINE != 'UNKNOWN'
AND CUSTOMER.REGION != 'UNKNOWN'
AND TIME.DT_SEQ = 0
AND Table__4.DEL_IND = 'Y'
)
GROUP BY
TIME.DATE,
PRODUCT.PROD_LINE
[edited, used bbc for better readability - Andreas]
Hi
For me also it happend sometimes like this.
Open the original universe(version5 .unv file) in Designer 6.1 save the universe and export it . Open the report, now you should be able to see the correct SQL.
For me When I exported the unverse in Designer6.1 it allworked fine.
All the best.
Hi all, I’m having similar problem with BO 5.1.2 and BO 5.1.7
In BO 5.1.2 the SQL generated is like this:
In BO 5.1.7 the SQL generated is:
I trim the above sqls 'coz they’re a bit long. Basically the difference is in BO 5.1.2, the SQL generated is correct. In BO 5.1.7 however, the ‘sum’ statements are included in the GROUP BY clause which will generate ‘group function is not allowed here’ error.
I tried saving and re-submitting the report in 5.1.7 as suggested by veera but it still generates the wrong SQL. And I can’t use the ‘do not generate SQL before running’ option, this option is greyed out 'coz there are 2 SQL’s generated for this query which are synchronized.
Is this to do with something not set up properly in the universe?