"Smart" where clause for filtering values (Oracle 10g)

Question

Imagine table t1 have 2 cols:

col1      col2
--------------
test 900     1 
test 901     2
test 902     3
user 901     4
test 909     5

I want to find just one row by 2 params (name, code) where name its (test, user ..etc) and code - (901, 902, null, .. etc). There could not be more than one row of the same (Code,name) - I Mean there Are no 2 rows that have "test 901" in Col1.

Code:

declare 
 name varchar2(30);
 code varchar(10);
 col_val varchar2(30);
 col2_val numeric;
begin
 name:= 'test';
 code := '900';

  select col1, col2 into col_val, col2_val 
  from t1 
  where 

   ( REGEXP_LIKE(col1, name||'\s+'||code) -- (3)
   or (
        not   REGEXP_LIKE(col1, name||'\s+'||code) -- (1) 
        and REGEXP_LIKE(col1, name) -- (2)
      )
    )
  order by col1;
  DBMS_OUTPUT.PUT_LINE('val:'||col_val||' id:'||col2_val);
end;

1) for test values name:= 'test' code := '900' the result should be "val:test 900 id:1" - It's OK.

2) BUT for name:= 'test' code := '909' the result should be "val:test 909 id:5", but I got "val:test 900 id:1" ( the first row with Name='Test' ) - It's NOT want I want.

3) and in case name:= 'test' code := '999' the result should be "val:test 900 id:1" (there are NO 999 code, so I need just any row that have Name='test' inside).

The main question is WHY oracle Ignores (1) clause for 2) example? Perhaps I'm doing something wrong - so it would be great if You could show my mistake!

Answer 1

You cannot combine ROWNUM and ORDER BY like this, because the ROWNUM is the row number before it gets sorted.

You need to get more verbose and write something like

 select a.* from (
     select * from table order by col1 ) a
  where rownum = 1;

Check the documentation for ROWNUM and paging.

Answer 2

This condition:

(REGEXP_LIKE(col1, :name || '\s+' || :code)
OR (NOT REGEXP_LIKE(col1, :name || '\s+' || :code) AND REGEXP_LIKE(col1, :name))

will match all rows in your example.

When searching for test 909, you are matching 'test 909' OR (NOT 'test 909' AND 'test'). This will match every row starting from 'test' (either per first condition or per second).

Since you are using rownum = 1, the first column matched in returned, in your case 'test 900'

If you want a fallback match (return 'test 909' if it exists, any 'test' otherwise), use this:

SELECT  *
FROM    (
        SELECT  *
        FROM    t1
        WHERE   REGEXP_LIKE(col1, name || '\s+' || code)
        UNION ALL
        SELECT  *
        FROM    t1
        WHERE   REGEXP_LIKE(col1, name)
        )
WHERE   rownum = 1

, or, if you don't like UNIONs:

SELECT  *
FROM    (
        SELECT  *
        FROM    t1
        WHERE   REGEXP_LIKE(col1, name)
        ORDER BY
                CASE WHEN REGEXP_LIKE(col1, name || '\s+' || code) THEN 0 ELSE 1 END
        )
WHERE   rownum = 1

The latter one, though, is less efficient, since it has to sort.

Answer 3

You would be much better off storing the parts of col1 in separate columns. In any case, here's one more thing besides what Thilo said.

Ignoring the rownum part, four rows of your table match the WHERE clause in your example. Your WHERE clause in English is "name and code both match or else they don't both match, but name matches."

This is a complicated way of saying "name matches," and there's no need for such a complicated WHERE clause if that's really what you mean.

(If \s means space, which I'm not sure, it's "name matches and there is a space after the name.") If you want to see a "both" match ahead of a name-only match, choose the first row in this "scoring" order:

ORDER BY 
  CASE WHEN REGEXP_LIKE(col1, name||'\s+'||code) THEN 2 
       WHEN REGEXP_LIKE(col1, name||'\s+) THEN 1
  ELSE 0 END DESC

Response to comment:

Your WHERE clause fails to express the BUT if not, ... aspect of your requirement. Your WHERE clause simply expresses If there is row with both CODE and NAME OR a row with just NAME in it, show it, and that isn't your requirement.

To express WHERE A, BUT if not A, then B, it won't work to write

WHERE <condition A> OR <condition B>

You have to write

WHERE <condition A> OR (NOT <condition A> AND <condition B>)

or do something like I did with my CASE expression.

Does that help?