Mastering SQL for Oracle Fusion HCM – Chapter 4 : SELECT Statement in SQL

 

✅ Chapter 4. SELECT Statement in SQL

SQL SELECT Statement – Read Data in Oracle Fusion HCM

Learn how to use the SQL SELECT statement with practical examples from Oracle Fusion HCM. Includes syntax, use cases, and real-time HCM queries.

---

🔹 Introduction

The SELECT statement is the foundation of SQL. It allows you to retrieve data from one or more tables in a database. In Oracle Fusion HCM, you’ll use SELECT frequently to:

• Build BI Publisher reports

• Validate HDL data

• Create SQL-based value sets

• Analyze employee, job, and payroll information

This chapter will help you master the SELECT statement with real examples from Fusion HCM.

🔹 Theoretical Concepts

🔍 Syntax of SELECT

SELECT column1, column2, ...
FROM table_name
[WHERE condition];

📌 Key Clauses

Clause	Purpose	Example
SELECT	Specifies which columns to return	SELECT full_name
FROM	Specifies the table	FROM per_all_people_f
WHERE	Filters data	WHERE person_id = 1001
ORDER BY	Sorts results	ORDER BY full_name ASC
DISTINCT	Removes duplicates	SELECT DISTINCT department_id

---

🔹 Example Queries in Fusion HCM

✅ Example 1: Basic Query from People Table

SELECT person_number,
       full_name,
       date_of_birth
FROM   PER_ALL_PEOPLE_F;

✅ Example 2: Filter Active Records

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

✅ Example 3: Sort Alphabetically

SELECT full_name
FROM   PER_ALL_PEOPLE_F
ORDER  BY full_name ASC; 

✅ Example 4: Distinct Departments

SELECT DISTINCT department_id
FROM   PER_ALL_ASSIGNMENTS_M; 

---

🔹 Real-Time Scenario (Fusion HCM Use Case)

🧾 Scenario:

“You are asked to generate a list of currently active employees and their dates of birth, sorted by name.”

✅ SQL Query:

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

This type of query is used frequently in:

• BI Publisher reports

• HCM Extract filters

• Validation of HDL loads

---

🔹 Practice Exercise

💡 Task:

Write a SQL query to fetch the assignment number, job_id, and department_id for all employees whose assignments are active today.

SELECT assignment_number,
       job_id,
       department_id
FROM   PER_ALL_ASSIGNMENTS_M
WHERE  TRUNC(SYSDATE) BETWEEN effective_start_date AND effective_end_date; 

---

🔹 Best Practices

• Always use effective_start_date and effective_end_date to get current data.

• Avoid using SELECT * in production queries—list only required fields.

• Use ORDER BY to ensure consistent results in reports.

• Use DISTINCT carefully—can affect performance.

---

🔹 Summary

✅ The SELECT statement is used to read data from HCM tables.
✅ Use clauses like WHERE, ORDER BY, and DISTINCT to control result sets.
✅ In Fusion HCM, effective dates are essential when selecting active records.
✅ Mastering SELECT is critical for BI Publisher and HDL-based validations.

---

🔹 Next Steps

• Previous Chapter: Mastering SQL for Oracle Fusion HCM – Chapter 3 : Oracle SQL Developer Setup

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

Tags: #SQLSELECT, #FusionHCM, #EmployeeData, #BIReports, #EffectiveDates, #OracleSQL