Home » RDBMS Server » Server Administration » Select
Select [message #370306] Thu, 19 August 1999 15:23 Go to next message
Amit Chauhan
Messages: 74
Registered: July 1999
Member
Hi,
I have a file containing about 5000 ids. I want to know how many of these are NOT present in the database. Say, they are emp_id of table emp. How do I find that. I know, I can find how many are present, but how to get how many are NOT present. Another point to be noted, that I cant use all these 5000 ids in the IN clause of SQL as well as it has a limit of 254.
Can anyone tell me any way to do this, may be by writting a SQL query or using a PL/SQL block using Utl_file package.
Thanks
Amit
Re: Select [message #370307 is a reply to message #370306] Fri, 20 August 1999 02:36 Go to previous messageGo to next message
Johan Thorselius
Messages: 2
Registered: August 1999
Junior Member
I would load the file keys into a PL/SQL-table in the PL/SQL-package (on the server side) and just loop through. It's a very efficient query if you use a "select 1 into dummy-var from table where file-key = tab-col and rownum = 1" and catch the exception for not found and then simply increase a counter in your procedure.

But maybe...pending on the volumes use a table with just with the keys from the file and just do a SQL-query b/w the two tables. If I would have the time I would experiment both and see the difference in efficiency.

Like it says in the books, don't use "NOT IN".

Johan Thorselius
Re: Select [message #370310 is a reply to message #370306] Fri, 20 August 1999 06:43 Go to previous messageGo to next message
Chris Hunt
Messages: 27
Registered: March 1999
Junior Member
You coudd load your file into a table, say my_ids and then do a query something like...

SELECT COUNT(*)
FROM   my_ids i
WHERE  NOT EXISTS (SELECT 'X'
                   FROM   emp e
                   WHERE  e.emp_id = i.emp_id)
/


Of course another way, since you already know how to do it, is to find how many ARE present and subtract this figure from the total number of keys.

Re: Select ids not there [message #370317 is a reply to message #370306] Sun, 22 August 1999 23:25 Go to previous messageGo to next message
KenHP
Messages: 6
Registered: August 1999
Junior Member
You don't say the nature of your 'id', but if it is strictly numeric there is an easy solution that does not require creating tables etc:

choose another table that you know contains more rows than your id table and:

select rownum from large_table
where rownum <=
(
/* you could hard code a value here if you want */
select max(id_column) from id_table
)
MINUS
select id_column from id_table

Ken
Re: Select ids not there [message #370318 is a reply to message #370317] Sun, 22 August 1999 23:32 Go to previous messageGo to next message
KenHP
Messages: 6
Registered: August 1999
Junior Member
I suppose for completeness I could suggest that if the 'ids' are not numeric, then a suitable design solution might be to create a table of all valid ids. (This can be maintained by the process that adds a valid id etc.)

Then simply do the MINUS union:
select id from valid_ids
MINUS
select id from id_table

Ken
Problem with select [message #370327 is a reply to message #370317] Fri, 27 August 1999 04:20 Go to previous messageGo to next message
MANUEL
Messages: 10
Registered: August 1999
Junior Member
In my scripts written in Perl, all my sql statement work properly less "SELECT" statement, I have a Oracle database and my operating system is Nt. I can't pick up data of the database however I can insert,delete and update data into the database, What happen?
I would very grateful if somebody could help me.
Re: Problem with select [message #370337 is a reply to message #370327] Sun, 29 August 1999 18:44 Go to previous message
KenHP
Messages: 6
Registered: August 1999
Junior Member
Manuel,
Check that your user does have select access to the tables.
It is possible to have insert,update and delete access granted without having select access.
One cause might be that select access was once granted to public and the tables have been dropped and re-created, so the select acess is lost.

Ken
Previous Topic: two-dimensional pl/sql table
Next Topic: Re: hiding the character
Goto Forum:
  


Current Time: Thu Mar 28 04:45:29 CDT 2024