Can I conditionally change the Type of a where condition in SQL?-CodePudding

In Oracle (PL)SQL, I currently have a table with key - value pairs. And need to search by different conditions (like smaller than or contains)

I'm currently trying to conditionally (regex) change the datatype of a column inside my where condition, so that I'm able to use a smaller than < condition for the number datatype. The problem I'm guessing is, that sql is confused about it, because in some cases it would need to compare a string and in others a number.

In my table I know that every element which has attr_id (basically a group) 11 is a string, 8 is an iso-date and 6 is a number (if there's a way to utilize that)

Table:

CREATE TABLE attr_value (id, attr_id, value) AS
  SELECT 1, 11, 'abc' FROM DUAL UNION ALL
  SELECT 2, 11, 'test123' FROM DUAL UNION ALL
  SELECT 3, 6, '123' FROM DUAL UNION ALL
  SELECT 4, 8, '2021-12-15' FROM DUAL UNION ALL
  SELECT 5, 6, '789' FROM DUAL;

Select:

SELECT
    *
FROM
    attr_value av
WHERE av.attr_id = 6 
AND CASE WHEN REGEXP_LIKE(av.VALUE, '^[- ]?[0-9] $') 
  THEN CAST(av.VALUE AS NUMBER) 
  ELSE av.VALUE 
END < 250

https://dbfiddle.uk/?rdbms=oracle_18&fiddle=9dae1841bd9d7e993fc53b7992e97149

Thanks in advance

CodePudding user response：

If you need to assert that one filter is applied before another, the simplest way is to use two different scopes (relying on the order in which they're written won't work, owing to SQL being declarative rather than imperative). The first scope restricts to atrribute 6, and the second scope restricts to values below 250.

That means using sub-queries.

https://dbfiddle.uk/?rdbms=oracle_18&fiddle=73e2fe1f5ea0d5dae4e78c725db38c2c

SELECT
  *
FROM
(
  SELECT * FROM attr_value WHERE attr_id = 6
)
  av
WHERE
  CAST(av.VALUE AS NUMBER) < 250