✅ Chapter 7. SQL Functions and Expressions in Fusion HCM

SQL Functions in Oracle Fusion HCM – Date, String, Numeric
Learn key SQL functions (DATE, STRING, NUMBER, NULL) with real-time Fusion HCM examples. Build efficient queries for reporting, BI, and HDL validation.

🔹 Introduction

SQL functions allow you to manipulate and transform data within your queries. In Oracle Fusion HCM, functions are essential when:

Formatting dates
Trimming strings
Handling null values
Creating custom columns in BI Publisher
Writing logic in value sets or HCM extracts

This chapter covers the most used built-in functions and how to apply them in Fusion HCM real-world queries.

🔹 Categories of SQL Functions

📆 Date Functions

Function	Description	Example
`SYSDATE`	Current date	`SYSDATE`
`TRUNC(date)`	Remove time	`TRUNC(SYSDATE)`
`ADD_MONTHS(date, n)`	Add months	`ADD_MONTHS(hire_date, 3)`
`MONTHS_BETWEEN(d1, d2)`	Difference in months	`MONTHS_BETWEEN(SYSDATE, hire_date)`
`NEXT_DAY(date, 'DAY')`	Next occurrence of a day	`NEXT_DAY(SYSDATE, 'MONDAY')`

🔤 String Functions

Function	Description	Example
`UPPER(str)`	Convert to uppercase	`UPPER(full_name)`
`LOWER(str)`	Convert to lowercase	`LOWER(email)`
`SUBSTR(str, start, length)`	Extract substring	`SUBSTR(full_name, 1, 5)`
`INSTR(str, search)`	Find position	`INSTR(email, '@')`
`TRIM(str)`	Remove spaces	`TRIM(full_name)`
`REPLACE(str, old, new)`	Replace text	`REPLACE(full_name, 'Mr. ', '')`

🔢 Numeric Functions

Function	Description	Example
`ROUND(num, n)`	Round number	`ROUND(salary, 2)`
`FLOOR(num)`	Largest whole number ≤ num	`FLOOR(salary)`
`CEIL(num)`	Smallest whole number ≥ num	`CEIL(salary)`
`MOD(a, b)`	Remainder	`MOD(10, 3)` → 1

🚫 NULL Functions

Function	Description	Example
`NVL(expr1, expr2)`	Replace NULL	`NVL(email, 'Not Provided')`
`COALESCE(expr1, expr2, ...)`	First non-null	`COALESCE(work_email, personal_email)`
`NVL2(expr1, expr2, expr3)`	If not null → expr2, else → expr3	`NVL2(email, 'Available', 'Missing')`

🔹 Fusion HCM Real-Time Examples

✅ Example 1: Format Employee Name


SELECT Upper(full_name) AS formatted_name
FROM   per_all_people_f;

✅ Example 2: Age Calculation

SELECT person_number,
       Trunc(Months_between(sysdate, date_of_birth) / 12) AS age
FROM   per_all_people_f
WHERE  Trunc(sysdate) BETWEEN effective_start_date AND effective_end_date;

✅ Example 3: Joining Name with Title

SELECT 'Mr. '
       || full_name AS titled_name
FROM   per_all_people_f;

✅ Example 4: Handle Null Email Addresses


SELECT person_number,
       Nvl(email_address, 'Not Provided') AS contact_email
FROM   per_all_people_f;

🔹 Real-Time Scenario (BI Publisher Use Case)

🧾 Scenario:

“Create a report to list active employees with their names in uppercase and show their email addresses, replacing NULLs with 'No Email'.”

✅ SQL Query:


SELECT Upper(full_name)               AS employee_name,
       Nvl(email_address, 'No Email') AS contact_email
FROM   per_all_people_f
WHERE  Trunc(sysdate) BETWEEN effective_start_date AND effective_end_date;

🔹 Practice Exercise

💡 Task: Write a query to calculate the number of full years an employee has completed since their hire date.

SELECT person_number,
Trunc(Months_between(sysdate, hire_date) / 12) AS years_completed
FROM per_all_assignments_m

WHERE Trunc(sysdate) BETWEEN effective_start_date AND effective_end_date;

🔹 Best Practices

Always TRUNC(SYSDATE) when comparing with effective_start_date.
Use NVL or COALESCE to prevent nulls from causing display/reporting issues.
Avoid applying functions directly on indexed columns in WHERE clauses (can hurt performance).
Combine functions to build intelligent logic in reports and fast formulas.

🔹 Summary

✅ SQL functions allow you to format, calculate, and clean up data dynamically.
✅ Use date, string, numeric, and null functions frequently in HCM reporting.
✅ Real-time use includes age calculation, null handling, and formatting values.
✅ In BI Publisher and HDL scenarios, functions help deliver meaningful outputs.

🔹 Next Steps

Previous Chapter: Mastering SQL for Oracle Fusion HCM – Chapter 6 : SQL ORDER BY and Sorting Logic in Fusion HCM
Next Chapter: Mastering SQL for Oracle Fusion HCM –Chapter 8. Handling NULLs in SQL for Fusion HCM

Tags: #SQLFunctions, #FusionHCM, #DateLogic, #NullHandling, #BIReports, #OracleSQL

Fusion Katta

Pages

Mastering SQL for Oracle Fusion HCM – Chapter 7. SQL Functions and Expressions in Fusion HCM