Home » SQL & PL/SQL » SQL & PL/SQL » Comparing fields? (Oracle 9i)
icon1.gif  Comparing fields? [message #303039] Wed, 27 February 2008 13:52 Go to next message
gentlelily
Messages: 9
Registered: December 2006
Location: St Louis, MO
Junior Member
I have a problem.

I want to compare plan1 or part of plan1 and plan2 or part of plan2 in tableA to plan3 of part of plan3 in tableB.

For instance:

Table A
Plan1:
Apple Care Plan

B]Table A[/B]
Plan2:
Red
Blue
Green ChoiceI
Green ChoiceII
Purple Network

Table B
Plan3:
Apple Select I
Care Classic I
Tree Vacation
Blue Plan II
Apple Green PlanIII
App Purple
Red Apple Plan
Network Plan
Vacation Package 1

I need to evaluate the contents of plan1 in tableA and see if anything matches the content of plan3 in tableB. The same with plan2 in tableA, evaluate the content and see if any portion of it matches plan3 in tableB.

I can use the space in field1 tableA as a delimiter.

I expect out of the above fields, to retrieve the following result set in a new table:

Plan1 or Plan2 from TableA matched with Plan3:
Apple Care Plan - App Purple
Apple Care Plan - Apple Green PlanIII
Apple Care Plan - Apple Select I
Apple Care Plan - Care Classic I
Apple Care Plan - Network Plan
Apple Care Plan - Red Apple Plan
Blue - Blue Plan II
Green ChoiceI - Apple Green PlanIII
Green ChoiceI - Choice Apple Crates
Green ChoiceII - Apple Green PlanIII
Green ChoiceII - Choice Apple Crates
Purple Network - App Purple
Purple Network - Network Plan
Red - Red Apple Plan

I have done this is cobol a very very long time ago, but have no idea how to tackle this is SQL.

I appreciate any help anyone can give me.

Thanks.

