B.O improperly forming SQL...causing skewed results

building reports desktopintelligence
1

We have noticed a problem when selecting objects to use for a query. The problem we have found is that based on the order in which objects are selected, different results can be returned. This is apparently due to the Group BY clause. We reorder them and notice that the results are now correct.

Is this a BO bug? or something we can control?

jresendez :mexico: (BOB member since 2004-05-03)

2

:blue: What? :blue: … Are you sure they are the exact same objects, just re-arranged? Have you over-ridden the SQL generation by chance? If what you say is true, it’s definitely a bug. Could you give us the two examples of the resulting SQL? Any by the way, what version of BusObj, what database, etc?

Dwayne Hoffpauir :us: (BOB member since 2002-09-19)

3

It looks like when we select objects based on our heirarchy (location/dept/class…etc) the results are correct…however when selected in different orders…the results are skewed

This poses a potential serious problem to us, as not all of our users know (nor do i think they should have to know) our heirarchy to select objects and run reports.

any suggestions?
j


1st query returns correct results

SELECT
  GEO_LOCATION.LOCATION_NAME,
  DWSKU.DEPARTMENT_ID,
  {fn concat({fn concat({fn concat({fn concat({fn substring(DIGITS(DWSKU.DEPARTMENT_CLASS_ID),1,2)},'/')},{fn substring(DIGITS(DWSKU.DEPARTMENT_CLASS_ID),3,2)})},'  ')},DWSKU.DEPARTMENT_CLASS_DESCRIPTION)},
  sum(FF_LOCATION_SKU_SALES_WEEKLY.NET_SALES_RETAIL),
  DWSKU.CURRENT_RETAIL,
  DWSKU.CURRENT_COST
FROM
  GEO_LOCATION,
  DWSKU,
  FF_LOCATION_SKU_SALES_WEEKLY
WHERE
  ( GEO_LOCATION.LOCATION_ID=FF_LOCATION_SKU_SALES_WEEKLY.LOCATION_ID  )
  AND  ( DWSKU.SKU_NUMBER=FF_LOCATION_SKU_SALES_WEEKLY.SKU_NUMBER  )  )
GROUP BY
  GEO_LOCATION.LOCATION_NAME, 
  DWSKU.DEPARTMENT_ID, 
  {fn concat({fn concat({fn concat({fn concat({fn substring(DIGITS(DWSKU.DEPARTMENT_CLASS_ID),1,2)},'/')},{fn substring(DIGITS(DWSKU.DEPARTMENT_CLASS_ID),3,2)})},'  ')},DWSKU.DEPARTMENT_CLASS_DESCRIPTION)}, 
  DWSKU.CURRENT_RETAIL, 
  DWSKU.CURRENT_COST

SELECT
  DWSKU.CURRENT_RETAIL,
  DWSKU.CURRENT_COST,
  GEO_LOCATION.LOCATION_NAME,
  DWSKU.DEPARTMENT_ID,
  {fn concat({fn concat({fn concat({fn concat({fn substring(DIGITS(DWSKU.DEPARTMENT_CLASS_ID),1,2)},'/')},{fn substring(DIGITS(DWSKU.DEPARTMENT_CLASS_ID),3,2)})},'  ')},DWSKU.DEPARTMENT_CLASS_DESCRIPTION)},
  sum(FF_LOCATION_SKU_SALES_WEEKLY.NET_SALES_RETAIL)
FROM
  DWSKU,
  GEO_LOCATION,
  FF_LOCATION_SKU_SALES_WEEKLY
WHERE
  ( GEO_LOCATION.LOCATION_ID=FF_LOCATION_SKU_SALES_WEEKLY.LOCATION_ID  )
  AND  ( DWSKU.SKU_NUMBER=FF_LOCATION_SKU_SALES_WEEKLY.SKU_NUMBER  ) )
GROUP BY
  DWSKU.CURRENT_RETAIL, 
  DWSKU.CURRENT_COST, 
  GEO_LOCATION.LOCATION_NAME, 
  DWSKU.DEPARTMENT_ID, 
  {fn concat({fn concat({fn concat({fn concat({fn substring(DIGITS(DWSKU.DEPARTMENT_CLASS_ID),1,2)},'/')},{fn substring(DIGITS(DWSKU.DEPARTMENT_CLASS_ID),3,2)})},'  ')},DWSKU.DEPARTMENT_CLASS_DESCRIPTION)}

jresendez :mexico: (BOB member since 2004-05-03)

4

While I’m not going to outwardly say that this makes no sense…

What’s the database? This seems to be MS Access. If not, then SQL Server. Try getting rid of the fn concat junk and use native database syntax for string concatenation.

The order of things in SQL doesn’t affect the results. What happens when you run this outside of BO?

This looks like a database issue or perhaps a BO connectivity issue. Have you tried adding a universe parameter? Hardrefresh=1 may help.

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

5

DB is AS400…
I agree that the order of objects selected doesn’t skew the results…however the order of objects in the GROUP BY appears to have an affect on the result set .

jresendez :mexico: (BOB member since 2004-05-03)

6

I know it appears to, but does the same test outside of BO do the same thing?

These 2 statements are the same. They might sort differently, but should produce the exact same results.

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