OraFAQ Forum: SQL & PL/SQL » Help me for a SQL query

Home » SQL & PL/SQL » SQL & PL/SQL » Help me for a SQL query (Oracle HCM Fusion)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Help me for a SQL query [message #685993]

Sun, 22 May 2022 09:10

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.

Report message to a moderator

Re: Help me for a SQL query [message #685994 is a reply to message #685993]

Sun, 22 May 2022 12:47

Michel Cadot
Messages: 68421
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

Report message to a moderator

Re: Help me for a SQL query [message #685995 is a reply to message #685993]

Sun, 22 May 2022 13:12

John Watson
Messages: 8805
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").

Report message to a moderator

Re: Help me for a SQL query [message #685996 is a reply to message #685995]

Sun, 22 May 2022 13:20

hamihai
Messages: 2
Registered: May 2022

Junior Member

Yes John, I would rather use to say column in place of field.
thank you.

Report message to a moderator

Previous Topic:	Oracle flinding max plan id
Next Topic:	Can we use WHEN OTHERS exception as first

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Mar 31 01:52:17 CDT 2023