Linker IT Software
Google
Web www.oraxcel.com
menubar-top-links menubar-top-rechts
Home Help Search Login
Welcome, Guest. Please Login.
SQL*XL: Database to Excel bridge litLIB: Excel power functions pack ExcelLock: Locking and securing your valuable Excel spreadsheets encOffice: Protect your Excel file easy and safe encOffice: Protect your Excel file easy and safe
Pages: 1
Checking the number of rows returned (Read 1357 times)
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Checking the number of rows returned
17.06.14 at 08:42:09
 
Checking the number of rows returned
 
When you have concerns that not all records were retrieved by the database, the following investigation can be fruitful. It involves figuring out how many rows the database will collect for you and then compare that number of rows with what is actually fetched.
 
Any query (select statement) can be rewritten as a count(*) query. This will bring back one row with the number of rows hit by your selection.
 
Example:
Suppose your query is: select * from emp, dept where emp.deptno = dept.deptno.
If you would like to know how many rows the database selects for you you can run one of the following queries:
 
select count(*) from (select * from emp, dept where emp.deptno = dept.deptno)
 
or  
 
select count(*) from emp, dept where emp.deptno = dept.deptno
 
The first uses the inline query technique that is supported by Oracle and MS SQL. It has the benefit that you can leave your SQL statement intact.
 
When you know the number of rows the database has selected you can compare that with the actual number of rows returned by SQL*XL. Obviously it should return exactly the same number of rows. If not, let me know!
 
Common issues when retrieving less rows than expected (should also be indicated with the row count technique):
  • The use of parameters in the query. Try with hard coded values.
  • Implicit conversions of values. Always try to explicitly convert.
       E.g. don't compare a number and a varchar field in a where clause. Oracle will implicitly convert your number to a varchar.
       The most common mistake is that of implicit date conversions. You always need to explictly convert to date.
  • Back to top
     
     

    Gerrit-Jan Linker
    Linker IT Software
    Email WWW Gerrit-Jan Linker   IP Logged
    Pages: 1