Mastering SQL for Oracle Fusion HCM – Chapter 6 : SQL ORDER BY and Sorting Logic in Fusion HCM

 

✅ Chapter 6. SQL ORDER BY and Sorting Logic in Fusion HCM

SQL ORDER BY – Sorting Data in Oracle Fusion HCM

Learn how to use SQL ORDER BY for sorting Fusion HCM data. Includes ascending, descending, multi-column, and real-time BI Publisher report examples.

---

🔹 Introduction

The ORDER BY clause allows you to sort the results of a query by one or more columns. Sorting is essential in Fusion HCM when:

• Generating employee lists alphabetically

• Sorting payroll results by date or amount

• Ensuring consistent layout in BI Publisher or Extract outputs

This chapter explains how to use ORDER BY effectively in both simple and complex queries.

---

🔹 Theoretical Concepts

📌 Syntax of ORDER BY

SELECT   column1,
         column2
FROM     table_name
ORDER BY column1 [asc|desc];

📊 Key Sorting Options

Keyword	Description	Example
ASC	Ascending (default)	ORDER BY full_name ASC
DESC	Descending	ORDER BY hire_date DESC
Multiple Columns	Sort by priority	ORDER BY department_id, full_name
Position	Sort by column number	ORDER BY 1 (sort by first column in SELECT)

---

🔹 Fusion HCM Real-World Use Cases

✅ Example 1: Sort by Employee Name

SELECT person_number,
       full_name
FROM   PER_ALL_PEOPLE_F
WHERE  TRUNC(SYSDATE) BETWEEN effective_start_date AND effective_end_date
ORDER  BY full_name ASC; 

✅ Example 2: Sort by Department and Employee

SELECT full_name,
       department_id
FROM   PER_ALL_ASSIGNMENTS_M paam
       join PER_ALL_PEOPLE_F papf
         ON paam.person_id = papf.person_id
WHERE  TRUNC(SYSDATE) BETWEEN paam.effective_start_date AND
                              paam.effective_end_date
ORDER  BY department_id,
          full_name; 
✅ Example 3: Latest Record First

SELECT full_name,
       hire_date
FROM   PER_ALL_PEOPLE_F

ORDER  BY hire_date DESC; 

🔹 Real-Time Scenario (Fusion HCM Reporting)

🧾 Scenario:

“HR wants a report of active employees sorted by business unit, then by full name.”

✅ SQL Query:

SELECT papf.person_number,
       papf.full_name,
       paam.business_unit_id
FROM   PER_ALL_PEOPLE_F papf
       join PER_ALL_ASSIGNMENTS_M paam
         ON papf.person_id = paam.person_id
WHERE  TRUNC(SYSDATE) BETWEEN papf.effective_start_date AND
                              papf.effective_end_date
       AND TRUNC(SYSDATE) BETWEEN paam.effective_start_date AND
                                  paam.effective_end_date
ORDER  BY paam.business_unit_id,
          papf.full_name ASC; 

📌 This output will group employees by business unit and sort them alphabetically.

---

🔹 Practice Exercise

💡 Task: Write a query to fetch all primary assignments for active employees, sorted by job ID (descending), then by assignment number (ascending).

SELECT assignment_number,
       job_id,
       person_id
FROM   PER_ALL_ASSIGNMENTS_M
WHERE  primary_flag = 'Y'
       AND TRUNC(SYSDATE) BETWEEN effective_start_date AND effective_end_date
ORDER  BY job_id DESC,

          assignment_number ASC; 

🔹 Best Practices

• Always use ORDER BY in BI Publisher reports to ensure consistent output layout.

• Avoid ordering by non-indexed columns in large data sets unless needed.

• Use NULLS FIRST or NULLS LAST in Oracle for better null sorting:

ORDER BY last_name ASC NULLS LAST

• You can sort on expressions:

ORDER BY UPPER(full_name)

---

🔹 Summary

✅ The ORDER BY clause sorts query results for clear and structured output.
✅ You can sort by one or more columns, use ASC/DESC, and sort expressions.
✅ In Fusion HCM, use ORDER BY to control report layouts, grouping, and business logic outputs.
✅ Sort by business unit, hire date, department, or person name based on context.

---

🔹 Next Steps

• Previous Chapter: Mastering SQL for Oracle Fusion HCM – Chapter 5: WHERE Clause in SQL

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

Tags: #SQLORDERBY, #FusionHCM, #SortedReports, #BIReportLayout, #OracleSQLTips