This should really have been sent to the -general mailing list, so I'm adding it. You will need to use OUTER JOIN syntax to accomplish this in PostgreSQL; see http://www.postgresql.org/docs/8.0/interactive/queries-table-expressions.html#QUERIES-FROM Note that most databases (including Oracle) now support JOIN syntax instead of other hacks to support outer joins. -----Original Message----- From: Praveen Kumar (TUV) [mailto:praveen.k@xxxxxxxxxxxxxxxxxx] Sent: Wednesday, January 11, 2006 6:39 AM To: Jim Nasby; jgardner@xxxxxxxxxxxxxxxxxxx; robert.blixt@xxxxxxxxxx; sandhyar@xxxxxxxxxxxxxx; devrim@xxxxxxxxxxxxxxxxx Subject: [SPAM] - how can we use outer join in Postures - Found word(s) if you received this in error in the Text body Hello All, I want to create one table with from many different tables using outer joins.Please can you guide how is possible to create in Postgresql. Let we have syntax for creating table in oracle.If we want to create same table in Postgresql then how will we replace (+) in syntax sothat we can use outer join facility in PostgreSQL. CREATE table comp_prod_cert AS select tuv_tuvdotcom_mast.tuvdotcom as tuvdotcom, COALESCE(tuv_location_mast_intl.first_name,tuv_location_mast.first_name) || ' ' || COALESCE(tuv_location_mast_intl.second_name,tuv_location_mast.second_name) as company_name, tuv_certificate_mast.cert_id as cert_id, tuv_certificate_mast.cert_number as certificate_number, tuv_certificate_mast.cust_id as cust_id, COALESCE(tuv_zart_mast_intl.description,tuv_zart_mast.description) as description, tuv_tuvdotcom_mast.tuvdotcom_id as tuvdotcom_id,tuv_tuvdotcom_mast.status_id as status_id, '' page_valid_from, sysdate page_creation_date, tuv_tuvdotcom_mast.tuvdotcom||' '||COALESCE(tuv_tuvdotcom_intl.sublease_company_name,tuv_tuvdotcom_mast.sublease_company_name)||' ' ||COALESCE(tuv_tuvdotcom_intl.marketing_info,tuv_tuvdotcom_mast.marketing_info)||' '||tuv_certificate_mast.cert_number||' ' ||tuv_certificate_mast.scope_english||' '||tuv_certificate_mast.scope_german||' '||tuv_certificate_mast.scope_local||' ' ||tuv_zart_mast.zart_name||' '||COALESCE(tuv_zart_mast_intl.description,tuv_zart_mast.description)||' ' ||COALESCE(tuv_customer_mast_intl.url,tuv_customer_mast.url)||' '||COALESCE(tuv_customer_mast_intl.email,tuv_customer_mast.email)||' ' ||tuv_customer_mast.name_local||' '||tuv_customer_mast.address_local||' '||tuv_customer_mast.building_local||' ' ||tuv_customer_mast.city_local||' '||COALESCE(tuv_customer_mast_intl.title,tuv_customer_mast.title)||' ' ||COALESCE(tuv_customer_mast_intl.first_name,tuv_customer_mast.first_name)||' ' ||COALESCE(tuv_customer_mast_intl.second_name,tuv_customer_mast.second_name)||' ' ||COALESCE(tuv_customer_mast_intl.third_name,tuv_customer_mast.third_name)||' ' ||COALESCE(tuv_customer_mast_intl.fourth_name,tuv_customer_mast.fourth_name)||' ' ||tuv_location_mast.post_code||' '||tuv_location_mast.phone||' '||tuv_location_mast.fax||' ' ||COALESCE(tuv_location_mast_intl.title,tuv_location_mast.title)||' ' ||COALESCE(tuv_location_mast_intl.first_name,tuv_location_mast.first_name)||' ' ||COALESCE(tuv_location_mast_intl.second_name,tuv_location_mast.second_name)||' ' ||COALESCE(tuv_location_mast_intl.third_name,tuv_location_mast.third_name)||' ' ||COALESCE(tuv_location_mast_intl.fourth_name,tuv_location_mast.fourth_name)||' ' ||COALESCE(tuv_location_mast_intl.street_1,tuv_location_mast.street_1)||' ' ||COALESCE(tuv_location_mast_intl.street_2,tuv_location_mast.street_2)||' ' ||COALESCE(tuv_location_mast_intl.city_1,tuv_location_mast.city_1)||' ' ||COALESCE(tuv_location_mast_intl.city_2,tuv_location_mast.city_2)||' ' ||COALESCE(tuv_location_mast_intl.state,tuv_location_mast.state)||' '||COALESCE(tuv_location_mast_intl.country,tuv_location_mast.country) as search_data from tuv_tuvdotcom_mast, tuv_tuvdotcom_intl, tuv_tuvdotcom_type_mast, tuv_tuvdotcom_system_certs, tuv_certificate_mast, tuv_customer_location, tuv_location_mast, tuv_location_mast_intl, tuv_customer_mast, tuv_customer_mast_intl, tuv_zart_mast, tuv_zart_mast_intl where tuv_tuvdotcom_mast.tuvdotcom_id = tuv_tuvdotcom_intl.tuvdotcom_id(+) and tuv_tuvdotcom_mast.tdc_type_id = tuv_tuvdotcom_type_mast.tdc_type_id and tuv_certificate_mast.cert_type_id = 2 and tuv_certificate_mast.validity in (04,14,24,90) and tuv_tuvdotcom_system_certs.tuvdotcom_id(+) = tuv_tuvdotcom_mast.tuvdotcom_id and tuv_tuvdotcom_system_certs.cert_id = tuv_certificate_mast.cert_id(+) and tuv_certificate_mast.cust_id = tuv_customer_location.cust_id and tuv_certificate_mast.location_id = tuv_location_mast.location_id and tuv_customer_location.location_id= tuv_location_mast.location_id and tuv_location_mast.location_id= tuv_location_mast_intl.location_id(+) and tuv_customer_location.cust_id = tuv_customer_mast.cust_id and tuv_customer_mast.cust_id = tuv_customer_mast_intl.cust_id(+) and tuv_certificate_mast.zart_id = tuv_zart_mast.zart_id and tuv_zart_mast.zart_id = tuv_zart_mast_intl.zart_id(+) and tuv_tuvdotcom_intl.lang_id (+)= 2 and tuv_location_mast_intl.lang_id (+)= 2 and tuv_customer_mast_intl.lang_id (+)= 2 and tuv_zart_mast_intl.lang_id (+)= 2; Praveen Malik Software Engineer Extn:1607 -- Jim C. Nasby, Sr. Engineering Consultant jnasby@xxxxxxxxxxxxx Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
