data manipulation

[rkiss]

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

[patel_dil]

Use LIKE operator instead of equal(=).

[rkiss]

Yes, I made a mistake. I use LIKE already, but it does not solve the problem.

Roman

[TomazZ]

are you doing it like
where r.customer like t.customer||'%'

[slimdave]

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

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

[rkiss]

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

[slimdave]

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'))

[rkiss]

Thanks for all the help. It works great.

Roman