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
Testing modified SQL select statements   (Read 1999 times)
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Testing modified SQL select statements  
12.07.09 at 19:40:08
 
Testing modified SQL select statements
 
To identify the impact of a change to a SQL select statement the SQL*XL software can be used in Excel.
The impact of the change will become aparent when studying the changes in the resultsets of the old and new SQL select statement.
SQL*XL can help to map these changes out in Excel.
 
To start run both the old and new statement in SQL*XL and dump the results to an Excel sheet.
To find the differences in the two results sets you can use the vlookup Excel function. In using this function it is handy if you can use an easy name for the range where the primary key or other unique key column can be found.
Select the range containing the old key values and use insert->name in Excel to name this range old.
Similarly, select the range with the new key values and name it new.
Add a column to the old resultset and use the vlookup function to find whether the row can be found in the new resultset.
Example: =VLOOKUP(B3;new;1;FALSE)
Similarly, add a column to the new resultset and use the vlookup function to find out whether the row was in the old resultset.
Example: =VLOOKUP(I3;old;1;FALSE)
 
Now, inspect all the values in the columns where you used the vlookup function.
When the value is #N/B then that value is not in the other list.
 
This way you can easily identify which old rows are not in the new set and which new records were not in the old recordset.
Back to top
 
 

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