OTBI Expression not working

User_RD03L

Hello,

We're creating an OTBI report that fetch employees time card details. Please see below requirements

1. If employee did not work on a holiday, it should return 'PH'
2. If employee worked on a holiday, it should return the number of reported hours.

We already completed the number one requirement. However, we can't still figure out on how to retrieve the number two.

Please see the SQL Expression we used: (on the second WHEN statement, this is where we try to create the number two requirement. However, it's not working.

CASE WHEN "Reported Time Entry Details"."Payroll Time Type Name" = 'Public Holiday IN' THEN 'PH'
{WHEN "Reported Time Entry Details"."Payroll Time Type Name" = 'Public Holiday IN' AND "Reported Time Cards"."Reported Hours"
IS NOT NULL THEN CAST("Reported Time Cards"."Reported Hours" AS CHAR)}
ELSE CAST("Reported Time Cards"."Reported Hours" AS CHAR) END

Any help would be appreciated.

Thank you!

Nathan CCC

maybe (edit the ifnull if you want some value when hours is null)

select all 

case ("Reported Time Entry Details"."Payroll Time Type Name") 

when 'Public Holiday IN' then 'PH' 

else CAST(ifnull("Reported Time Cards"."Reported Hours",null) AS CHARACTER) 

end as s 

from "Workforce Management - Reported Time Cards Real Time"

gclampitt

Also the intial case statement is incorrect

If "Payroll Time Type Name" = 'Public Holiday IN', then this is satisfied by the 1st "When" condition:
"WHEN "Reported Time Entry Details"."Payroll Time Type Name" = 'Public Holiday IN' THEN 'PH'"
ie it will return "PH", so it will the 2nd condition will never be met because "'Public Holiday IN" has already been dealt with

RichardChan

Agreed with logic above the case statement will work top down you need a Reporting Hours is null as part of the first statement

Bhaskar Konar

Hi @User_RD03L,

Can you please try the following to see if there's any betterment?

CASE 

WHEN ("Reported Time Entry Details"."Payroll Time Type Name" = 'Public Holiday IN' AND "Reported Time Cards"."Reported Hours" IS NOT NULL)

THEN CAST("Reported Time Cards"."Reported Hours" AS CHAR)

WHEN "Reported Time Entry Details"."Payroll Time Type Name" = 'Public Holiday IN' THEN 'PH'

ELSE CAST("Reported Time Cards"."Reported Hours" AS CHAR) 

END

Hope this help.

Cheers,

User_RD03L

https://community.oracle.com/products/oracleanalytics/discussion/comment/61831#Comment_61831

Hello everyone, thank you for your inputs.

@Bhaskar after trying this sql, requirement 2 was met (If employee worked on a holiday,
it should return the number of reported hours). However for requirement 1, it's still not working.

As you may see on the screenshot below, for row 1, employee worked on Public Holiday. Hence, his reported hours is fetched.

For row 2, employee did not work on a Public Holiday. The expected value is that it should be tagged as 'PH'

Please let me know if we can still use the CASE expression to satisfy both of the requirements?

Thank you!

gclampitt

Add payroll time time into your output, so its clear what it is - once know what it is returning, can then work out the correct case statement

Bhaskar Konar

Hi @User_RD03L,

Can you please add "Reported Time Entry Details"."Payroll Time Type Name" in the above report and send the screenshot as suggested by @gclampitt , it will help to understand the data points and will be helpful to code accordingly.

Cheers,

User_RD03L

https://community.oracle.com/products/oracleanalytics/discussion/comment/61833#Comment_61833

Hello, as requested I've added the payroll time type. Please see screenshot below

gclampitt

So you no have 4 rows instead of 2, so your orig pivot table was aggregating values.
Can you share the output how you want it to look pls

Bhaskar Konar

Hi @User_RD03L,

Please try the following:

CASE 

WHEN ("Reported Time Entry Details"."Payroll Time Type Name" = 'Public Holiday IN' AND "Reported Time Cards"."Reported Hours" IS NOT NULL)

THEN CAST("Reported Time Cards"."Reported Hours" AS CHAR)

WHEN ("Reported Time Entry Details"."Payroll Time Type Name" = 'Public Holiday IN' AND "Reported Time Cards"."Reported Hours" IS NULL) THEN 'PH'

ELSE CAST("Reported Time Cards"."Reported Hours" AS CHAR) 

END

Cheers,