Max function returns multiple rows

Robin Olsen

Hello

I work in a Windows environment and I am using Oracle HCM Analysis tool to alter a benefits report to only show the latest benefit by date.

I am using the 'Benefits - Enrollment Real Time' subject area. The report is to display the information per employee.

The problem is I use the max function to try and restrict records to only the record with the latest date. However, I am getting multiple rows returned (as if the max function was completely ignored). I need to know how to completely omit the records where the date is not the latest date.

The 'amount' value is also displayed on the report and there are multiple records per benefit (example: a employee has Basic Group Life for 150,000 which was effective on 01/01/2024 - on 01/15/2024 the employee reduced the benefit to 130,000 - the report should only show the entry for the 15th and the amount of 130,000 but it will show both records instead.)

Can anyone tell me how to correct this? I have tried using group by, I have tried not using group by. I would write this directly from the tables and not use this analysis tool but I have no idea of the actual table names this data comes from.

Thanks in advance for any and all tips and tricks shared...

EzequielC-Oracle

Hi Robin,

I don't think you can. You can accept my answer to close it.

Regards,

Ezequiel.

EzequielC-Oracle

Hi Robin,

This is the Oracle Analytics Cloud / Oracle Analytics Server community.

Your question is about Fusion HCM.

Moderators @Emily Cikovsky-Oracle this post may need to be moved to Fusion HCM.

Thank you,

Ezequiel

Robin Olsen

EzequielC-Oracle - thanks for the tip - I moved it over myself...I would delete it from here but cannot see that functionality

Emily Cikovsky-Oracle

Hi Robin! No worries about the thread being here.

I raised this with the Fusion Analytics team as well, seeing as OAC is a key part of that solution and is becoming more present as part of the user experience, so want both product teams to see your question.

Renuka Nuguru-Oracle

Hi @Robin Olsen,

I see you are using OTBI fusion SaaS 'Benefits - Enrollment Real Time' subject area.

Go through the KM doc, which might help to resolve the issue.

OTBI - How To Display MAX Row In The Report When Invoice Lines Require More Than One Approval OR How To Display Max Date Row In the Report (Doc ID 2596815.1)

Sample logical sql, focus on where clause to get the max record. Modify your sql accordingly.

SELECT
"Cash Management - Bank Statement Balances Real Time"."- Header Information"."Statement Header Reconciliation Status Code" s_1,
"Cash Management - Bank Statement Balances Real Time"."- Header Information"."Statement Number" s_2,
"Cash Management - Bank Statement Balances Real Time"."Bank Account"."Bank Name" s_3,
"Cash Management - Bank Statement Balances Real Time"."Bank Account"."Masked Account Number" s_4,
"Cash Management - Bank Statement Balances Real Time"."Time"."Date" s_5
FROM "Cash Management - Bank Statement Balances Real Time"
WHERE
( MAX("Cash Management - Bank Statement Balances Real Time"."Time"."Date" By "Cash Management - Bank Statement Balances Real Time"."Bank Account"."Bank Name") = "Cash Management - Bank Statement Balances Real Time"."Time"."Date")

Hope this helps…

Renuka