Hi All,
Could you please help me to build a query. I have 5 tables and need to join and get result. please refer below tables and filter criteria. I am not expert in oracle query build.
Please help on the query.
CREATE TABLE OM_PORTFOLIO_GROUP
(
PTFL_GROUP_ID NUMBER(38) NOT NULL,
BOOKING_CENTER VARCHAR2(20 BYTE) NOT NULL,
NAME VARCHAR2(300 BYTE),
OPEN_DATE DATE,
REFERENCE VARCHAR2(60 BYTE),
CCY_CODE VARCHAR2(20 BYTE),
REPORT_DATE DATE NOT NULL
);
PTFL_GROUP_ID BOOKING_CENTER NAME OPEN_DATE REFERENCE CCY_CODE REPORT_DATE
3002 JS 04/27/2016 00:00:00 AL:000034000 USD 08/31/2020 00:00:00
CREATE TABLE OM_PORTFOLIOS_MEMBERS
(
PTFL_RL_SHIP_ID NUMBER(38) NOT NULL,
PTFL_ID NUMBER(38),
PTFL_GROUP_ID NUMBER(38) NOT NULL,
BOOKING_CENTER VARCHAR2(20 BYTE) NOT NULL,
PTFL_NUMBER VARCHAR2(120 BYTE),
OPEN_DATE DATE,
REFERENCE VARCHAR2(60 BYTE) NOT NULL,
PTFL_GROUP_REFERENCE VARCHAR2(60 BYTE) NOT NULL
);
PTFL_RL_SHIP_ID PTFL_ID PTFL_GROUP_ID BOOKING_CENTER PTFL_NUMBER OPEN_DATE REFERENCE PTFL_GROUP_REFERENCE
100 200 3002 JS 210008080006 09/01/2016 00:00:00 00210008080006 AL:000034000
102 202 3002 JS 210008080006 09/01/2016 00:00:00 00210008080006 AL:000034000
#Query1
SELECT PG.PTFL_GROUP_ID,PG.OPEN_DATE,PG.REFERENCE,REPORT_DATE,PG.BOOKING_CENTER,PM.PTFL_NUMBER
FROM OM_PORTFOLIO_GROUP PG LEFT JOIN OM_PORTFOLIOS_MEMBERS PM
ON PG.PTFL_GROUP_ID = PM.PTFL_GROUP_ID
AND REPORT_DATE=TO_DATE('08/31/2019', 'mm/dd/yyyy') AND PG.BOOKING_CENTER='JS')
CREATE TABLE OR_ACCOUNT
(
ACNODESC VARCHAR2(12 BYTE) NOT NULL,
ACNO VARCHAR2(12 BYTE) NOT NULL,
ACID NUMBER(10) NOT NULL
)
CREATE TABLE OR_ACCOUNT
(
ACNODESC VARCHAR2(12 BYTE) NOT NULL,
ACNO VARCHAR2(12 BYTE) NOT NULL,
ACID NUMBER(10) NOT NULL
Pftermdate date
)
ACNODESC ACNO ACID date
TEST 210008080006 5003 09/01/2016
CREATE TABLE OR_ACGROUP
(
AGGROUPACID NUMBER(10) NOT NULL,
AGMEMACID NUMBER(10) NOT NULL,
AGIDATE DATE NOT NULL
)
AGGROUPACID AGMEMACID AGIDATE
2002 5003 09/01/2016
CREATE TABLE OR_PARGROUP
(
PGP_ID NUMBER(10) NOT NULL,
PGP_DES VARCHAR2(24 BYTE) NOT NULL,
)
PGP_ID PGP_DESC1
2002 AL:000034000
#Query2:
select trim(OR_PARGROUP.PGP_DES) PGP_DES,
trim(OR_ACCOUNT.ACNO) ACNO,
OR_ACGROUP.agidate
from OR_ACGROUP, OR_PARGROUP, OR_ACCOUNT
where OR_ACGROUP.AGGROUPACID = OR_PARGROUP.PGP_ID
and OR_ACGROUP.AGEDATE >= sysdate
and OR_ACGROUP.AGMEMACID = OR_ACCOUNT.acid(+)
and to_date('3999-12-31', 'YYYY-MM-DD') = OR_ACCOUNT.Pftermdate(+)
1st set of tables
OM_PORTFOLIO_GROUP (group)
OM_PORTFOLIOS_MEMBERS (members or accounts)
2nd set of tables
OR_ACGROUP, OR_PARGROUP, OR_ACCOUNT
I need result with below filter conditions
1. Group record not exists in 2nd set
2. Member/account record not exists in 2nd set
3. Group record exists in 2nd set of tables and member not exists in 2nd
Result I am expectiong by matching above filter criteria.
Group records
Member/account records