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@xxxxxxxxxxxxx] Sent: Thursday, January 12, 2006 1:01 AM To: Praveen Kumar (TUV); jgardner@xxxxxxxxxxxxxxxxxxx; robert.blixt@xxxxxxxxxx; sandhyar@xxxxxxxxxxxxxx; devrim@xxxxxxxxxxxxxxxxx Cc: pgsql-general@xxxxxxxxxxxxxx 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@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
