result on single line

[crumb]

I am trying to get a subquery to output a single column multi-record as a single record delimited by something of my choosing. We are converting an Oracle DB. Here is the code with the 'not working' section shown by *:

Select po_vendors.VENDOR_ID, po_vendors.VENDOR_NAME, po_vendor_sites_all.VENDOR_SITE_ID,
(po_vendor_sites_all.ADDRESS_LINE1 || '~' || po_vendor_sites_all.ADDRESS_LINE2 || '~' ||
po_vendor_sites_all.ADDRESS_LINE3 || '~' || po_vendor_sites_all.ADDRESS_LINE4) as address,
po_vendor_sites_all.CITY, po_vendor_sites_all.STATE,
po_vendor_sites_all.ZIP, po_vendor_sites_all.COUNTRY,
(po_vendor_sites_all.FAX_AREA_CODE || po_vendor_sites_all.FAX) as fax,
* (select unique po_vendor_contacts.VENDOR_SITE_ID
* from po_vendor_contacts
* where po_vendor_contacts.VENDOR_SITE_ID = po_vendor_sites_all.VENDOR_SITE_ID) as contacts

from po_vendors, po_vendor_sites_all where po_vendors.VENDOR_ID = po_vendor_sites_all.VENDOR_ID
order by vendor_id

Thanks

 

[rtmpxr]

If the select marked with * retrieve more than a record then you can't do that.