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
Build SQL statements in Excel based db data (Read 3116 times)
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Build SQL statements in Excel based db data
26.01.06 at 11:48:25
 
Greg from the UK asked the following question:
 
Question:
I want to retrieve some data from an Oracle database. Then I wanted to build the sql query automatically from data returned to Excel. I would like to return the data from this query automatically to the same spreadsheet.
 
Answer:
This is all possible to do. You us a mixture of Excel forumulas and SQL*XL features. It may be clearer when I write an example.
 
Suppose your task is to print a list of tables in your schema together with the number of rows in each table. You know that you can use the user_tables view in Oracle to retrieve the table names.
 
First you need to execute the following query in SQL*XL:
select table_name from user_tables;
 
When you put the results in A1, row 1 will show the column header Table_Name and from row 2 you will see the table names.
 
Now, put a formula in say C2:
=concatenate("select '",A2,"', count(*) from ",A2,";")
As a result you will see e.g.: select 'emp', count(*) from emp;
 
Copy and paste the formula down, for all rows that contain a table name value.
 
With the mouse select the block of cells with the select count(*) statements. Open SQL*XL's SQL dialog and specify you want to take SQL from the worksheet. Check the option that each cell contains a statement.
Run the SQL. If you say Ok to all in the resultset dialog all queries will be run without further interruptions.
Back to top
 
 

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