1

I have a huge set of employee ids (1000 to 10000) which I can't pass through IN clause. I don't want to use a Global Temporary table since I don't have write access in the DB (only read access).

What is the best way to pass the data to the query?

SELECT EMPLOYEE_NAME,AGE,SALARY 
from EMPLOYEE 
WHERE EMPLOYEE_ID IN (???)
  • You can split up the IN clauses: where employe_id IN (.. first 1000 ids) or employee_id IN (next 1000 ids) or employee_id IN (...) but that is going to be horribly slow. A temporary table is the better solution – a_horse_with_no_name Oct 20 '16 at 11:26
1

You will need some level of write access to the DB for this task. You could store the data in a CSV file and then create an external table mapped to that file. Or you could use a Global Temporary Table. As long as you explicitly drop it when you're finished, it would meet your requirements.

1

After some digging around it does seem like write access can be granted but the constraint here is there should be no leftovers i.e no empty temp table lying around after the transaction is done.

Going with the temp table solution and dropping it at the end of the transactions.

0

I would recommend using a CTE at the begining of your query, and joining all data sets together. You could build the query in Excel then copy it into your preferred DBMS.

WITH EMPLOYEES_A AS (
SELECT
      123 AS EMPLOYEE_ID
FROM DUAL
UNION ALL
SELECT
      456
FROM DUAL
/*
REPEATE AS MANY TIMES AS YOU LIKE
*/
)

SELECT
        EMPLOYEE_NAME
      , AGE
      , SALARY
FROM EMPLOYEE
  INNER JOIN
    EMPLOYEES_A
    ON EMPLOYEE.EMPLOYEE_ID = EMPLOYEES_A.EMPLOYEE_ID

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.