select in select inner join

Question

I try to make an inner join on select. My select statement is like below

SELECT con.fpd_no, 
       con.contract_no, 
        cm.cust_name, 
       (select cm.cust_name, TRIM(cm.address) || ', ' ||  TRIM(cm.zipcode) || ', ' || TRIM(klm.kelurahan_desc) || ', ' || TRIM(kcm.kecamatan_desc) || ', ' || TRIM(kbm.kabupaten_desc) || ', ' || TRIM(prm.propinsi_desc)
        from customer_master cm
        inner join kelurahan_master klm on klm.kelurahan_id=cm.kelurahan_id
        inner join kecamatan_master kcm on kcm.kecamatan_id=klm.kecamatan_id
        inner join kabupaten_master kbm on kbm.kabupaten_id=kcm.kabupaten_id
        inner join propinsi_master prm on prm.propinsi_id=kbm.propinsi_id;) as address, 
        con.principal_amount
   FROM contract con
   JOIN customer_master cm ON cm.cust_no = con.cust_no
   WHERE con.deal_code = 'A005'
    AND con.fpd_date BETWEEN '01-01-2016' AND '31-01-2016'
    AND con.fpd_no = '0103006116'
    AND con.objt_group = 'MOTOR';

but I get this error:

ORA-00911: invalid character.

Answer 1

try this:

remove semicolon on subselect and sure about select returns only one row

SELECT con.fpd_no, con.contract_no, cm.cust_name, (select distinct cm.cust_name, TRIM(cm.address) || ', ' ||  TRIM(cm.zipcode) || ', ' || TRIM(klm.kelurahan_desc) || ', ' || TRIM(kcm.kecamatan_desc) || ', ' || TRIM(kbm.kabupaten_desc) || ', ' || TRIM(prm.propinsi_desc)
from customer_master cm
inner join kelurahan_master klm on klm.kelurahan_id=cm.kelurahan_id
inner join kecamatan_master kcm on kcm.kecamatan_id=klm.kecamatan_id
inner join kabupaten_master kbm on kbm.kabupaten_id=kcm.kabupaten_id
inner join propinsi_master prm on prm.propinsi_id=kbm.propinsi_id) as address, con.principal_amount
   FROM contract con
   JOIN customer_master cm ON cm.cust_no = con.cust_no
   WHERE con.deal_code = 'A005'
    AND con.fpd_date BETWEEN '01-01-2016' AND '31-01-2016'
    AND con.fpd_no = '0103006116'
    AND con.objt_group = 'MOTOR';