-
 Emp_Details_Report
Table1: per_all_people_f
select 
person_id,
person_number,
to_char(trunc(start_date),'yyyy-mm-dd')
from
per_all_people_f --(person_number,start_date)
where
person_number=:P_person_number --Bind Parameter.
--and nvl(:P_person_number,P_person_number)--Optional Parameter
AND sysdate between EFFECTIVE_START_DATE and EFFECTIVE_END_DATE

----------------------
Table2:
Per_person_names_f --person_name
select
FULL_NAME 
from
Per_person_names_f
where
--PERSON_ID=300000047606113
and NAME_TYPE='GLOBAL'
--------------------------------------------------
Table3:per_people_legislative_f
select
sex as gender,
marital_status
from
per_people_legislative_f
where
person_id=<enter_id>
---------------------------------------
Table4:per_persons
select
date_of_birth
from
per_persons
where
person_id=<enter_id>

Now, we will join the tables to get the Report
=================================
Bind Parameter is mandatory:
----------------------------
select 
papf.person_id,
papf.person_number,
papf.start_date,
ppnf.full_name emp_name,
pplf.sex gender,
pplf.marital_status,
pp.date_of_birth
from
per_all_people_f papf,
per_person_names_f ppnf,
per_people_legislative_f pplf,
per_persons pp 
where
papf.person_number=:P_person_number
--1st and 2nd table join condition
AND papf.PERSON_ID=ppnf.PERSON_ID
AND sysdate between papf.EFFECTIVE_START_DATE and papf.EFFECTIVE_END_DATE
AND sysdate between ppnf.EFFECTIVE_START_DATE and ppnf.EFFECTIVE_END_DATE
AND ppnf.name_type='GLOBAL'
--2nd and 3rd table join condition
AND ppnf.person_id=pplf.person_id 
AND sysdate between pplf.EFFECTIVE_START_DATE and pplf.EFFECTIVE_END_DATE
--1st and 4th table join condition(1st table is the central table)
and papf.person_id=pp.person_id

Optional Parameter
-----------------------
select 
papf.person_id,
papf.person_number,
papf.start_date,
ppnf.full_name emp_name,
pplf.sex gender,
pplf.marital_status,
pp.date_of_birth
from
per_all_people_f papf,
per_person_names_f ppnf,
per_people_legislative_f pplf,
per_persons pp 
where
papf.person_number=nvl(:P_person_number,papf.person_number)
--1st and 2nd table join condition
AND papf.PERSON_ID=ppnf.PERSON_ID
AND sysdate between papf.EFFECTIVE_START_DATE and papf.EFFECTIVE_END_DATE
AND sysdate between ppnf.EFFECTIVE_START_DATE and ppnf.EFFECTIVE_END_DATE
AND ppnf.name_type='GLOBAL'
--2nd and 3rd table join condition
AND ppnf.person_id=pplf.person_id 
AND sysdate between pplf.EFFECTIVE_START_DATE and pplf.EFFECTIVE_END_DATE
--1st and 4th table join condition(1st table is the central table)
and papf.person_id=pp.person_id

Comments

Post a Comment

Popular posts from this blog

Oracle SQL

-
SQL Course Content Topic_Details  1. DB,DBMS,RDBMS,ORDMBS & DB development lifecycle Click Here 2. About Oracle Click Here 3. Client/Server Click Here 4. About SQL Click Here 5. Data, data definitions, schema, and SQL Developer tool and DB install process. Click Here 6. Creating a User/Schema/Account in Oracle DB. Click Here 7. Data types in Oracle. Click Here 8. Creating,inserting a Table in Oracle DB. Click Here 9. Operators in Oracle DB. Click Here 10. Displaying data from Oracle DB. Click Here 12. Where Clause Click Here 13. In,between,Like,Is Operators Click Here 14. Alias, All_users,User tables. Click Here 15.Order by, Distinct,fetch clauses ...
Read more

SubQueries

-
Single Row_SubQuery -------------------- Single-Row Sub-Queries Returns one value SELECT <columns> FROM <table_name> WHERE <col_name> <OP> (SELECT STMNT); > , >=, <, <=, =. <> A single-row subquery in Oracle SQL is a subquery (a query nested inside another query) that returns only one row. The main/outer query uses the inner query result as its conditions If single row sub-query returns more than one row then you will get an error ORA-01422: exact fetch returns more than requested number of rows) It can be used in Select, Where, Having clauses Single Row_SubQuery -------------------- --1. Find who is getting highest salary in the employee table? select max(sal) from emp; select *from emp where sal=(select max(sal) from emp); select max(salary) from employees; select *from employees where salary=(select max(salary) from employees); --2.Find second maximum salary emp? select *from emp where sal=( select max(sal) from emp where sal!=(select max(sa...
Read more

With Clause

-
 WITH Clause  ====================================== What? With Clause is a Sub-Query factoring or (Common Table Expressions-CTEs) This features is introduced in Oracle 9i.Using this feature: We assign query result set to a temporary tables. We can access data from temporary tables in main Select clause as many times we require with out repeating the sub-queries. When? Usage of (2,3 level-Nested Queries) can  be minimized or avoided when using With Clause. If we use sub-queries up to 1 level then performance may be good but when we use 2 level or 3 level sub-queries then there may chance of performance issues when we try to fetch large large volume of data. How to implement CTEs ====================== SELECT deptno,        Sum(sal) total_sal FROM   emp GROUP  BY deptno; --fetch sum of sal for each dept. SELECT Avg(total_sal) FROM  (SELECT deptno,               Sum(sal) total_sal     ...
Read more