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
Evaluating for dynamic SQL and result sets (Read 6833 times)
hermanlaw
YaBB Newbies
*


I Love SQL*XL

Posts: 1
Evaluating for dynamic SQL and result sets
26.06.08 at 01:56:22
 
SQLQueryValue provides the ability to dynamically generate SQL and return a single item to a singe cell using  
SQL Editor can generate and store a SQL query from excel cells and return multi-rows and multi-columns
 
The problem we found with SQL editor is that the SQL is statically stored.
The SQL does not change, when the cells used to initially compose the SQL are changed.
 
The problem we found with SQLQueryValue is that it only returns one column from one row in the database to a cell.
It doesn't seem to be able to return multi-rows and multi-columns
 
We are interested in a hybrid of these two functions
where SQL is dynamically generate using Excel cells and output is sent to
multi-rows and multi-columns of an Excel sheet.
 
Back to top
 
 
Email   IP Logged
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Re: Evaluating for dynamic SQL and result sets
Reply #1 - 26.06.08 at 08:21:02
 
Thank you for your question. Both methods of using SQL in SQL*XL are not limited in the way you claim.
 
SQLQueryValue function
This function can be used as a function in one cell but also as an array function. When used as a cell function you can only retrieve one value being the first column of the select statement and by default it returns the first row although you can set a parameter to get another row.
 
When used as a array function you can see both multiple columns as well as multiple rows. Array functions are a feature of Excel. If you have not used it before it may be helpful to consult the Excel help function to learn how to use it.
Basically you can assign one formula to a block of cells. If the formula returns a array - and basically this is what the SQLQueryValue function does when it returns multiple columns and multiple rows of data - it will show the portion of it that fits in the block of cells you selected.
 
You can try this by doing the following. Select A1 to D10. You have selected 4 columns and 10 rows. Now enter a query: select * from mytable. Instead of just pressing Enter, hold down the Ctrl and Shift key and then press Enter. Excel creates the function with {} brackets around it to denote it is an array function.
If mytable contains 100,000 rows and 10 columns the first 4 columns and the first 10 rows will be shown in the cells.
 
For more information on the SQLQueryValue function and how to use it as an array function please read:
SQLQueryValue as Array formula  
http://www.oraxcel.com/cgi-bin/yabb2/YaBB.pl?num=1162290970
 
SQL editor
SQL typed into the SQL editor is used as static SQL. However with various techniques it can be made dynamic.
 
  • Parameters
    You can use bind or substitution variables / parameters to make the SQL more dynamic. Examples are: select * from emp where empno = :A1;   select * from employees where name like '&name%';
    As you can see both bind and substitution variables work. You can use Excel cells as parameters. SQL*XL will automatically prompt for values but you can also define them and prompt for values yourself using the  accept command:
    accept name prompt 'Enter name:'; select * from emp where name = '&name';
     
    Please read:
    Parameters
    http://www.oraxcel.com/projects/sqlxl/help/techniques/parameters.html
    Configure parameters through commands
    http://www.oraxcel.com/cgi-bin/yabb2/YaBB.pl?num=1138809673
    Parameters dialog
    http://www.oraxcel.com/projects/sqlxl/help/dialogs/parameters/index.html
     
  • Build SQL up in cells
    SQL can be taken from a range of cells that you select. Suppose you type the following information into Excel:
    A1: select *
    A2: from
    B2: emp
    Select the block of cells A1:B2 and open the SQL dialog. If you select more than one cell SQL*XL thinks you may use SQL from the worksheet and sets the source option that way (you can do it manally too and use the browse field to select the cells with the SQL). SQL*XL will read the cells from left to right and then from top to bottom inserting a space between the values of the cells. Set the option that all cells make one statement and run the SQL.
     
    To make this dynamic You can use Excel formulas as you do  normally to display text or other values in cells. So I could have typed in B2: =concatenate("emp where empno = ",D12) and typed a value in D12, e.g. 1000. Now the function result shows in B2: "emp where empno = 1000".  SQL*XL will use the displayed text to make the SQL.
     
  • Record macros
    The most flexibility you get over the process when you start recording macros. I suggest you get familiar with this as it will allow you to become very flexible. Macros are recorded in the VB language and that is just like English. It reads easily and with very minor Visual Basic skills you can add a variable to make your code more dynamic.  
     
    Ensure the macro recording is enables (see program preferences) and start the macro recorder in Tools - Macros. Accept all the settings as defaults. Now run a query. Just type the SQL into the SQL dialog and run it. After the fetching is complete stop the macro recorder. Have a look at the recorded macro. Go to tools - macros and enter the VB environment. Your code is in de modules node in module1. You see a lot of statements but when you read through you see a line with your SQL statement. Something like:
    SQLXL.SQL.settext "select * from emp"
     
    Now you can use Visual Basic to be dynamic with your SQL. There is a subforum with discussions about macros.
    http://www.oraxcel.com/cgi-bin/yabb2/YaBB.pl?board=sqlxlvba
Back to top
 
« Last Edit: 26.06.08 at 08:40:23 by Gerrit-Jan Linker »  

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