BusinessQuery 6.1b generating wrong SQL

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

:reallymad: :crazy_face:

[edited, used bbc for better readability - Andreas]


ramirezr (BOB member since 2004-02-13)

I would argue that there is something very strange in your universe OR, someone has modified the SQL.

BO has never been able to generate the code you are suggesting. BO has no ability to do create inline tables until 6.5 which hasn’t come out yet.


Steve Krandel :us: (BOB member since 2002-06-25)

The original universe was created in 5.1 and has always given the right SQL script. When I open it in 6.1b, it appears to be fine also.

At first I thought it was only happenning in BusinessQuery but I’ve seen it in BusinessObjects too.

Even when I delete my local universes and create a report from scratch it keeps generating the wrong SQL.

Any ideas?
:confused:


ramirezr (BOB member since 2004-02-13)

Are you the universe designer? If not, then you need to get that person involved – perhaps in the “upgrade”, the universe was changed?


Anita Craig :us: (BOB member since 2002-06-17)

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.


BigV :australia: (BOB member since 2003-12-04)

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?

Thanks in advanced.

Ivan


ivan (BOB member since 2004-10-04)