11 Replies Latest reply: Dec 7, 2012 7:34 AM by 6363 RSS

    Query the values not exist in the table

    Gaurav
      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
          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
            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
              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
                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
                  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
                    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
                      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
                        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
                          >
                          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
                            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
                              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.