sql give list of numbers in where clause-CodePudding

I was looking at this example. This makes it look like single quotes are not required in the where clause when you give a list of numbers. I am using an oracle database if that makes a difference.

https://www.sqlservertutorial.net/sql-server-basics/sql-server-in/

SELECT
    product_name,
    list_price
FROM
    production.products
WHERE
    list_price IN (89.99, 109.99, 159.99)
ORDER BY
    list_price;

So why is toad complaining when I do this? It says invalid identifier as the error message.

This is the query that fails.

select * from table1 where id in (1, 2, 3, 4);

Toad does not complain when I do this.

select * from table1 where id in ('1', '2', '3', '4');

CodePudding user response：

Check to make sure you are using your expected datatype. In my case I was using CHAR(12) thats why I needed quotes.

SELECT 
    COLUMN_NAME,
    DATA_TYPE,
    DATA_LENGTH,
    DATA_PRECISION,
    DATA_SCALE
FROM ALL_TAB_COLS
Where TABLE_NAME = 'table1';

If that doesn't work your table may be in capital letters so try this.

SELECT 
    COLUMN_NAME,
    DATA_TYPE,
    DATA_LENGTH,
    DATA_PRECISION,
    DATA_SCALE
FROM ALL_TAB_COLS
Where TABLE_NAME = upper('table1');

CodePudding user response：

Since the question was about fetching different numbers, let's have a look on an easy way to do this.

Of course, if the column always holds numbers only and strings should not be allowed, we should just change its datatype.

And of course, if we just want to check for a few numbers like 1,2 and 3, we can use an IN clause like shown in the question.

But let's assume we can't change the datatype because also strings can occur and we need to accept a big range of numbers.

For example, we want to accept all numbers between 200 and 300. We dislike to write an IN clause with 101 values, don't we?

So we can use VALIDATE_CONVERSION to make sure we consider numeric entries only.

Then we use CAST AS INT to check the number should be between 200 and 300.

The query will be this one:

SELECT id
FROM table1
WHERE VALIDATE_CONVERSION(id AS NUMBER) = 1
AND CAST (id AS INT) BETWEEN 200 AND 300
ORDER BY id; -- sorting is of course not needed. Remove it if not intended.

We can try this out here: db<>fiddle