PLSQL search procedure to return best and near matches based on multiple criteria

Question

i have a requirement to write a stored procedure that takes 6 potential parameters. I need to do a search on a table that returns rows that match as closely as possible to the search parameters entered.

For example.

Table quotes (status {incomplete, pending, ordered}, date_created date, type {autoGenerated,manual,split} company {PipedPiper, Hooli,Oracle...} ... )

Here is a use case to illustrate what i need to achieve. Lets say a user enters the following search criteria on the front end: they want to get a list of quotes that met the following criteria, or the next best match:

quote status = pending company = PipedPiper type=manual date = Not selected

So, once the stored proc is called it should get the list of quotes that meet that criteria. If, no quotes are in pending then any Piped Piper manual quotes should be returned. Perhaps there are no pending quote for PipedPiper. At that point it should return any manual quotes in the db.

The SP needs to return a cursor and also a flag to confirm what criteria entered is met by the row. please note, it should not return anything against the date flag as the user did not select any date entries for this particular search. For example

if anyone can guide me in how to code this, provide pseudo code, or any guidance is much appreciated. I am poor at SQL so I am struggling to design an SQL statement that can achieve the above.

thanks in advance, Adrian.

Answer 1

if I correctly understood, that criteria:

1) status = pending

2) company = PipedPiper

3) type=manual

4) date = Not selected

means (1 or 2 or 3 or 4) = true

if so, there I can see two way. firstly you can use dynamic sql, secondly you can use case keyword. I think you know how to use dynamic sql, I will show example for using case keyword.

select * from your_table t

   where case 
         when param_status != 'Not selected' and param_status = t.status then 1
         when param_status = 'Not selected' then 1 else 0 end = 1

         and case 
         when param_company != 'Not selected' and param_company = t.company then 1
         when param_company = 'Not selected' then 1 else 0 end = 1

       ...

    -- Checking filter parameters, if non of selected then returning empty --
     and  case when param_status = 'Not selected' and param_company = 'Not selected' ... 
         then 0 else 1 end = 1 

I hope this will help you.