Solved

Return First Matching Query

Posted on 2013-11-25
9
702 Views
Last Modified: 2013-11-25
I have a set of business rules that are prioritized. These rules specify how a customer is located in the database (by various search criteria). The first matching rule wins. Within a stored procedure, how can I basically do something like this:

--Return this if there are rows
SELECT * FROM table WHERE firstCriteria = "firstValue"

-- Else Return this if there are rows
SELECT * FROM table WHERE secondCriteria = "secondValue"

-- Else Return this if there are rows
SELECT * FROM table WHERE thirdCriteria = "thirdValue"

-- etc.

Open in new window


Due to how are database is structured, I have determined that it will end up being the most efficient to break the queries out into separate ones rather than have a compound WHERE clause.

Now, as I am returning this to .NET code, I actually need to open a cursor in order to have .NET pick up the data. I think this might be where I'm getting thrown (mentally). I believe I can do a SELECT COUNT(1) for each query to check for rows before opening the cursor, but I thought that approach might be cumbersome, messy, slow, or all. I'm trying to write as clean of a query as possible (but willing to make sacrifices if necessary).

I'm afraid my PL/SQL skills are less than padawan level, so I turn to the Oracle gods. I'm not necessarily looking for the complete solution, but a nudge in the right direction would be most appreciated.

Thanks!
0
Comment
Question by:käµfm³d   👽
  • 3
  • 3
  • 2
  • +1
9 Comments
 
LVL 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 167 total points
ID: 39675719
Not sure three queries will be 'better' than a single one but it is your requirements.

See if this is what you are after.


drop table tab1 purge;
create table tab1(col1 char(1), col2 char(1));

insert into tab1 values('a','1');
insert into tab1 values('b','1');
insert into tab1 values('c','1');
commit;

select col1,col2 from
(
select col1, col2, criteria, min(criteria) over() min_criteria from
(
select 1 criteria, t1.* from tab1 t1 where col1='z'
union
select 2 criteria, t1.* from tab1 t1 where col1='y'
union
select 3 criteria, t1.* from tab1 t1 where col1='c'
)
)
where criteria=min_criteria
/

Open in new window

0
 
LVL 73

Accepted Solution

by:
sdstuber earned 333 total points
ID: 39675724
Combine all 3 queries into one (that way you only have to read the table once), assign numbers to identify the criteria you want.  Rank the results.  Take whatever ends up in Rank 1


SELECT *
  FROM (SELECT RANK() OVER (ORDER BY n) rn, x.*
          FROM (SELECT
                           CASE
                               WHEN firstcriteria = 'firstValue' THEN 1
                               WHEN secondcriteria = 'secondValue' THEN 2
                               WHEN thirdcriteria = 'thirdValue' THEN 3
                           END                      
                           n,
                       t.*
                  FROM yourtable t
                 WHERE firstcriteria = 'firstValue'
                    OR secondcriteria = 'secondValue'
                    OR thirdcriteria = 'thirdValue') x)
 WHERE rn = 1
0
 
LVL 8

Expert Comment

by:Mohit Vijay
ID: 39675727
Use dataReader. When you do DataReader.Read() it will give you next row, if row is not there result will be false.

It will be kind of like this

DataReader dr = Sql.ExccuteQuery()

While (dr.read())
{
var field1 = dr["field]
}
0
 
LVL 74

Author Comment

by:käµfm³d 👽
ID: 39675743
@slightwv & sdstuber

Won't your approach execute every query? I'm only trying to execute as a previous query fails to match (probably wasn't clear from the OP).

The indexes we have on the tables aren't playing nice with these requirements. I'm attempting to exhaust all manner of query manipulation before I go back to the DBAs to request a new index be added.

@VjSoft
I know how to read the data in .NET. That is not my question. Thank you for your input, though.
0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39675748
I'll let sdstuber continue.  His approach is much cleaner than mine.
0
 
LVL 73

Assisted Solution

by:sdstuber
sdstuber earned 333 total points
ID: 39675782
>>> Won't your approach execute every query?


No, because there is only one query to execute, but that query has 3 conditions.

The downside to that is the indexing, unless you have an index that covers all 3 conditions will likely be suboptimal.
That is, you "might" get index usage for one condition but not the others.
0
 
LVL 74

Author Closing Comment

by:käµfm³d 👽
ID: 39675812
OK, I understand what you are saying. I realize now that I oversimplified the question. But I'll see if I can make the case statement work within my environment. If not, I'll start a new question with a more accurate description.

You've answered the question that I asked, so...

Thanks!
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 39675904
I think I understood your requirement, but it's unfortunately not one that's easy to satisfy with good performance for all cases.

The single query with multiple conditions is "best" in terms of number of times you have to query the table.

However, it might not be good for performance if you have indexes that satisfy each of the 3 conditions and might possibly be able to very quickly read a row and validate.  Especially if the first condition would usually be hit.

If you did want to force a check (and you could do it efficiently) then I might try something like this...

Simply execute each query with a rownum=1 clause because you only need to check for existence, not actual values.  Then open your cursor


DECLARE
    v_check INTEGER;
    v_cur   SYS_REFCURSOR;
BEGIN
    SELECT COUNT(*)
      INTO v_check
      FROM yourtable
     WHERE firstcriteria = 'firstValue' AND ROWNUM = 1;

    --Return this if there are rows
    IF v_check = 1
    THEN
        OPEN v_cur FOR
            SELECT *
              FROM yourtable
             WHERE firstcriteria = 'firstValue';
    ELSE
        SELECT COUNT(*)
          INTO v_check
          FROM yourtable
         WHERE secondcriteria = 'secondValue' AND ROWNUM = 1;

        --Return this if there are rows
        IF v_check = 1
        THEN
            -- Else Return this if there are rows
            OPEN v_cur FOR
                SELECT *
                  FROM yourtable
                 WHERE secondcriteria = 'secondValue';
        ELSE
            SELECT COUNT(*)
              INTO v_check
              FROM yourtable
             WHERE thirdcriteria = 'thirdValue' AND ROWNUM = 1;

            --Return this if there are rows
            IF v_check = 1
            THEN
                -- Else Return this if there are rows
                OPEN v_cur FOR
                    SELECT *
                      FROM yourtable
                     WHERE thirdcriteria = 'thirdValue';
            END IF;
        END IF;
    END IF;
END;

Open in new window

0
 
LVL 74

Author Comment

by:käµfm³d 👽
ID: 39675941
Yeah, I started off with everything in one query, but there is one rule that relies on a substring (last 5 of SSN) of an indexed column (negating the index). The DBAs are OK with creating a new index if it is needed, but they want to do it in a manner that scales. Today we are working with last 5 of SSN; tomorrow we might need to go to 6. My goal was to whittle down the rows to a small enough number that the substring would be inconsequential. My rules aren't giving me enough columns to work with in order to achieve this smaller subset. This is why I thought i might run each query as needed. Then the hit might not be as bad.

The oversimplification I mentioned above is due to join tables, which unfortunately are not the same tables across all rules.

As it stands right now, my latest incarnation is running at a worst-case of about two minutes for a common search criteria (e.g. last name 'Smith'). Less common criteria seems to run a bit snappier. In the web world, though, 2 minutes is an eternity!
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

This article describes some very basic things about SQL Server filegroups.
CCModeler offers a way to enter basic information like entities, attributes and relationships and export them as yEd or erviz diagram. It also can import existing Access or SQL Server tables with relationships.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now