davidlimpo davidlimpo - 4 years ago 113
SQL Question

Alternative to multiple concats in WHERE clause?

I have a huge query like this:

SELECT 'Tipification', COUNT(*) TOTAL, to_char(INITDATE,'YYYY-MM-DD')
FROM (
SELECT (TYPE || ' \ ' || SUBTYPE || ' \ ' || PROBLEM ) Triplet, INITDATE
FROM TABLE
WHERE USER IS NOT null
AND (INITDATE >= TO_DATE('2016-12-01 00:00', 'YYYY-MM-DD HH24:MI:SS'))
AND (INITDATE <= TO_DATE('2016-12-31 23:59', 'YYYY-MM-DD HH24:MI:SS'))

AND ( (TYPE || ' \ ' || SUBTYPE || ' \ ' || PROBLEM) = 'aaa \ bbb \ ccc'
OR (TYPE || ' \ ' || SUBTYPE || ' \ ' || PROBLEM) = 'aaa \ bbb \ ddd'
OR (TYPE || ' \ ' || SUBTYPE || ' \ ' || PROBLEM) = 'xxx \ yyy \ zzz'
OR (TYPE || ' \ ' || SUBTYPE || ' \ ' || PROBLEM) = 'qqq \ www \ eee'
... etc ...
)
GROUP BY to_char(INITDATE,'YYYY-MM-DD')


Where it go to more 300 string concatenations of this:
(TYPE || ' \ ' || SUBTYPE || ' \ ' || PROBLEM) = '... \ ... \ ...'
.

When performing this query, I'm getting some core dumps since it is too expensive to DB to execute.

Is there any alternative way to perform a query like this without having the DB running out of memory?

Answer Source

Oracle has a nice in function that allows you to put in multiple column combos.

select to_char(INITDATE,'YYYY-MM-DD'), count(*)
from table
where user is not null
AND (INITDATE >= TO_DATE('2016-12-01 00:00', 'YYYY-MM-DD HH24:MI:SS')) 
AND (INITDATE <= TO_DATE('2016-12-31 23:59', 'YYYY-MM-DD HH24:MI:SS')) 
and (type, subtype, problem) in (
    ('aaa','bbb','ccc'),
    ('aaa','bbb','ddd')) --  ... etc
group by to_char(INITDATE,'YYYY-MM-DD')
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download