How to get records by joining four tables which has multiple condition?

Scenario:

If the ACNO having record in LINK_MASTER then amt will be shared as per below logic
- From customer table amt column will be shared if acno is in cust_fin table has status "2".
- NPA table amt column will be shared if the acno in cust_fin table status has "0" or "1".
If the acno not having any record in LINK_MASTER then from customer table amt will be shared.
If the flag is 'C' in customer then amt will for this field will be shared as NULL.

Below is DDL and DML for table

create table link_master (
    acno varchar2(200)
);

create table custfin (
    status varchar2(200),
    acno varchar2(200)
);

create table npa (
    amt varchar2(200),
    acno varchar2(200)
);

create table customer (
    flag varchar2(200),
    amt varchar2(200)
    acno varchar2(200)
);

insert into link_master values('100');
insert into link_master values('101');
insert into link_master values('102');

insert into custfin values('1','101');
insert into custfin values('2','102');
insert into custfin values('0','100');


insert into npa values('3545','101');
insert into npa values('4566','102');
insert into npa values('4544','108');
insert into npa values('878','109');

insert into customer values('C','123','100');
insert into customer values('O','124','101');
insert into customer values('O','125','102');
insert into customer values('C','126','103');
insert into customer values('C','127','104');
insert into customer values('O','124','102');

Expected result:

ACNO    STATUS  FLAG    AMT
100      0      C       NULL
101      1      O       4566
102      2      O       4544
103      0      O       126
104             O       127
105             C       NULL

I don't need status and flag column in output. Only needed acno and amt. The query need to be in select statement . Because i need to join this query with other queries

Solution

You can use something like:

SELECT DISTINCT
       c.acno,
       f.status,
       c.flag,
       CASE 
       WHEN c.flag = 'C' THEN NULL
       WHEN l.acno IS NULL OR f.status = 2 THEN c.amt
       WHEN l.acno IS NOT NULL AND f.status IN (0, 1) THEN n.amt
       END AS amt
FROM   customer c
       LEFT OUTER JOIN link_master l
       ON c.acno = l.acno
       LEFT OUTER JOIN custfin f
       ON c.acno = f.acno
       LEFT OUTER JOIN npa n
       ON c.acno = n.acno;

Which, for the sample data, outputs:

ACNO	STATUS	FLAG	AMT
101	1	O	3545
102	2	O	125
102	2	O	124
100	0	C	null
104	null	C	null
103	null	C	null

Or, finding the greatest amt:

SELECT acno,
       status,
       flag,
       amt
FROM   (
  SELECT c.acno,
         f.status,
         c.flag,
         CASE 
         WHEN c.flag = 'C' THEN NULL
         WHEN l.acno IS NULL OR f.status = 2 THEN c.amt
         WHEN l.acno IS NOT NULL AND f.status IN (0, 1) THEN n.amt
         END AS amt,
         ROW_NUMBER() OVER (
           PARTITION BY c.acno
           ORDER BY TO_NUMBER(
               CASE 
               WHEN c.flag = 'C' THEN NULL
               WHEN l.acno IS NULL OR f.status = 2 THEN c.amt
               WHEN l.acno IS NOT NULL AND f.status IN (0, 1) THEN n.amt
               END
             ) DESC
         ) AS rn
  FROM   customer c
         LEFT OUTER JOIN link_master l
         ON c.acno = l.acno
         LEFT OUTER JOIN custfin f
         ON c.acno = f.acno
         LEFT OUTER JOIN npa n
         ON c.acno = n.acno
)
WHERE  rn = 1;

Which, for the sample data, outputs:

ACNO	STATUS	FLAG	AMT
100	0	C	null
101	1	O	3545
102	2	O	125
103	null	C	null
104	null	C	null

Note: You are not going to get your expected output because:

There is no data for acno = 105 in your sample data.
acno = 101 does not connect to an amt = 4566.
acno = 102 has a status of 2 so the amt should be from customer not npa.
acno = 103 has a flag of C not O.

All of these are issues with the input data; fix them and you may be able to get the expected output.

fiddle