SQL generation WEBI VS Desktop Intelligence

building semantic layer
1

Ok, I am pulling my hair out on this one and yet have to find a post that helps me. :reallymad: :hb:

I have a universe built connecting to a DB2 database on os/390. I am using BO Enterprise XIr2ps1. I have a Measure created that does a count on a Policy #. The SQL in the Select window of designer is(F_UPOLEND2.POLEND_POLICY_ID). I have the Qualification set to Measure with the Function of NONE.

When I run a query in desktop intelligence, I do not have a problem. The SQL generated looks like this and the query runs fine:

SELECT
  F_UPOLEND2.POLEND_RATNG_STATE,
  F_UPOLEND2.POLEND_PRODUCT_CD,
  count(F_UPOLEND2.POLEND_POLICY_ID)
FROM
  F.UPOLEND  F_UPOLEND2
WHERE
  ( 
  F_UPOLEND2.POLEND_PRODUCT_CD  =  103
  AND  F_UPOLEND2.POLEND_RATNG_STATE  =  'MI'
  )
GROUP BY
  F_UPOLEND2.POLEND_RATNG_STATE, 
  F_UPOLEND2.POLEND_PRODUCT_CD

However, when I try to do the same exact thing in WEBI, it does not work because it does not add the Group By clause. The SQL generated looks like this:

SELECT
  F_UPOLEND2.POLEND_RATNG_STATE,
  F_UPOLEND2.POLEND_PRODUCT_CD,
  count(F_UPOLEND2.POLEND_POLICY_ID)
FROM
  F.UPOLEND  F_UPOLEND2
WHERE
  ( 
  F_UPOLEND2.POLEND_PRODUCT_CD  =  103
  AND  F_UPOLEND2.POLEND_RATNG_STATE  =  'MI'
  )

Maybe I am missing something but can someone please help!!! Is there a setting that I need to change somewhere in the universe? I do not understand why the two are different. GROUP BY works ok in WEBI when using the sum function (ie. sum(Variable)) Should it not function the same for count?

[Edited, when posting code samples please use the code option for formatting. It will preserve any indenting or formatting that you may have done. Thank you, Andreas.]

dhofman (BOB member since 2006-03-17)

2

I’m not sure if this still applies to XI, but in ye olde BO6, there were prm files in a dataAccess folder for each rdbms - these were changed to enforce certain rdbms parms / behaviour for joins etc
If the server and desk top prm files were different, you’d get unsimilar sql generated.
Copy them over if it’s still the case or upate the server version.

MikeD :south_africa: (BOB member since 2002-06-18)

3

Hi dhofman, I had exactly the same problem using BO65. It sounded like a bug, it seems to happen using BOXI too.
When you have a count, no Group By is generated by WebI reports. In DeskI it works.
A workaround suggested by techsupport is to change connection type (db engine): replace DB2 for OS390 with DBE Udb v7. I’ve tested, it works.
Hope it will work in XI
Let me know

andreo74 :it: (BOB member since 2004-07-23)

4

YES!!! Replacing DB2 for OS390 with DB2 Udb v7. worked for BOXIR2!

However I had to delete the connection and then re-add it. Simply changing the existing connection (the one that originally had DB2 for OS390) did not work. Once I had the new connection, I needed to export the universe and it worked.

Thanks for you help!

dhofman (BOB member since 2006-03-17)