Wildcarded lookup

Hello!

I have an interesting problem that I am wrestling with. I can make it work but it is not very efficient.

I have a table (table B) with 8 columns making up the key. 6 of those columns can have either a specific value or a wildcard (I'm using a star, '*', if it matters).

Those 8 columns also exist in another table (table A), with no wildcards. I need to find all of the rows in table B that match a single row in table A.

Let me give an example:

TABLE A

     Col_1 Col_2 Col_3 Col_4 Col_5 Col_6 Col_7 Col_8

R1   A     B     C     D     E     F     G     H
R2   A     B     C     Z     Y     X     W     V 

TABLE B

     Col_1 Col_2 Col_3 Col_4 Col_5 Col_6 Col_7 Col_8

R3   A     B     *     *     *     *     *     *
R4   A     C     W     *     *     F     *     *
R5   A     B     C     D     *     *     *     H
R6   A     B     C     D     *     *     *     R
R7   A     B     C     *     *     X     *     *

In this example, R1 from Table A would match R3 and R5. R2 would match R3 and R7. R4 and R6 would not match either row.

I apologize if this is unclear, but it really does match the problem I'm trying to solve :)

Anyway, right now I have a cursor where I say (A.Col_1 = B.Col_1 .... and (A.Col_5 = B.Col_5 OR B.Col_5 = '*') and ....). It gives me the right answers VERY slowly.

I would appreciate any and all suggestions, even if you tell me I've set up the tables all wrong.

thanks!
paul Received on Mon May 05 1997 - 00:00:00 CDT