Search Postgresql Archives

Re: [SPAM] - how can we use outer join in Postures - Found word(s) if you received this in error in the Text body

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux