BO Webi - Generated Query uses Max function

[axel_]

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

[amitrathi239]

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?

[axel_]

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.

[amitrathi239]

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.

[axel_]

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