1 2 3 Previous Next 30 Replies Latest reply: Mar 22, 2013 12:29 AM by 998291 RSS

    Same Query produces differenet result set

    998291
      Hi Friends,

      I have a transaction table with column Branch_Code varchar2 (15) ; below are the two queries which are producing different result set.

      select * from transaction where branch_code='625' (This produces 443 records)

      select * from transaction where branch_code=625; (This produces 422 records)

      I am not able to find the reason behind it.

      Can anyone help me with the reason.

      Regards
      LS
        • 1. Re: Same Query produces differenet result set
          bencol
          Try this:
          select distinct dump(branch_code)
          from transaction 
          where branch_code = '625';
          
          select distinct dump(branch_code)
          from transaction 
          where branch_code = 625;
          The second query is effectively
          select *
          from transaction
          where to_number(branch_code) = 625;
          • 2. Re: Same Query produces differenet result set
            ranit B
            It shouldn't behave like that.

            Are all the stats up to date.
            Else try -
            EXEC DBMS_STATS.GATHER_TABLE_STATS('<Schema_name>','<Table_name>'); 
            • 3. Re: Same Query produces differenet result set
              998291
              Hi,

              First two query produces the same result set

              TYp=1 Len=3:54,50,53

              Actually i want the reason why differnet result set?

              Regards
              LS
              • 4. Re: Same Query produces differenet result set
                BEDE
                Try:
                select * from transaction where branch_code='625' 
                minus
                 select * from transaction where branch_code=625; 
                and also:
                 select * from transaction where branch_code=625
                minus
                select * from transaction where branch_code='625' 
                This should help you figure out exactly what happens.
                • 5. Re: Same Query produces differenet result set
                  Nitesh.
                  select * from transaction where branch_code=625; (This produces 422 records)


                  First of all i want to know as how this query working for you because it will throw an error as 'invalid number' if we dont add single codes to the varchar2 column values ...

                  Check it properly ...
                  • 6. Re: Same Query produces differenet result set
                    998291
                    Hii All the stats on the table are up-to-date.

                    Also the query is executing successfully.....
                    • 7. Re: Same Query produces differenet result set
                      ranit B
                      180589 wrote:
                      First of all i want to know as how this query working for you because it will throw an error as 'invalid number' if we dont add single codes to the varchar2 column values ...
                      Not True.
                      Try creating a sample table and few inserts.

                      But yes, if you create a NUMBER column and try inserting a VARCHAR data, it'll throw an error.
                      • 8. Re: Same Query produces differenet result set
                        Nitesh.
                        Yes its always not true just tested .. I am even testing here but different bytes for varchar2 datatype giving different results ... I am even getting confuse now ...
                        • 9. Re: Same Query produces differenet result set
                          998291
                          Ranit,

                          You try creating a table with column as varchar2 datatype and insert few recorsd to it and try to select it with quotes and without quotes , it will work in both cases.

                          It will only throw a error at the time of insertion.

                          i had already tries this.

                          Regards
                          LS
                          • 10. Re: Same Query produces differenet result set
                            ranit B
                            From docs - http://docs.oracle.com/cd/B19306_01/server.102/b14200/sql_elements002.htm
                            >
                            Character and Number Values Example When a condition compares a character value and a NUMBER value, Oracle implicitly converts the character value to a NUMBER value, rather than converting the NUMBER value to a character value. In the following statement, Oracle implicitly converts '200' to 200:

                            SELECT last_name
                            FROM employees
                            WHERE employee_id = '200';
                            • 11. Re: Same Query produces differenet result set
                              m_helmet
                              -

                              made a mistake, sorry
                              Michael

                              Edited by: m_helmet on 21.03.2013 03:09
                              • 12. Re: Same Query produces differenet result set
                                Nitesh.
                                Yes accepted your reply ... But here it sometimes showing different results ... Anyways perfect no issues...
                                • 13. Re: Same Query produces differenet result set
                                  ranit B
                                  See, i tested -
                                  SQL> 
                                  SQL> 
                                  SQL> create table testing_x(
                                    2  col1 varchar2(15Char)
                                    3  );
                                  
                                  Table created.
                                  
                                  SQL> insert into testing_x values(1);
                                  
                                  1 row created.
                                  
                                  SQL> insert into testing_x values(1);
                                  
                                  1 row created.
                                  
                                  SQL> insert into testing_x values(1);
                                  
                                  1 row created.
                                  
                                  SQL> 
                                  SQL> insert into testing_x values('1');
                                  
                                  1 row created.
                                  
                                  SQL> insert into testing_x values('1');
                                  
                                  1 row created.
                                  
                                  SQL> insert into testing_x values('1');
                                  
                                  1 row created.
                                  
                                  
                                  SQL> insert into testing_x
                                    2  select '2' from dual;
                                  
                                  1 row created.
                                  
                                  SQL> insert into testing_x values('1');
                                  
                                  1 row created.
                                  
                                  SQL> commit;
                                  
                                  Commit complete.
                                  
                                  
                                  SQL> insert into testing_x
                                    2  select '2' from dual UNION ALL
                                    3  select 2 from dual;
                                  select '2' from dual UNION ALL
                                         *
                                  ERROR at line 2:
                                  ORA-01790: expression must have same datatype as corresponding expression 
                                  
                                  
                                  
                                  SQL> select * from testing_x
                                    2  where col1 = '1';
                                  
                                  COL1                                                                            
                                  ---------------                                                                 
                                  1                                                                               
                                  1                                                                               
                                  1                                                                               
                                  1                                                                               
                                  1                                                                               
                                  1                                                                               
                                  1                                                                               
                                  
                                  7 rows selected.
                                  
                                  SQL> select * from testing_x
                                    2  where col1 = 1;
                                  
                                  COL1                                                                            
                                  ---------------                                                                 
                                  1                                                                               
                                  1                                                                               
                                  1                                                                               
                                  1                                                                               
                                  1                                                                               
                                  1                                                                               
                                  1                                                                               
                                  
                                  7 rows selected.
                                  
                                  SQL> spool off
                                  • 14. Re: Same Query produces differenet result set
                                    998291
                                    Hii Ranit

                                    I agreed with your point that Oracle implicitly converts a varchar to number...i just wanted to know but why it is producing 2 differenet result set.
                                    1 2 3 Previous Next