1 2 Previous Next 19 Replies Latest reply on Jun 1, 2015 7:34 AM by Jonathan Lewis

    IN vs NOT IN behaviour

    Lalith_Jain

      Hi,

       

      We've this query which throws invalid number

       

      SELECT * FROM table A 

      WHERE A.corporate_id IN (59375,54387) AND TRUNC(created_dt) BETWEEN '19-DEC-14' AND '25-DEC-14';


      However it works fine if we use not in instead of in


      SELECT * FROM table A 
      WHERE A.corporate_id  NOT IN (59375,54387) AND TRUNC(created_dt) BETWEEN '19-DEC-14' AND '25-DEC-14';


      Please assist.

        • 1. Re: IN vs NOT IN behaviour
          RogerT

          Hmmm

           

          What is the datatype of corporate_id?

           

          Why are you going to compare a DATE trunc(created_dt) to STRINGS '19-DEC-14'

           

          What is you DB Version?

          • 2. Re: IN vs NOT IN behaviour
            BluShadow

            We don't have your table or data, so it's a bit hard for us to test.

             

            Please read: Re: 2. How do I ask a question on the forums?

             

            Also, when comparing dates, always use DATE datatypes, not varchar2 strings, and always use 4 digit years (we got rid of all the Y2K issues back in the late 1990's and we don't need people reintroducing the problem).

             

            e.g. use:

             

             

            BETWEEN to_date('19-DEC-2014','DD-MON-YYYY') AND to_date('25-DEC-2014','DD-MON-YYYY');

             


            • 3. Re: IN vs NOT IN behaviour
              Frank Kulash

              Hi,

               

              As mentioned already,  whenever you have a question, please post a little sample data (CREATE TABLE and INSERT statements, relevant columns only) for all the tables involved, and the exact results you want from that data, so that the people who want to help you can re-create the problem and test their ideas.

              Explain, using specific examples, how you get those results from that data.

              Always say what version of Oracle you're using (e.g. 11.2.0.2.0).

              See the forum FAQ:  Re: 2. How do I ask a question on the forums?

               

              As asked already, what is the data type of created_date?

              If created_date is any type other than DATE (or TIMESTAMP, but I'll just say DATE from now on),  then that's the answer right there.  Storing information about dates in any data type other than DATE is simply asking for trouble.  If that's what you're doing, then the application is failing as it was designed to.

              If created_date is a DATE, don't try to compare it to strings, such as '19-DEC-14'.  Use TO_DATE, as Blushadow did in reply #2, or use DATE literals, like this:

              AND     created_date  >= DATE '2014-12-19'

              AND     created_date  <  DATE '2014-12-26'

              • 4. Re: IN vs NOT IN behaviour
                chris227

                The phenomenon you are facing is due short curcuit evaluation and has nothing do to with using in or not in.

                In the case the corporate_id in is in the numbers given the and-expression is false (because of the not) and the second part is not evaluated.

                If it is eveluated an error is raised because of the faulty usage of dates as others pointed already out.

                 

                >select*

                  2  from dual

                  3  where 1=1 and 'a'=1

                  4  /

                where 1=1 and 'a'=1

                              *

                ERROR at line 3:

                ORA-01722: invalid number

                 

                 

                >ed

                Wrote file afiedt.buf

                 

                  1  select*

                  2  from dual

                  3* where 1=2 and 'a'=1

                >/

                 

                no rows selected

                • 5. Re: IN vs NOT IN behaviour
                  Chris Hunt

                  Get used to ensuring that the expressions on either side of a comparison operator ( =, IN, NOT IN, >, <, etc...) are of the same datatype, rather than expecting Oracle to figure out what you mean and do it for you. Because sometimes it doesn't, and that leads to errors like this one and others that can be harder to diagnose.

                   

                  So if A.corporate_id is a VARCHAR2, compare it with VARCHAR2s instead of numbers. If TRUNC(created_dt) is a DATE, compare it with dates instead of strings.

                   

                  You'll grow fewer grey hairs as a result.

                  • 6. Re: IN vs NOT IN behaviour
                    Frank Kulash

                    Hi,

                    Chris Hunt wrote:

                     

                    Get used to ensuring that the expressions on either side of a comparison operator ( =, IN, NOT IN, >, <, etc...) are of the same datatype, rather than expecting Oracle to figure out what you mean and do it for you. Because sometimes it doesn't, and that leads to errors like this one and others that can be harder to diagnose.

                     

                    So if A.corporate_id is a VARCHAR2, compare it with VARCHAR2s instead of numbers. If TRUNC(created_dt) is a DATE, compare it with dates instead of strings.

                     

                    You'll grow fewer grey hairs as a result.

                    That's a very good point.

                     

                    Oracle tries very hard not to raise errors, so (for example) if you use a string in a place where a DATE is required, Oracle will try to implicitly convert the string to a DATE.  Sometimes it works, sometimes it doesn't.  sometimes, when the conversion does work, it produces the results you expect, sometimes it doesn't.  If you want code that always works, and always does what you expect,then don't rely on implicit conversions.  Use DATEs where DATEs are expected.  Explicitly convert strings to DATEs ((using TO_DATE) when necessarry.

                    • 7. Re: Re: IN vs NOT IN behaviour
                      chris227

                      Chris Hunt wrote:

                       

                      Get used to ensuring that the expressions on either side of a comparison operator ( =, IN, NOT IN, >, <, etc...) are of the same datatype, rather than expecting Oracle to figure out what you mean and do it for you. Because sometimes it doesn't, and that leads to errors like this one and others that can be harder to diagnose.

                       

                      So if A.corporate_id is a VARCHAR2, compare it with VARCHAR2s instead of numbers. If TRUNC(created_dt) is a DATE, compare it with dates instead of strings.

                       

                      You'll grow fewer grey hairs as a result.

                      Good hint!

                      However in this case we seem to have ORA-01722: invalid number.

                      Shouldnt we expect ORA-01790 if implicit type conversion was the reason?

                      • 8. Re: IN vs NOT IN behaviour
                        Lalith_Jain

                        Thank you for all the responses.

                        its not about the date, without the date clause also it throws the invalid number error.

                        and a.corporate_id is a VARCHAR2 type.

                         

                        My question is upon using IN I get invalid number error However when I use NOT IN I don't get an error.

                        • 9. Re: IN vs NOT IN behaviour
                          RogerT

                          Of course it is not about the date ... you would get a different error-message if it was about the date but

                           

                          you just do the same mistake you do with the corporate_id (mixing datatypes) with your dates too....

                           

                          so if your corporate_id is a VARCHAR then you have to do

                           

                          A.corporate_id IN ('59375','54387')


                          instead of comparing it to number literals.

                           

                          And the same is true for your "date" comparison too.

                           

                          hth

                           

                           




                          • 10. Re: IN vs NOT IN behaviour
                            Frank Kulash

                            Hi,

                             

                            RoaringLion wrote:

                             

                            Thank you for all the responses.

                            its not about the date, without the date clause also it throws the invalid number error.

                            and a.corporate_id is a VARCHAR2 type.

                             

                            My question is upon using IN I get invalid number error However when I use NOT IN I don't get an error.

                            Once again, don't use one datatype when a different data type is required.

                            If corporate_id is a VARCHAR2, then don't try to compare it to a NUMBER (such as 59375); compare it to another string (such as '59375').

                             

                            Once again, post CREATE TABLE and INSERT statements for your sample data whenever you have a question.  Don't wait until reply #8 to give essential information.

                            • 11. Re: IN vs NOT IN behaviour
                              Lalith_Jain

                              Thanks Roger.

                               

                              I understand if I don't put it as a string literal, oracle will implicitly convert while comparing. I've already resolved this issue by putting those numbers inside single quotes.

                               

                              What I've not understood is -

                               

                              Why it only threw error when I used "IN" ?

                              Why it did not threw error when I used "NOT IN" ?

                               

                              Thanks

                              • 12. Re: IN vs NOT IN behaviour
                                RogerT

                                Have you done :

                                 

                                SELECT * FROM table A 
                                WHERE A.corporate_id  NOT IN (59375,54387) ;

                                 

                                without the DATE comparison part without running into an error?

                                 

                                hth

                                 

                                 


                                • 13. Re: IN vs NOT IN behaviour
                                  Lalith_Jain

                                  Yes, thats exactly what I did and i din't run into any error.

                                  • 14. Re: IN vs NOT IN behaviour
                                    Chris Hunt

                                    I don't know why you get the error for IN(), but not for NOT IN(). Different query paths maybe? Maybe if you can produce a small set of the data in A.corporate_id that reproduces the problem, the answer will become apparent.

                                     

                                    However, I do know that the problem is caused by Oracle implicitly doing this:

                                     

                                    WHERE TO_NUMBER(A.corporate_id) IN (59375,54387)

                                     

                                    where there are values in A.corporate_id that are not valid numbers. Either you can spend time figuring out why Oracle sometimes copes with this and sometimes doesn't, or you can stop the problem arising altogether:

                                     

                                    WHERE A.corporate_id IN ('59375','54387')   -- compare VARCHAR2 to VARCHAR2, no number conversion required.

                                     

                                    Like I said, fewer grey hairs.

                                     

                                    PS. If corporate Id is supposed to be a number, why store it in a VARCHAR2?

                                    1 2 Previous Next