DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: data manipulation

  1. #1
    Join Date
    Jun 2003
    Posts
    132

    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

  2. #2
    Join Date
    May 2001
    Location
    Maryland, USA
    Posts
    409
    Use LIKE operator instead of equal(=).
    -- Dilip

  3. #3
    Join Date
    Jun 2003
    Posts
    132
    Yes, I made a mistake. I use LIKE already, but it does not solve the problem.

    Roman

  4. #4
    Join Date
    Jul 2001
    Location
    Slovenia
    Posts
    422
    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

  5. #5
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    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
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  6. #6
    Join Date
    Jun 2003
    Posts
    132
    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

  7. #7
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    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'))
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  8. #8
    Join Date
    Jun 2003
    Posts
    132
    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
  •  


Click Here to Expand Forum to Full Width