| Select [message #370306] |
Thu, 19 August 1999 15:23  |
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  |
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 |
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 |
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 |
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 |
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 |
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
