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.
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