CODEWITHSUNDEEP
sqloracle-databasems-accessodbc
JC.
JC.

Reputation: 317

Querying Oracle with a pick list

I have an oracle database that I have read-only access (with no permission to create temporary tables). I have a pick list (in Excel) of 28000 IDs corresponding to 28000 rows in a table which has millions of records. How do I write a query to return the 28000 rows?

I tried creating a table in access and performing a join through ODBC but Access freezes/takes an incredible long time. Would I have to create a query with 28,000 items in an IN statement?

Is there anything in PL/SQL that would make it easier?

Thank you for your time and help.

-JC

Upvotes: 1

Views: 882

Answers (5)

Theo
Theo

Reputation: 819

The best way to do it is described here: How to put more than 1000 values into an Oracle IN clause

Upvotes: 0

EvilTeach
EvilTeach

Reputation: 28882

That's a painful condition to be in. One workaround is to create a view that contains all of the ids, then join to it.

The example below is Oracle.

WITH
ids AS
(
    SELECT 314 ID FROM DUAL UNION ALL
    SELECT 159 ID FROM DUAL UNION ALL
    SELECT 265 ID FROM DUAL
)
SELECT VALUE1, VALUE2
FROM SOME_TABLE, ids
WHERE SOME_TABLE.ID = ids.ID

This basically embeds all 28000 ids, in the with clause, allowing you to do the join, without actually creating a table.

Ugly, but it should work.

Upvotes: 0

Mark Roddy
Mark Roddy

Reputation: 27986

Try creating an index on the table you created in Access.

Upvotes: 0

Graham
Graham

Reputation: 15244

What makes your 28,000 rows special?

Is there another field in the records you can use to restrict you query in a WHERE clause (or at least narrow down the millions of rows a bit)? Perhaps the ID's you're interested in fall within a certain range?

Upvotes: 3

Gandalf
Gandalf

Reputation: 9855

The max number of variables for an IN (.., .. ,,) type query is 1000 in Oracle 10g.

Upvotes: 0

Related Questions