Home » SQL & PL/SQL » SQL & PL/SQL » get numbers which is not in the (ORACLE 10G,sOLARIS)
get numbers which is not in the [message #594698] Sun, 01 September 2013 23:55 Go to next message
prejib
Messages: 120
Registered: March 2009
Location: India
Senior Member
Hi ,
I want to get the list of values which is not the given list .

I have the below data in my emp table
SQL> select emp_id from emp;

    EMP_ID
----------
       101
       102
       103
       104
       105
       106
       108
       110
       120
       220
       225

11 rows selected.

But I need to display the values from the list of values which are not in the emp table . So the result will be '3000,3002,3003'. can you please help me to write the query?

SQL> select emp_id from emp where emp_id ?;
    EMP_ID
----------
       3000
       3002
       3003


Re: get numbers which is not in the [message #594699 is a reply to message #594698] Mon, 02 September 2013 00:03 Go to previous messageGo to next message
BlackSwan
Messages: 22907
Registered: January 2009
Senior Member
HUH?

I must have blinked, since I have no idea from where 3000, 3002, or 3003 were obtained.

why is the desired solution not "ID10T"?
Re: get numbers which is not in the [message #594702 is a reply to message #594699] Mon, 02 September 2013 00:43 Go to previous messageGo to next message
prejib
Messages: 120
Registered: March 2009
Location: India
Senior Member
Hi,

Actually 3000, 3002, 3003 values are not in the table , But some how those invalid values came in the list (.csv file - ~2500 Nos. ) . So I have to taken out the list of values not in the table for validation.
Re: get numbers which is not in the [message #594703 is a reply to message #594702] Mon, 02 September 2013 00:49 Go to previous messageGo to next message
BlackSwan
Messages: 22907
Registered: January 2009
Senior Member
why is the desired solution not "ID10T"?
Re: get numbers which is not in the [message #594705 is a reply to message #594703] Mon, 02 September 2013 00:59 Go to previous messageGo to next message
Littlefoot
Messages: 19694
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
What you described so far suggests that external table feature might be used. CSV file you mentioned would be external table's source, so you'd be able to perform various queries on it, such as
select emp_id from emp
minus
select emp_id from csv_file_as_external_table
Re: get numbers which is not in the [message #594711 is a reply to message #594705] Mon, 02 September 2013 02:30 Go to previous messageGo to next message
prejib
Messages: 120
Registered: March 2009
Location: India
Senior Member
Hi ,

Thanks for the suggestion. But I am not clear ,how to use this ? Can you please give me one example?
Re: get numbers which is not in the [message #594712 is a reply to message #594711] Mon, 02 September 2013 02:39 Go to previous messageGo to next message
Michel Cadot
Messages: 59424
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Can you explain where does 3000,3002,3003 come from?

Regards
Michel
Re: get numbers which is not in the [message #594714 is a reply to message #594712] Mon, 02 September 2013 03:12 Go to previous messageGo to next message
Littlefoot
Messages: 19694
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Here's an example.

I'm connected as SCOTT and I have access to the following directory. Note that directory MUST be created on a database server, not locally! Therefore, c:\temp is not on my PC but on a server. Directory (an Oracle object) must be created by a privileged user (SYS in my case) and, afterwards, it should grant privileges to other users (SCOTT was granted READ and WRITE). You'd do that as
SQL> create directory ext_dir as 'c:\temp';

Directory created.

SQL> grant read, write on directory ext_dir to scott;

Grant succeeded.

SQL>

SQL> show user
USER is "SCOTT"
SQL> select * from all_directories;

OWNER                          DIRECTORY_NAME                 DIRECTORY_PATH
------------------------------ ------------------------------ --------------------
SYS                            EXT_DIR                        c:\temp

SQL>


Here's a sample CSV file. The first 3 lines represent employees from Scott's EMP table. The rest is brand new:
7369;SMITH
7499;ALLEN
7521;WARD
3001;LITTLE
3002;FOOT
3003;ZOMBIE


Now, create an external table. For much more information and syntax examples, see the documentation.
SQL> create table ext_table_csv
  2    (emp_id number,
  3     ename  varchar2(20)
  4    )
  5  organization external
  6    (type oracle_loader
  7     default directory ext_dir
  8     access parameters
  9      (records delimited by newline
 10       fields terminated by ';'
 11       missing field values are null
 12      )
 13     location('empid_test.txt')
 14    )
 15  reject limit unlimited;

Table created.

SQL>

So far, so good. Finally, let's find employees listed in the CSV file that do not exist in Scott's EMP table:
SQL> select emp_id, ename from ext_table_csv
  2  minus
  3  select empno, ename from emp;

    EMP_ID ENAME
---------- --------------------
      3001 LITTLE
      3002 FOOT
      3003 ZOMBIE

SQL>


Now it's up to you to create any kind of queries on the external table (as it, apparently, works).
Re: get numbers which is not in the [message #594717 is a reply to message #594714] Mon, 02 September 2013 04:12 Go to previous message
prejib
Messages: 120
Registered: March 2009
Location: India
Senior Member
Thanks a lot for your guidance and helps ... Smile
Previous Topic: What is the Worng in this Code
Next Topic: simple problem of creating a table inside execute immediate, but can not debug the error
Goto Forum:
  


Current Time: Fri Oct 24 02:29:50 CDT 2014

Total time taken to generate the page: 0.14308 seconds