CODEWITHSUNDEEP
sqloracle-database
Randy
Randy

Reputation: 131

Custom query based on values entered in search

I am hoping someone can at least point me in the right direction on something I am struggling to put together.

Conceptually, it would appear to be simple: In a search form a user selects values from a dropdown in one field, in 2 other fields manually enters a value, and then clicks a button to begin a search of a database. If all 3 values selected/entered match a record in the db then show the record, but if 1 or more values does/do not match (but at least 1 of them does match), then just display a message, like "One or more values do not match, please try again". If none of the values match display a different message, something like "Record not created".

I cannot wrap my head around how this would be done in a SQL query. Any advice would be greatly appreciated. Thank you.

Upvotes: 0

Views: 43

Answers (1)

unleashed
unleashed

Reputation: 771

I suppose you could do something like this. This uses three filters that are also used in the select clause to determine if the filter is used or not. You could then check this for no rows, rows with exactly 3 filters hit, or rows return that have less then 3 filters hit.

    with test_data(id, name, value) as (
        select 1, 'bob', 100 from dual union all
        select 2, 'sue', 90 from dual union all
        select 3, 'bill', 110 from dual union all
        select 4, 'tom', 125 from dual union all
        select 5, 'bart', 95 from dual)
    select test_data.*,
           case when id >= 3 then 1 else 0 end 
            + case when name like 'b%' then 1 else 0 end 
            + case when value >= 100 then 1 else 0 end as filter_count
    from   test_data
    where  id >= 3
        or name like 'b%'
        or value >= 100;

Upvotes: 1

Related Questions