How can I JOIN the query below?

Question

SELECT 
PB_BANK_CODE,    ---- DB ITEM
PB_BANK_NAME,  ---- DB ITEM
TOTAL_AMOUNT   --- NON DB ITEM
     FROM  GLAS_PDC_BANKS   

where PB_COMP_CODE=:parameter.COMP_CODE AND  pb_bank_code in(select distinct pc_bank_from from glas_pdc_cheques where PC_COMP_CODE=:parameter.COMP_CODE AND pc_due_dateTIME between :block01.date_from and :block01.date_to AND ISNULL(pc_discd,'X') = 'R')
order by pb_bank_code

TOTAL AMOUNT:

    BEGIN
  SELECT SUM(PC_AMOUNT) INTO :BLOCK02.TOTAL_AMOUNT
    FROM GLAS_PDC_CHEQUES
   WHERE PC_DUE_DATE BETWEEN :BLOCK01.DATE_FROM AND :BLOCK01.DATE_TO
     AND   PC_BANK_FROM = :BLOCK02.PB_BANK_CODE
     AND   NVL(PC_DISCD, 'X') = 'R';
EXCEPTION WHEN OTHERS THEN :BLOCK02.TOTAL_AMOUNT := 0;
END;

IS IT NEED TO SE JOINING?GIVE SOLUTION

Answer 1

Combine from list, giving each table an alias, Banks b.

Then combine the select lists prepending the appropriate alias to each field name, b.code.

Do the same for the where and order by.

Lastly add to the where clause the condition that matches records from the tables, c.id = b.cheques_id or whatever it is.

Answer 2

Use:

  SELECT b.pb_bank_code,
         b.pb_bank_name,
         x.total_amount
    FROM GLAS_PDC_BANKS b
    JOIN (SELECT c.pc_bank_from,
                 SUM(c.pc_amount) AS total_amount
            FROM GLAS_PDC_CHEQUES c
           WHERE c.pc_due_dateTIME BETWEEN :block01.date_from AND :block01.date_to 
             AND c.pc_discd = 'R'
        GROUP BY c.pc_bank_from) x ON x.pc_bank_from = b.pb_bank_code
   WHERE b.pb_comp_code = :parameter.COMP_CODE 
ORDER BY b.pb_bank_code