Re: Comparing fields? [message #303093 is a reply to message #303039] Wed, 27 February 2008 23:41 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Are you new to Oracle ?

What did you try so far for acheinving the same ?

Anyway try with LIKE and JOIN

Thumbs Up
Rajuvan.
Re: Comparing fields? [message #303218 is a reply to message #303039] Thu, 28 February 2008 07:35 Go to previous messageGo to next message
gentlelily
Messages: 9
Registered: December 2006
Location: St Louis, MO
Junior Member
I am currently using the LIKE condition and I am joining my tables to each other.

I was just wondering if there is an easier way of accomplishing this without the use of LIKE.

Without putting my real data here, the problem is that by looking at the physical data, one can tell that they may belong together, but I have to deconstruct plan1 and plan2 in tableA and try to match it up with plan3 in tableB to be able to give the possible combinations. It is like looking for a needle in a haystack.

And with thousands of different plan names, I am looking for an alternative where the value does not have to physically altered each time I need to look up information for the next plan in line.

Let me know if you need more information.

Thanks for your help and patience.
Re: Comparing fields? [message #303220 is a reply to message #303218] Thu, 28 February 2008 07:42 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
But what exactly is the criterium? at least one whole word in plan3 should match anything in the other table?
Re: Comparing fields? [message #303227 is a reply to message #303220] Thu, 28 February 2008 07:56 Go to previous messageGo to next message
gentlelily
Messages: 9
Registered: December 2006
Location: St Louis, MO
Junior Member
Yes. At least one whole word should match.

Thanks
Re: Comparing fields? [message #303238 is a reply to message #303227] Thu, 28 February 2008 09:00 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
I think the way to go would be to extract all words from the records from table B as individual records (there are examples how to split records in this forum) and then do a
where a.plan1 like '%'||<generated_field>||'%'

An alternative would be to digg (deeeeep) into OracleText indexes.
Re: Comparing fields? [message #303307 is a reply to message #303238] Thu, 28 February 2008 18:01 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9100
Registered: November 2002
Location: California, USA
Senior Member
I would use Oracle Text. It is ideal for doing such comparisons efficiently and has many options for refining and ordering searches. I have provided a brief demonstration of a few features below. I have demonstrated usage of a stoplist to exclude comparison of whatever common words that you specify and usage of wildcard to search for a portion of a word such as app% to find apple, and some weighted scoring to give a higher priority to matching a full word rather than a partial word. The text index already gives a higher priority to matching multiple words. This enables you to order your results in order of likelihood of matches. This is just the tip of the iceberg. There are many more features available. If you decide to use Oracle Text, we have a separate forum for that here, so please continue there. Also, in the future, please provide create table and insert statements for your sample case. Please read our forum guide, located at the top of the newbies forum, highlighted in yellow, for what we expect.


-- test data:
SCOTT@orcl_11g> SELECT * FROM table_a
  2  /

PLAN_1_OR_2
--------------------
Apple Care Plan
Red
Blue
Green ChoiceI
Green ChoiceII
Purple Network

6 rows selected.

SCOTT@orcl_11g> SELECT * FROM table_b
  2  /

PLAN_3
--------------------
Apple Select I
Care Classic I
Tree Vacation
Blue Plan II
Apple Green PlanIII
App Purple
Red Apple Plan
Network Plan
Vacation Package 1

9 rows selected.


-- text indexes with stoplist:
SCOTT@orcl_11g> EXEC CTX_DDL.CREATE_STOPLIST ('your_stoplist', 'BASIC_STOPLIST')

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> EXEC CTX_DDL.ADD_STOPWORD ('your_stoplist', 'PLAN')

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> CREATE INDEX table_a_idx ON table_a (plan_1_or_2)
  2  INDEXTYPE IS CTXSYS.CONTEXT
  3  PARAMETERS ('STOPLIST your_stoplist')
  4  /

Index created.

SCOTT@orcl_11g> CREATE INDEX table_b_idx ON table_b (plan_3)
  2  INDEXTYPE IS CTXSYS.CONTEXT
  3  PARAMETERS ('STOPLIST your_stoplist')
  4  /

Index created.


-- simple query:
SCOTT@orcl_11g> SELECT a.plan_1_or_2, b.plan_3
  2  FROM   table_a a, table_b b
  3  WHERE  CONTAINS (a.plan_1_or_2, REPLACE (b.plan_3, ' ', ' OR '), 1) > 0
  4  OR     CONTAINS (b.plan_3, REPLACE (a.plan_1_or_2, ' ', ' OR '), 2) > 0
  5  /

PLAN_1_OR_2          PLAN_3
-------------------- --------------------
Apple Care Plan      Apple Select I
Apple Care Plan      Care Classic I
Apple Care Plan      Apple Green PlanIII
Apple Care Plan      Red Apple Plan
Red                  Red Apple Plan
Blue                 Blue Plan II
Green ChoiceI        Apple Green PlanIII
Green ChoiceII       Apple Green PlanIII
Purple Network       App Purple
Purple Network       Network Plan

10 rows selected.


-- query using wildcard and score:
SCOTT@orcl_11g> SELECT a.plan_1_or_2, b.plan_3, SCORE(1) + SCORE(2) + SCORE (3) + SCORE (4) AS the_score
  2  FROM   table_a a, table_b b
  3  WHERE  CONTAINS (a.plan_1_or_2, REPLACE (b.plan_3, ' ', ' OR '), 1) > 0
  4  OR     CONTAINS (a.plan_1_or_2, REPLACE (b.plan_3, ' ', '% OR ') || '%', 2) > 0
  5  OR     CONTAINS (b.plan_3, REPLACE (a.plan_1_or_2, ' ', ' OR '), 3) > 0
  6  OR     CONTAINS (b.plan_3, REPLACE (a.plan_1_or_2, ' ', '% OR ') || '%', 4) > 0
  7  ORDER  BY the_score DESC
  8  /

PLAN_1_OR_2          PLAN_3                THE_SCORE
-------------------- -------------------- ----------
Red                  Red Apple Plan               22
Purple Network       App Purple                   22
Blue                 Blue Plan II                 22
Purple Network       Network Plan                 22
Apple Care Plan      Care Classic I               22
Green ChoiceI        Apple Green PlanIII          20
Apple Care Plan      Apple Green PlanIII          20
Green ChoiceII       Apple Green PlanIII          20
Apple Care Plan      Red Apple Plan               18
Apple Care Plan      Apple Select I               18
Apple Care Plan      App Purple                    5

11 rows selected.

SCOTT@orcl_11g> 

Re: Comparing fields? [message #303519 is a reply to message #303307] Fri, 29 February 2008 12:41 Go to previous message
gentlelily
Messages: 9
Registered: December 2006
Location: St Louis, MO
Junior Member
Thanks everyone.

I will try all of these methods.
Previous Topic: Help with a procedure
Next Topic: Import CSV without SQL*LOADER
Goto Forum:
  


Current Time: Tue Dec 03 05:45:47 CST 2024