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

 

✅ Chapter 5. SQL WHERE Clause in Fusion HCM

SQL WHERE Clause – Filtering Fusion HCM Data

Learn how to use the SQL WHERE clause to filter employee, job, and assignment data in Oracle Fusion HCM with real-time examples.

---

🔹 Introduction

The WHERE clause in SQL allows you to filter records based on specific conditions. In Oracle Fusion HCM, it plays a vital role when:

• Fetching only active employees

• Filtering by department, job, or assignment

• Applying security or business logic to reports and extracts

Understanding how to use the WHERE clause effectively helps you write efficient and accurate queries.

---

🔹 Theoretical Concepts

🔍 Syntax of WHERE

SELECT column1, column2
FROM table_name
WHERE condition;

🎯 Common Operators

Operator	Use Case	Example
=	Equal to	person_id = 1001
<> or !=	Not equal to	job_id <> 300
> < >= <=	Comparisons	salary > 5000
LIKE	Pattern matching	full_name LIKE 'J%'
IN	Match a list	job_id IN (101,102)
BETWEEN	Range	salary BETWEEN 3000 AND 5000
IS NULL / IS NOT NULL	Null checks	email_address IS NOT NULL

---

🔹 Fusion HCM-Specific Conditions

🔹 Example Queries in Fusion HCM

✅ Example 1: Filter by Person ID

SELECT person_number,
       full_name
FROM   PER_ALL_PEOPLE_F
WHERE  person_id = 12345; 

✅ Example 2: Active Employees

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

✅ Example 3: Employees with Non-Null Email

SELECT person_number,
       full_name,
       email_address
FROM   PER_ALL_PEOPLE_F
WHERE  email_address IS NOT NULL; 

✅ Example 4: Filter Assignments for Specific Job IDs

SELECT person_id,
       assignment_number,
       job_id
FROM   PER_ALL_ASSIGNMENTS_M
WHERE  job_id IN ( 101, 102, 103 ); 

🔹 Real-Time Scenario (Fusion HCM Use Case)

🧾 Scenario:

“You are asked to create a report showing only active employees who belong to department 300 and have an email address.”

✅ SQL Query:

SELECT person_number,
       full_name,
       department_id,
       email_address
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
       AND paam.department_id = 300
       AND papf.email_address IS NOT NULL; 

---

🔹 Practice Exercise

💡 Task: Write a query to fetch all active primary employee assignments with job ID 200 and assignment status as “ACTIVE”.

SELECT assignment_number,
       job_id,
       assignment_status_type
FROM   PER_ALL_ASSIGNMENTS_M
WHERE  job_id = 200
       AND assignment_status_type = 'ACTIVE'
       AND primary_flag = 'Y'
       AND TRUNC(SYSDATE) BETWEEN effective_start_date AND effective_end_date; 

---

🔹 Best Practices

• Always combine WHERE with effective dates in date-tracked tables.

• Use IN instead of multiple ORs for cleaner code.

• Avoid filtering on calculated fields without indexes for performance.

• Test edge cases like NULL, 0, and inactive statuses.

---

🔹 Summary

✅ The WHERE clause allows precise filtering of data.
✅ Use it to filter on person ID, job, department, status, and more.
✅ In Fusion HCM, always use effective_start_date and effective_end_date filters for active records.
✅ Combine WHERE with joins for powerful reporting logic.
✅ It’s a foundational skill for BI Publisher and HDL validation.

---

🔹 Next Steps

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

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

---

Tags: #SQLWhereClause, #FusionHCM, #EmployeeFilter, #EffectiveDating, #BIReporting