 Comparing fields? [message #303039] |
Wed, 27 February 2008 13:52  |
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 #303218 is a reply to message #303039] |
Thu, 28 February 2008 07:35  |
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 #303238 is a reply to message #303227] |
Thu, 28 February 2008 09:00 |
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 |
 |
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>
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
