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
Many count star queries (Read 3636 times)
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Many count star queries
09.03.07 at 14:39:10
 
Many count star queries
 
A SQL*XL user sent me a spreadsheet to inspect. I noticed he used a lot of count(*) queries. All these queries were stored in comments and he uses the refresh button to requery them all.  
 
This is a perfect way to build a spreadsheet however a more elegant technique is available to create these dasbhboard or cockpit type of spreadsheets. Use the SQLQueryValues function to "bind" a cell to the query result. Just type e.g. =SQLQueryValues("select count(*) from emp") to show the number of rows in emp in the cell. If your queries get too large so you get above Excel's 255 character formula limit, just type the SQL in a cell somewhere far away on your spreadsheet (so you cannot see it) and reference the cell address: =SQLQueryValue(Sheet22!D12)
 
Back to top
« Last Edit: 09.03.07 at 14:57:47 by Gerrit-Jan Linker »  

countstar.gif

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Re: Many count star queries
Reply #1 - 09.03.07 at 14:58:45
 
Some dashboard creation links that will be useful:
 
Tutorial: creating dashboards
http://www.oraxcel.com/cgi-bin/yabb2/YaBB.pl?num=1172472828
SQLQueryValue as Array formula
http://www.oraxcel.com/cgi-bin/yabb2/YaBB.pl?num=1162290970
Back to top
 
« Last Edit: 09.03.07 at 14:59:01 by Gerrit-Jan Linker »  

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