Using subquery with IN clause

Question

I am stuck at one particular problem here, I am fetching ID's from one column that are like this ',90132988,90133148,72964884,' Let's say this value is stored in a column ColumnA of Table1.

I want to use this in another query that's using IN clause something like this.

SELECT * 
FROM USER_GROUP 
WHERE ID IN (SELECT CONCAT(CONCAT('(',REPLACE(LTRIM(RTRIM(REPLACE(COLUMNA, ',', ' '))), ' ', ',')),')') FROM Table1)

When I run this query I get invalid number error. Any suggestions ?

@Acroyear The datatype of ID is BIGINT and the columnA is multilist column so it's VARCHAR daatype — Muhammad Asim
– Muhammad Asim, Commented Feb 20, 2020 at 16:40
There is your problem. You have to convert one side or the other so you are comparing like data types. — Acroyear
– Acroyear, Commented Feb 20, 2020 at 21:03

Gordon Linoff · Accepted Answer · 2020-02-20 16:39:24Z

1

You get a number error because id is a number. So, Oracle wisely assumes that the subquery returns numbers.

I think the logic you want is:

SELECT ug.*
FROM USER_GROUP ug
WHERE EXISTS (SELECT 1
              FROM Table1 t1
              WHERE t1.COLUMNA LIKE '%,' || ug.ID || ',%'
             );

I strongly discourage you from storing number lists as strings. Just store one row per number. That is really much simpler and the resulting code will be more efficient.

answered Feb 20, 2020 at 16:39

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Muhammad Asim Over a year ago

I'm sorry to ask again but as I have marked it complete would you be able to also tell me how to use 2 exists in same query ? I want to use another column from Table1 with User Group table having same type of vaules

Gordon Linoff Over a year ago

@MuhammadAsim . . . You would just add and exists . . . to the end of the query.

Muhammad Asim Over a year ago

Well I was hoping that's would work but I don't understand query kept executing and doesn't stop. Well I'll have a look. Do I need to use User Group table again with another alias ? I tried both ways though

Muhammad Asim Over a year ago

Does a NULL column affect this query if ColumnA is null

Gordon Linoff Over a year ago

@MuhammadAsim . . . NULL will not match anything. That is expected behavior.

Acroyear · Accepted Answer · 2020-02-20 21:06:43Z

0

You can try converting "ID" using TO_CHAR, but you will lose the use of any index on ID if you have one.

SELECT * 
FROM USER_GROUP 
WHERE TO_CHAR(ID) IN (SELECT CONCAT(CONCAT('(',REPLACE(LTRIM(RTRIM(REPLACE(COLUMNA, ',', ' '))), ' ', ',')),')') FROM Table1)

answered Feb 20, 2020 at 21:06

Acroyear

1,4614 gold badges25 silver badges40 bronze badges

Collectives™ on Stack Overflow

Using subquery with IN clause

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related