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<?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.

Search Discussions

  • Michael Fuhr at Jan 12, 2006 at 5:33 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/interactive/queries-table-expressions.html
    http://www.postgresql.org/docs/8.1/interactive/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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-admin @
categoriespostgresql
postedJan 11, '06 at 11:50a
activeJan 12, '06 at 5:33a
posts2
users2
websitepostgresql.org
irc#postgresql

People

Translate

site design / logo © 2022 Grokbase