Extended Aggregate function (XSUM, XMIN, XMAX, XCOUNT etc)

[biusercog]

Hi All,

I am facing an issue with extended Aggregate function (XSUM, XMIN, XMAX, XCOUNT etc) when I am trying to use functions like sum, count etc to create a Max term date Flag column in FM. I have gone thru some links which says about creating determinants to define the grain but I am using only one view in my case and I still see extended Agg functions (both in FM SQL and Report studio SQL).

When I use these functions, i see "over (partition by" in Native SQL and "XMAX" in IBM Cognos sql with more than one select statement.

Is there anyway Are there any potential issues I should worried about?

Report Studio SQL:

with
EMP_VIEW as
    (select
           case when EMP_VIEW.TERM_DT = XMAX(EMP_VIEW.TERM_DT  for EMP_VIEW.EMP_NUM ) then 'Y'
             else 'N'
           end  as  Max_Term_Dt
     from
           EMP_VIEW EMP_VIEW
    )
select distinct
       EMP_VIEW.Max_Term_Dt  as  Max_Term_Dt
from
       EMP_VIEW

Thanks

[New_Guy]

Hi,
The way you were trying is fine, don't worry about the sql, we do the same for getting the same, max[Data Item](common practice). Test the report run time and if it is not causing any performance issue, you should be ok. It has to run a seperate sql to get the max based on what it is maxed out to(that is the reason you will see the partition by)and then it will be supplied to the query. Let us know if you face any issue.
Good luck
New guy