Re: A select challenge or is it?

In article <anamDnCJKx.5pH_at_netcom.com>, S. Kharbanda TM says...
>
>With some help from folks in this newsgroup I have solved most of my
>problems, I think this is the last one:)
>
>What I want to do:
>
>From my table select all rows that match keywords in one of six columns,
>i.e col1,col2.....or col6 contain keyword.
>
>I can do this using instr and it works very well.

I presume you mean: INSTR(col1||col2||col3||col4||col5||col6, keyword)>0 This has problems:
1. It won't be able to use any index on col1..col6, should there be any, nor will it be able to use an index on keyword (if that's a database column; if it isn't then there's no index anyway).
2. It will produce spurious matches if the keyword is a substring of any of the keywords. This can be fixed by appending a tag (eg ',') to each string. 3. It will produce spurious matches if the keyword spans columns. eg:   col1='hello' col2='goodbye'
  INSTR(col1||','||col2||',', 'o,g')=5
Unlikely, but possible.

>
>My problem is that the user can choose to input a number of keywords. Do
>i just need to write select statements for each condition (i.e 2 keywords
>have two instr statements in the where clause etc.)?
>
>Is there a better way to solve this problem? Stored procedures, PL/SQL etc.

You need to either
1. use dynamic code (PRO*C, DMBS_SQL), or 2. fetch every row which has one match, and check to see whether they satisfy

    the other conditions.
3. put the keywords in a table, and fetch all the rows from the first table

     where there does not exist and unmatched keyword. See below. 4. Write a query which using of up to N keywords, which might be NULL. Then

      hope the user doesn't exceed N.

Code for 3. This can run blindingly fast, given the right data, and indexes, despite the NOT EXISTS.

SELECT *
FROM data dt
WHERE NOT EXISTS (
  SELECT kw.value /* Unmatched keywords */   FROM keywords kw
  WHERE kw.value NOT IN
    (dt.col1, dt.col2, dt.col3, dt.col4, dt.col5, dt.col6))

-- 
 _________________________   __________________________________________
/  Tommy Wareing          \ /  I've been looking for an original sin,  \
|  p0070621_at_brookes.ac.uk  X   One with a twist and a bit of a spin    |
\  0865-483389            / \     -- Pandora's Box, Jim Steinman       /
 ~~~~~~~~~~~~~~~~~~~~~~~~~   ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~