Skip to Content
0

BO Webi - Generated Query uses Max function

Feb 13 at 07:47 AM

79

avatar image

Hi Webi/IDT experts,

I got in an universe a derived table -> simple Aggregation

-------------

SELECT
Table__1."DB-WORKDATE",
Table__1."DB-ID",
SUM (Table__1."DB-HOURS") as "HRS"
FROM
@catalog('Z01')."Local.BC"."DB_QRY" Table__1
GROUP BY
Table__1."DB-WORKDATE",
Table__1."DB-ID"

---------------

All working fine in IDT data preview etc.

When I build now a query in Webi simply selecting fields from the aggregated table I did not get the result expected. Only one row per ID showed up. When researching I turned out that the generated SQL used the max function on WORKDATE and ID (see below).

Can somebody tell me why?

Thanks and best regards,

Axel

PS: Origin DB Table is key on a Counter field. Aggregated Table keys I assign are on Workdate and ID

--------------

SELECT
max( Table__3."DB-WORKDATE" ),
max( Table__3."DB-ID" ),
sum(Table__3."HRS")
FROM
(
SELECT
Table__1."DB-WORKDATE",
Table__1."DB-ID",
SUM (Table__1."DB-HOURS") as "HRS"
FROM
"Z01"."Local.BC"."DB_QRY" Table__1
GROUP BY
Table__1."DB-WORKDATE",
Table__1."DB-ID" ) Table__3
GROUP BY
Table__3."DB-ID

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

1 Answer

AMIT KUMAR
Feb 13 at 11:09 AM
0

have you crossed checked the Sql generated in IDT query and Webi are same or different?

check if Business layer objects are mapped with same table or different table.check if max() function in the used in the business layer objects?

Show 3 Share
10 |10000 characters needed characters left characters exceeded

All looks good in IDT. Mapping etc. Correct tables are used in the Query SQL. It is just the MAX function that is "wrongly" used.

0

is there any custom SQL in webi query? can you also try to include/exclude one object at a time and check the sql to see which is impacting.

0

Hi Amit,

the max function is used as soon as I drag the measure element DB-HOURS into the query.

Thanks and best regards,

Axel

0