This discussion is archived
11 Replies Latest reply: Dec 7, 2012 5:34 AM by 6363 RSS

Query the values not exist in the table

Gaurav Newbie
Currently Being Moderated
We have a table AP_INVOICES_ALL where we have a column INVOICE_NUM, now we are searching INVOICE_NUM from a large set of values of INVOICE_NUM provided by the user.

I want a query to find the INVOICE_NUM values supplied by user that doesn't exist in AP_INVOICES_ALL table.

Database version : 11g
OS : aix 6.1

Regards,
Gaurav
  • 1. Re: Query the values not exist in the table
    jeneesh Guru
    Currently Being Moderated
    How are you getting the set of values from the user?

    Is it in a table? Then you can do a MINUS
    select invoice_num
    from your_temporary_table
    minus
    select INVOICE_NUM
    from AP_INVOICES_ALL 
    If this is not the case, provide sample data and expected output. Help us to help you better..:)

    Edited by: jeneesh on Dec 6, 2012 12:35 PM
  • 2. Re: Query the values not exist in the table
    Purvesh K Guru
    Currently Being Moderated
    Gaurav wrote:
    We have a table AP_INVOICES_ALL where we have a column INVOICE_NUM, now we are searching INVOICE_NUM from a large set of values of INVOICE_NUM provided by the user.

    I want a query to find the INVOICE_NUM values supplied by user that doesn't exist in AP_INVOICES_ALL table.
    How does user supply the values? Are they in a Collection variable/a Temp table?

    This query assuming you pass the Invoice Num one at a time in a scalar variable, will tell you if the Invoice Num exists in table. 0 indicates absence, Null indicates presence.
    select decode(count(*), 0, v_invoice_num, null) not_exists_vals
      from ap_invoices_all
    where invoice_num = v_invoice_num;
    If a Temp Table, then this may serve the purpose (Untested obviously due to lack of tables)
    select *
      from (
    select a.invoice_num, t.invoice_num temp_invoice_num
      from ap_invoices_all a right outer join invoice_temp_tab t
         on (a.invoice_num = t.invoice_num)
    ) a
    where a.invoice_num is null and t.invoice_num is not null;
  • 3. Re: Query the values not exist in the table
    Chanchal Wankhade Journeyer
    Currently Being Moderated
    Hi,
    If you are getting values in the table then the above solution is there but if you are getting the values in excel or text file then you need to upload the data into the table by using sql loader or by using external table.

    if the case then

    load data from excel or plain file into oracle table by using sql loader.(loader is server based utility)

    create table in oracle like create table table_name as column specification.

    If you have excel then save it as .csv file and create sql loader control file like
    oPTIONS (SKIP=1)
    load data 
    infile 'Path_where_you_saved_csv_file'
    truncate 
    into table table_name_you_have_created
    fields terminated by ','
    optionally enclosed by '"'
    trailing nullcols
    (your_column_name)
    
    {code}
    call control file from command prompt from the path where your control file and csv or plain file resides.
    
    {code}
    sqlldr userid=user_name/passowrd@database_name control=control_file_name
    {code}
    
    above will insert data into table.
    now you can use the select query...                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
  • 4. Re: Query the values not exist in the table
    Manik Expert
    Currently Being Moderated
    External table would be my pick, as u can directly frame the query on top of it.

    Using exists or in clause match it with your table values.. thats it.. job done ;)

    Cheers,
    Manik.
  • 5. Re: Query the values not exist in the table
    Gaurav Newbie
    Currently Being Moderated
    I have some values, not from table. Is it neccessery to fetch data from table? Isn't it possible to treat values as a column. i.e.
    ('33357173-64001953-1-1PKJ4TF',
    '33389902-81030214-1-1P611DE',
    '33315430-93356365-1-1PGDH37') INVOICE_NUM minus 
    
    (SELECT INVOICE_NUM FROM AP_INVOICE_ALL WHERE INVOICE_NUM IN ('33357173-64001953-1-1PKJ4TF',
    '33389902-81030214-1-1P611DE',
    '33315430-93356365-1-1PGDH37') )
  • 6. Re: Query the values not exist in the table
    jeneesh Guru
    Currently Being Moderated
    You have some values- But where ?

    Manually passed?

    Or passed as a multi value string?

    Or as a collection?

    How are you passing it to the db?
  • 7. Re: Query the values not exist in the table
    Gaurav Newbie
    Currently Being Moderated
    I have set of values for INVOICE_NUM. I want to get those values from set of values which doesn't exist in AP_INVOICES_ALL table. I do not want to create a temporary table for those set of values.
  • 8. Re: Query the values not exist in the table
    6363 Guru
    Currently Being Moderated
    You can select the values from a collection.
    SQL> select column_value from
      2  table(sys.odcivarchar2list('DUAL','ALL_OBJECTS','TESTX1','USER_OBJECTS','TESTX99'))
      3  where not exists
      4      (select null from all_objects
      5      where object_name = column_value);
    
    COLUMN_VALUE
    --------------------------------------------------------------------------------
    TESTX99
    TESTX1
    You are going to have assign the values to some object that SQL recognizes to use select statement on them. You have been asked multiple times where the values are, if you don't know or can't say apart from where you don't have them, then how do you expect SQL to select from them?
  • 9. Re: Query the values not exist in the table
    rp0428 Guru
    Currently Being Moderated
    >
    We have a table AP_INVOICES_ALL where we have a column INVOICE_NUM, now we are searching INVOICE_NUM from a large set of values of INVOICE_NUM provided by the user.

    I want a query to find the INVOICE_NUM values supplied by user that doesn't exist in AP_INVOICES_ALL table.
    . . .
    I have some values, not from table. Is it neccessery to fetch data from table? Isn't it possible to treat values as a column. i.e.
    . . .
    (SELECT INVOICE_NUM FROM AP_INVOICE_ALL WHERE INVOICE_NUM IN ('33357173-64001953-1-1PKJ4TF',
    '33389902-81030214-1-1P611DE',
    '33315430-93356365-1-1PGDH37') )
    >
    You were pretty close, thanks for posting the sample data and table information.

    Just do the select of your values from DUAL with a MINUS query from your AP_INVOICE_ALL table
    SELECT * from (
    SELECT '33357173-64001953-1-1PKJ4TF' from dual UNION ALL
    SELECT '33389902-81030214-1-1P611DE' from dual UNION ALL
    SELECT '33315430-93356365-1-1PGDH37' from dual)
    minus
    SELECT INVOICE_NUM FROM AP_INVOICE_ALL;
  • 10. Re: Query the values not exist in the table
    Gaurav Newbie
    Currently Being Moderated
    SQL> select column_value from
    2 table(sys.odcivarchar2list('DUAL','ALL_OBJECTS','TESTX1','USER_OBJECTS','TESTX99'))
    3 where not exists
    4 (select null from all_objects
    5 where object_name = column_value);

    COLUMN_VALUE
    --------------------------------------------------------------------------------
    TESTX99
    TESTX1


    Thanks a lot for your help and support

    Kindly explain what is function of table(sys.odcivarchar2list('DUAL','ALL_OBJECTS','TESTX1','USER_OBJECTS','TESTX99')) ? I mean is it creating a new table with these values with these values or just treating these as table value?

    My question is solved with the solution provided by you.
  • 11. Re: Query the values not exist in the table
    6363 Guru
    Currently Being Moderated
    The supplied data type ODCIVarchar2List is a VARRAY or Collection type, it is basically a SQL object that can hold an array of values.

    http://docs.oracle.com/cd/E11882_01/appdev.112/e10765/ext_types_ref.htm#ADDCI5016

    http://docs.oracle.com/cd/E11882_01/appdev.112/e10765/introduction.htm#ADDCI4250

    >
    Collection Types

    Collections are SQL data types that contain multiple elements. Elements, or values, of a collection are all from the same type hierarchy. In Oracle, collections of complex types can be VARRAYs or nested tables.

    A VARRAY type contains a variable number of ordered elements and can be used for a column of a table or an attribute of an object type. The element type of a VARRAY can be either a native data type, such as NUMBER, or a user-defined type.
    >

    The TABLE keyword allows the values in the varray to be selected in SQL as if it were a table. There is no underlying table and the values in the array are stored in memory much like the way all other programming languages handle arrays of values.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points