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 Go to next message
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
Re: Query help on 5 tables. [message #681976 is a reply to message #681972] Tue, 22 September 2020 10:39 Go to previous messageGo to next message
Michel Cadot
Messages: 67450
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Welcome to the forum.
Please read the OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Indent the code, use code tags and align the columns in result.

Also always post your Oracle version, with 4 decimals (query v$version), as often solution depends on it.

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Re: Query help on 5 tables. [message #681988 is a reply to message #681976] Tue, 22 September 2020 15:19 Go to previous messageGo to next message
Iffat
Messages: 2
Registered: September 2020
Junior Member
Contact me at [removed]


[Edit MC: email removed]

[Updated on: Wed, 23 September 2020 00:27] by Moderator

Report message to a moderator

Re: Query help on 5 tables. [message #681989 is a reply to message #681988] Tue, 22 September 2020 19:50 Go to previous messageGo to next message
EdStevens
Messages: 1266
Registered: September 2013
Senior Member
Iffat wrote on Tue, 22 September 2020 15:19
Contact me at [removed]

Write to the forum, read from the forum. This is not a private consultancy.

[Updated on: Wed, 23 September 2020 00:27] by Moderator

Report message to a moderator

Re: Query help on 5 tables. [message #681991 is a reply to message #681989] Tue, 22 September 2020 22:33 Go to previous message
Iffat
Messages: 2
Registered: September 2020
Junior Member
Ok sir, noted
Previous Topic: Date interval Blank count
Next Topic: Finding entries missing a value and listing all used values for specific data
Goto Forum:
  


Current Time: Thu Oct 22 15:35:00 CDT 2020