pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
how can we use outer join in Postures
2+ messages / 2 participants
[nested] [flat]

* how can we use outer join in Postures
@ 2006-01-11 11:53  Praveen Kumar (TUV) <praveen.k@renaissance-it.com>
  0 siblings, 1 reply; 2+ messages in thread

From: Praveen Kumar (TUV) @ 2006-01-11 11:53 UTC (permalink / raw)
  To: brad@kieser.net; +Cc: pgsql-admin

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.

 

 

 

 

^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: how can we use outer join in Postures
@ 2006-01-12 05:32  Michael Fuhr <mike@fuhr.org>
  parent: Praveen Kumar (TUV) <praveen.k@renaissance-it.com>
  0 siblings, 0 replies; 2+ messages in thread

From: Michael Fuhr @ 2006-01-12 05:32 UTC (permalink / raw)
  To: Praveen Kumar (TUV) <praveen.k@renaissance-it.com>; +Cc: brad@kieser.net; pgsql-admin

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



^ permalink  raw  reply  [nested|flat] 2+ messages in thread


end of thread, other threads:[~2006-01-12 05:32 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-01-11 11:53 how can we use outer join in Postures Praveen Kumar (TUV) <praveen.k@renaissance-it.com>
2006-01-12 05:32 ` Michael Fuhr <mike@fuhr.org>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox