-
data manipulation
Hi,
Could someone please help me with the code for the following task?
I have a table called "real_customer"
Lets say someone from another department sends me a list of company names and he/she would like to find out what companies are our current customers. I import the list into "temp_company" table and compare it with the "real_customer" table.
-------------------------------------
SELECT t.customer
FROM temp_company t, r.real_customer
where t.customer = r.customer
-------------------------------------
The problem is that a lot of times the "temp_company" company name is missing INC., CORP., LLP, which results in unfound match.
Solution:
---------------------------------------------------------------------
I would love to have a stored procedure that would do the following FOR EACH ROW:
1. if the company names are equal in both tables, then insert into "temp_company" values ('yes')
2. if "temp_company" company name is not equal the name in "real_customer"
THEN:
- APPEND 'INC.' to the end of the company name in the "temp_company" table. Check if both values are equal, if yes, then INSERT INTO "temp_company" values ('yes')
if no THEN
- APPEND 'CORP.' - you get the idea.
--------------------------------------------------------------------
I hope I explained it clearly
Thank you
Regards
Roman
-
Use LIKE operator instead of equal(=).
-- Dilip
-
Yes, I made a mistake. I use LIKE already, but it does not solve the problem.
Roman
-
are you doing it like
where r.customer like t.customer||'%'
Tomaž
"A common mistake that people make when trying to design something completely
foolproof is to underestimate the ingenuity of complete fools" - Douglas Adams
-
Suppose you maintained a table containing a list of sample company name suffix strings, such as "", " INC", "INC.", "INC"," INC.", "CORP" etc., called COMPANY_SUFFIXES.
you could join this to your real_customer table, and attempt joins to the temp_company table, like ...
Code:
Select
cc.customer
From
(
Select -- cartesian product intentional
rc.customer||cs.sffx company_name
From
real_customer rc,
company_suffixes cs
) cc,
temp_company tc
Where
tc.company_name = cc.comopany_name
-
Yes,
Just to give you an example:
"Temp_company" tables has a record company called: EPA
When I execute the "where r.customer like t.customer||'%'" query, I aslo get match where "real_customer" name are EPANA NETWORKS and EPARTNERS INC., which is not accurate.
That is why I was asking if there is a way to do the following solution:
Solution:
---------------------------------------------------------------------
I would love to have a stored procedure that would do the following FOR EACH ROW:
1. if the company names are equal in both tables, then insert into "temp_company" values ('yes')
2. if "temp_company" company name is not equal the name in "real_customer"
THEN:
- APPEND 'INC.' to the end of the company name in the "temp_company" table. Check if both values are equal, if yes, then INSERT INTO "temp_company" values ('yes')
if no THEN
- APPEND 'CORP.' - you get the idea.
----------------------------------------------------------------------
Thanks for the ideas.
Regards
Roman
-
You might also like to take out all the punctuation, whitespace, and case issues by comparing on ...
Upper(Translate(t1.customer,'a~ .,-_":;','a')) = Upper(Translate(t2.customer,'a~ .,-_":;','a'))
-
Thanks for all the help. It works great.
Roman
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|