Home » SQL & PL/SQL » SQL & PL/SQL » Help me for a SQL query (Oracle HCM Fusion)
Help me for a SQL query [message #685993] Sun, 22 May 2022 09:10 Go to next message
hamihai
Messages: 2
Registered: May 2022
Junior Member
Hi Friends,
I am new to SQL and can write basic SQL queries, but struggle when it comes to calling the fields from multiple tables.
Please can someone help me to provide the query for the below requirement?

I need to fetch the below info from oracle HCM tables.

PERSON NUMBER
Assignment NUMBER
EMP NAME
Date of birth
MARITAL STATUS
HIRE DATE
EMP EMAIL ID
Business Unite NAME
Legal Employer NAME
BASIC SALARY

thank you.
Re: Help me for a SQL query [message #685994 is a reply to message #685993] Sun, 22 May 2022 12:47 Go to previous messageGo to next message
Michel Cadot
Messages: 68217
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You have to determine:
1/ In which tables are these columns
2/ What are the referential constraints (join columns) between them
3/ Use these constraints columns to build the join conditions between the tables

For instance, using the common SCOTT schema, you want, for each employee, his name, his department name and the location of this later.
The appropriate tables are EMP for employee's name (ENAME), and DEPT for department name and location (DNAME and LOC):
SQL> desc emp
 Name                             Null?    Type
 -------------------------------- -------- ----------------------
 EMPNO                            NOT NULL NUMBER(4)
 ENAME                                     VARCHAR2(10 CHAR)
 JOB                                       VARCHAR2(9 CHAR)
 MGR                                       NUMBER(4)
 HIREDATE                                  DATE
 SAL                                       NUMBER(7,2)
 COMM                                      NUMBER(7,2)
 DEPTNO                                    NUMBER(2)

SQL> desc dept
 Name                             Null?    Type
 -------------------------------- -------- ----------------------
 DEPTNO                           NOT NULL NUMBER(2)
 DNAME                                     VARCHAR2(14 CHAR)
 LOC                                       VARCHAR2(13 CHAR)
The primary keys are:
  • for EMP, EMPNO
  • for DEPT, DEPTNO
EMP.DEPTNO is a foreign key to DEPT.DEPTNO, so the join condition between the 2 tables is EMP.DEPTNO = DEPT.DEPTNO and the query is:
SELECT emp.ename, dept.dname, dept.loc
FROM emp JOIN dept ON dept.deptno = emp.deptno
/
You can add an ORDER BY clause if you want a sorted result, for instance by department and employee names (changing the columns order to match the sort I want, this is not mandatory):
SQL> SELECT dept.dname, emp.ename, dept.loc
  2  FROM emp JOIN dept ON dept.deptno = emp.deptno
  3  ORDER BY dept.dname, emp.ename
  4  /
DNAME          ENAME      LOC
-------------- ---------- -------------
ACCOUNTING     CLARK      NEW YORK
ACCOUNTING     KING       NEW YORK
ACCOUNTING     MILLER     NEW YORK
RESEARCH       ADAMS      DALLAS
RESEARCH       FORD       DALLAS
RESEARCH       JONES      DALLAS
RESEARCH       SCOTT      DALLAS
RESEARCH       SMITH      DALLAS
SALES          ALLEN      CHICAGO
SALES          BLAKE      CHICAGO
SALES          JAMES      CHICAGO
SALES          MARTIN     CHICAGO
SALES          TURNER     CHICAGO
SALES          WARD       CHICAGO
Re: Help me for a SQL query [message #685995 is a reply to message #685993] Sun, 22 May 2022 13:12 Go to previous messageGo to next message
John Watson
Messages: 8718
Registered: January 2010
Location: Global Village
Senior Member
You need to understand your tables and their relationships. HZ_PERSON_PROFILES might be the table from which to start, but you'll have to join to several others.

(By the way, I wish you would not say "field" when you mean "column").
icon14.gif  Re: Help me for a SQL query [message #685996 is a reply to message #685995] Sun, 22 May 2022 13:20 Go to previous message
hamihai
Messages: 2
Registered: May 2022
Junior Member
Yes John, I would rather use to say column in place of field.
thank you.
Previous Topic: Oracle flinding max plan id
Next Topic: Can we use WHEN OTHERS exception as first
Goto Forum:
  


Current Time: Thu Jun 30 13:42:21 CDT 2022