Linker IT Software
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
Easy run queries and take parameters (Read 2773 times)
Gerrit-Jan Linker
YaBB Administrator

Posts: 75
Easy run queries and take parameters
01.06.06 at 15:59:03
Easy run queries and take parameters
Enrique from Spain asked the following question:
This is what we need.
We have several queries in our database. All of them return a column or a row from a parameter.
For example, you can call the query "Employee_data (9999)" which will return a column with the data of employee 9999.
The goal is to put those returned data in the Excel in a simply way. We need to give our users the possibility to insert in the Excel a row containing those data. For example in the Excel, the users can fill a column on the left with several employee IDs, and insert on the right a row per employee with their data.
The requirements for the Excel are:
  • We have several queries. We need something like a combo, a function list, etc... where the users can select easily the query they need.
  • The rows can be inserted anywhere.
  • The way to insert a row has to be simple. Something like to insert a math function.
I hope you can help us. I honestly think your application can be used for our needs. We just need to ensure about that and to know how to set up it.
There are many ways in which you can achieve this. From the SQL*XL worksheet function SQLQueryValue that return only one column, to recording a macro that will run a query and put the results somewhere for you.
However I think that the following feature is best suitable to your solution. It requires no programming and setup at all.  
I suggest you use the SQL Favorites option.
Connect to the database and type the following query into the SQL dialog:
accept my_empno prompt 'Enter the employee number:';
select * from emp where empno = &my_empno;

The first command will display a prompt to enter an employee number. The second command will get the data using this parameter. Note that you can also use text parameters like this:
accept my_ename prompt 'Enter the employee name:';
select * from emp where ename = '&my_ename';

Note the single quotes around the parameter &my_ename. The variables are called substitution variables and are just string replaced into the SQL text.
Now you have run the statements, select the Add Favorite option in the SQL Favorites dropdown on the SQL*XL toolbar. You will see your statements are already filled in and you just need to give it a friendly name. You could name it Get Employee.
When you press OK you will see that the Get Employee option is now added to the SQL Favorites list. Note that when you get many of these favorites you can use the Organise option to group them together is sub menus.
When you select your favorite from the list, SQL*XL will open the SQL dialog populating it with the SQL text.
To share these files with your colleages, just look for the SQL files with the Favorite names on your hard disk. Copy the files to the SQL Favorites folder on the PC of your colleage and it will appear in their SQL Favorites list too.
When you select the Get Employee item from the Favorites list, SQL*XL will display the SQL dialog with the commands prepopulated. You just need to run it.
See also:
Configure parameters through commands
How to use parameters in SQL
Back to top
« Last Edit: 02.06.06 at 08:08:47 by Gerrit-Jan Linker »  

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