Home » SQL & PL/SQL » SQL & PL/SQL » Query help on 5 tables. (Oracle, 11g)
Query help on 5 tables. [message #681972] |
Tue, 22 September 2020 07:42 |
|
krajasekhar.n@gmail.com
Messages: 1 Registered: September 2020
|
Junior Member |
|
|
Hi All,
Could you please find below table information and help on query. There are 5 tables and need to add filter conditions. I am not expert in oracle queries. Please help me.
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
Result I am expectiong by matching above filter criteria.
Group records
Member/account records
|
|
|
|
|
|
|
Goto Forum:
Current Time: Sun Jun 30 01:16:00 CDT 2024
|