CODEWITHSUNDEEP
sqloracle-database
Lavariet
Lavariet

Reputation: 635

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

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

Upvotes: 1

Views: 388

Answers (2)

Jon Heller
Jon Heller

Reputation: 36807

While you cannot dynamically change the type in SQL, since version 12.2 you can use DEFAULT NULL ON CONVERSION ERROR to ignore errors:

SELECT
    *
FROM
    attr_value av
WHERE av.attr_id = 6 
  AND TO_NUMBER(value DEFAULT NULL ON CONVERSION ERROR) < 250;

But you still have to hope that every programmer remembers to do that every time. And you have to hope that you don't have a configuration that hits a weird parsing bug with ON CONVERSION ERROR.

A more permanent solution would be to change your data model to store every value as their native type. As I explain in this answer, it's simpler, safer, and faster to change the one VALUE column to three or more columns like NUMBER_VALUE, STRING_VALUE, and DATE_VALUE.

Upvotes: 1

MatBailie
MatBailie

Reputation: 86706

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

Upvotes: 0

Related Questions