Hello All,
I have installed Tsearch-Module for full text indexing .But when I search text using gist(idxFTI) index on table I also
found
all data which have same accent. Example
1.If I try search for MANI word it also search for MANY word.
2.If I try search for ANDY word it also search for ANDI word.
Please can you tell me how to avoid this problem ? If I want to search text MANI it should search only for MANI not
MANY.
-----Original Message-----
From: Jim Nasby [mailto:jnasby@pervasive.com]
Sent: Thursday, January 12, 2006 1:01 AM
To: Praveen Kumar (TUV); jgardner@jonathangardner.net;
robert.blixt@transpa.se; sandhyar@amiindia.co.in;
devrim@commandprompt.com
Cc: pgsql-general@postgresql.org
Subject: RE: [SPAM] - how can we use outer join in Postures - Found
word(s) if you received this in error in the Text body
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@renaissance-it.com]
Sent: Wednesday, January 11, 2006 6:39 AM
To: Jim Nasby; jgardner@jonathangardner.net; robert.blixt@transpa.se; sandhyar@amiindia.co.in; devrim@commandprompt.com
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
createin 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@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
