PL/SQL "Where" conditional "WHERE" clause

Question

I am trying to make a a query against a table and am kind of stuck in constructing the WHERE clause. Table has a column "SUBCLASS" that has a value like "UN" or "U*".

"U*" implies a matching of "U" and any other character(e.g. UB, UC, ...).

Table looks like:

ID    ISC    SUBCLASS
---   ----   ---------
1     ABC        UN
2     DEF        UN
3     DEF        U*

Given a string UC12341001000012 or UN12341001000012, how would I construct the WHERE clause.

I tried:

SELECT * FROM MYTABLE
WHERE x AND y AND
(SUBCLASS='UC' OR SUBSTR(SUBCLASS, 1, 1) = SUBSTR('UC',1,1))

but it returns all rows. (I am using 'UC' here but actually it is a parameter passed to a stored procedure).

So, given UC12341001000012, I should get the third record, given UN12341001000012 I should get first two records.

Answer 1

The (slightly) tricky bit is not including the wildcard U* row when there are exact matches. There are various approaches with subqueries and unions etc.; this one uses an inline view to flag and count the exact and wildcard matches, and then filters that inline view:

select id, isc, subclass, exact, wild
from (
  select id, isc, subclass,
    case when subclass = substr(:str, 1, 2) then 'Y' end as exact,
    case when subclass = substr(:str, 1, 1) || '*' then 'Y' end as wild,
    count(case when subclass = substr(:str, 1, 2) then subclass end) over () as exact_cnt
  from mytable
  where subclass like substr(:str, 1, 1) || '%' -- optional
)
where exact = 'Y' or (wild = 'Y' and exact_cnt = 0)

I've used a :str bind variable in place of your shorter literal, partly because I think it's clearer, but also because with just UC it isn't obvious why I've used more substr() calls than you had; since with your full longer values you only want to look at the first two anyway.

You can change it around a bit to not repeat the case expression (with another layer of inline view/CTE that you then count from), or change the inner filter to explicitly look for the same things the case expression is checking (or leave it out - depends on volume, indexes...), etc., but hopefully gives you the idea.

With a CTE to provide your sample subclass data:

var str varchar2(20);

exec :str := 'UC12341001000012';

-- CTE for sample data
with mytable (id, isc, subclass) as (
  select 1, 'ABC', 'UN' from dual
  union all select 2, 'DEF', 'UN' from dual
  union all select 3, 'DEF', 'U*' from dual
)
-- actual query
select id, isc, subclass
from (
  select id, isc, subclass,
    case when subclass = substr(:str, 1, 2) then 'Y' end as exact,
    case when subclass = substr(:str, 1, 1) || '*' then 'Y' end as wild,
    count(case when subclass = substr(:str, 1, 2) then subclass end) over () as exact_cnt
  from mytable
  where subclass like substr(:str, 1, 1) || '%' -- optional
)
where exact = 'Y' or (wild = 'Y' and exact_cnt = 0);

        ID ISC SU
---------- --- --
         3 DEF U*

exec :str := 'UN12341001000012';

<same query>

        ID ISC SU
---------- --- --
         1 ABC UN
         2 DEF UN

in case of multiple rows being returned

If you only want one of the exact-match rows, you could add a row_number() call in the inline view - with a suitable order by for however you want to split ties - and then add that to the outer filter:

select id, isc, subclass
from (
  select id, isc, subclass,
    case when subclass = substr(:str, 1, 2) then 'Y' end as exact,
    case when subclass = substr(:str, 1, 1) || '*' then 'Y' end as wild,
    count(case when subclass = substr(:str, 1, 2) then subclass end) over () as exact_cnt,
    row_number() over (partition by subclass order by isc) as rn
  from mytable
  where subclass like substr(:str, 1, 1) || '%' -- optional
)
where (exact = 'Y' or (wild = 'Y' and exact_cnt = 0))
and rn =1

... or you can initially select all three rows but order them so that the wildcard one comes last, before applying a rownum filter:

select id, isc, subclass
from (
  select id, isc, subclass
  from mytable
  where subclass = substr(:str, 1, 2)
  or subclass = substr(:str, 1, 1) || '*'
  order by case when subclass like '_*' then 2 else 1 end,
    isc -- or however you want to split ties
)
where rownum = 1