PostgreSQL Administration - how can we use outer join in Postures

Author

how can we use outer join in Postures

Praveen Kumar

2006-01-12, 3:24 am

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_locatio
n_mast_intl. first_name,tuv_locat
ion_mast.first_name)
|| ' ' || COALESCE(tuv_locatio
n_mast_intl. second_name,tuv_loca
tion_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_ma
st_intl. description,tuv_zart
_mast.description) as description,
tuv_tuvdotcom_mast.tuvdotcom_id as tuvdotcom_id,tuv_tuv
dotcom_mast.status_id as status_id,
'' page_valid_from, sysdate page_creation_date,
tuv_tuvdotcom_mast.tuvdotcom||' '||COALESCE(tuv_tuvd
otcom_intl. sublease_company_nam
e,tuv_tuvdotcom_mast
. sublease_company_nam
e)||' '
||COALESCE(tuv_tuvdo
tcom_intl. marketing_info,tuv_t
uvdotcom_mast.marketing_info)||' '||tuv_certificate_m
ast.cert_number||' '
||tuv_certificate_ma
st.scope_english||' '||tuv_certificate_m
ast.scope_german||' '||tuv_certificate_m
ast.scope_local||' '
||tuv_zart_mast.zart_name||' '||COALESCE(tuv_zart
_mast_intl. description,tuv_zart
_mast.description)||' '
||COALESCE(tuv_custo
mer_mast_intl. url,tuv_customer_mas
t.url)||' '||COALESCE(tuv_cust
omer_mast_intl. email,tuv_customer_m
ast.email)||' '
||tuv_customer_mast.name_local||' '||tuv_customer_mast
.address_local||' '||tuv_customer_mast
.building_local||' '
||tuv_customer_mast.city_local||' '||COALESCE(tuv_cust
omer_mast_intl. title,tuv_customer_m
ast.title)||' '
||COALESCE(tuv_custo
mer_mast_intl. first_name,tuv_custo
mer_mast.first_name)||' '
||COALESCE(tuv_custo
mer_mast_intl. second_name,tuv_cust
omer_mast.second_name)||' '
||COALESCE(tuv_custo
mer_mast_intl. third_name,tuv_custo
mer_mast.third_name)||' '
||COALESCE(tuv_custo
mer_mast_intl. fourth_name,tuv_cust
omer_mast.fourth_name)||' '
||tuv_location_mast.post_code||' '||tuv_location_mast
.phone||' '||tuv_location_mast
.fax||' '
||COALESCE(tuv_locat
ion_mast_intl. title,tuv_location_m
ast.title)||' '
||COALESCE(tuv_locat
ion_mast_intl. first_name,tuv_locat
ion_mast.first_name)||' '
||COALESCE(tuv_locat
ion_mast_intl. second_name,tuv_loca
tion_mast.second_name)||' '
||COALESCE(tuv_locat
ion_mast_intl. third_name,tuv_locat
ion_mast.third_name)||' '
||COALESCE(tuv_locat
ion_mast_intl. fourth_name,tuv_loca
tion_mast.fourth_name)||' '
||COALESCE(tuv_locat
ion_mast_intl. street_1,tuv_locatio
n_mast.street_1)||''
||COALESCE(tuv_locat
ion_mast_intl. street_2,tuv_locatio
n_mast.street_2)||''
||COALESCE(tuv_locat
ion_mast_intl. city_1,tuv_location_
mast.city_1)||' '
||COALESCE(tuv_locat
ion_mast_intl. city_2,tuv_location_
mast.city_2)||' '
||COALESCE(tuv_locat
ion_mast_intl. state,tuv_location_m
ast.state)||' '||COALESCE(tuv_loca
tion_mast_intl. country,tuv_location
_mast.country)
as search_data
from
tuv_tuvdotcom_mast,
tuv_tuvdotcom_intl,
tuv_tuvdotcom_type_m
ast,
tuv_tuvdotcom_system
_certs,
tuv_certificate_mast
,
tuv_customer_locatio
n,
tuv_location_mast,
tuv_location_mast_in
tl,
tuv_customer_mast,
tuv_customer_mast_in
tl,
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_m
ast.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_locatio
n.cust_id and
tuv_certificate_mast
.location_id = tuv_location_mast.location_id and
tuv_customer_locatio
n.location_id= tuv_location_mast.location_id and
tuv_location_mast.location_id= tuv_location_mast_in
tl.location_id(+) and
tuv_customer_locatio
n.cust_id = tuv_customer_mast.cust_id and
tuv_customer_mast.cust_id = tuv_customer_mast_in
tl.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_in
tl.lang_id (+)= 2 and
tuv_customer_mast_in
tl.lang_id (+)= 2 and
tuv_zart_mast_intl.lang_id (+)= 2;

Praveen Malik
Software Engineer<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

Extn:1607
====================
====================
====================
===============
Sobha Renaissance Information Technology Private Limited
An SEI-CMM & P-CMM Level 5 Company

World's 1st SSE-CMM Level 5 Company

BS 7799 certified by British Standards Institute

ISO 9001:2000 Certified by TÜV Rheinland/Berlin-Brandenburg

A Six Sigma Practice Company

Tel: + 91 80 51951999; Fax: + 91 80 51523300; Video Conference: + 91 80 51252222
Email : praveen.k@renaissance-it.com; Web: www.renaissance-it.com
====================
====================
====================
===============
The information transmitted is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender and delete all copies from any computer.

Michael Fuhr

2006-01-12, 3:24 am

On Wed, Jan 11, 2006 at 05:23:03PM +0530, Praveen Kumar (TUV) wrote:
> 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.

See "Table Expressions" and SELECT in the documentation:

http://www.postgresql.org/docs/8.1/...xpressions.html
http://www.postgresql.org/docs/8.1/...sql-select.html

Any book or web site that covers standard SQL join syntax should
also be helpful since PostgreSQL's syntax is the same as the
standard's.

--
Michael Fuhr

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Sponsored Links