nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Oracle Communities

Oracle AI and Analytics

Child Item

Hi, I need a query to split the salaried hours as 8 hrs/day from the state of earnings.

SARAORC

Find more posts tagged with

Fusion HCM Analytics

Accepted answers

All comments

Ashish-Oracle

@SARAORC This forum is dedicated to Fusion Data Intelligence. Please confirm the application you are using here? Also provide more details (if possible) screenshots showing the issue.

NOTE: For Fusion related queries we have a separate sister community.

https://community.oracle.com/customerconnect/

MandeepGupta

https://community.oracle.com/products/oracleanalytics/discussion/24136/hi-i-need-a-query-to-split-the-salaried-hours-as-8-hrs-day-from-the-state-of-earnings

Can you please elaborate your query a bit more? What are you trying to build is it an OTBI or a BIP or in FDI?

Thanks.

SARAORC

@MandeepGupta Thanks for the response.

It was a BIP report, I am trying pull the data of entry hours for the salaried employee hours from the state of earnings page.

So, I need to split the hours eg: if the value is 80 I need to split into 8 hrs/day as 10 separate rows.

MandeepGupta

https://community.oracle.com/products/oracleanalytics/discussion/comment/62114#Comment_62114

You can write something like below:

select papf.person_number ,'80' num_of_hours, to_number('80')/to_number('10') per_day_hours
from per_all_people_f papf,
(select level lvl
from dual
connect by level <= to_number('80')/to_number('10')
)
where trunc(sysdate) between papf.effective_start_date and papf.effective_end_date
and lvl <= to_number('80')/to_number('10')
and papf.person_number = '269628'

Thanks.

SARAORC

Hi @MandeepGupta

Thanks for the response

Not all the employees have 80 hours some of them have 72 and 64 hours.

Based on the hours I need to split the values.

For Example:

if the value is 80 hours, I need to populate 8 hrs/day as 10 separate rows.
If the value is 72 hours it should populate 8 hrs/day as 9 separate rows

MandeepGupta

https://community.oracle.com/products/oracleanalytics/discussion/comment/62120#Comment_62120

That was just an example. Instead of below:

'80' num_of_hours, to_number('80')/to_number('10') per_day_hours

Use your actual table.column names.

If you are not able to replicate, share the table name and column name, I can try to write it for you.

Thanks.

SARAORC

I try to replicate the table and column with this query, but it is not working.

I am sharing the table and column below.

Table - Pay_Run_Balances

Column - Balance_Value

Could you please write it for me using this table.

Thanks.

MandeepGupta

https://community.oracle.com/products/oracleanalytics/discussion/comment/62124#Comment_62124

In your main select clause:

add below:

Pay_Run_Balances.Balance_Value num_of_hours

,(Pay_Run_Balances.Balance_Value/10) per_day_hours

FROM …..

add a new line in from clause:

(select level lvl

from dual

connect by level <= (Pay_Run_Balances.Balance_Value/10)

)

in where clause:

and lvl <= (Pay_Run_Balances.Balance_Value/10)

Thanks.

SARAORC

Thanks for sharing.

Upcoming Event

AI Data Platform - Creating High Code Agents and Using Developer Tools in AI Data Platform
Explore how to build agents using code in AI Data Platform. We'll create an agent that examines and analyzes the contents of a Github repository and reports its findings. The session demonstrates how to use different developer tools to create agents including the AIDP extension for VS Code and Codex. Using these tools we…

Top Leaders

GTA 50