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
SQLQueryValue as Array formula (Read 12532 times)
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
SQLQueryValue as Array formula
31.10.06 at 11:36:10
 
SQLQueryValue as Array formula
 
In SQL*XL 4.1.4 the SQLQueryValue function is recoded as an array formula. With the SQLQueryValue function you can bind a database value to a cell. Now that it supports arrays as well you can select a range of cells and enter the function. The rows and columns will be populated in the cells.
 
How does it work?
 
Select a range of cells in the spreadsheet. For example A1..D12.
Use the SQL*XL menu to insert a new function: SQLQueryValue
Enter a SQL statement. For example: select * from emp
Close the dialogs
 
Now you will see that SQL*XL displays the first value (empno) of the first row in emp.
 
Now, let's transform it into the array function. If it is ok still all the cells A1..D12 are selected.
Put the cursor in the formula bar where you see the SQLQueryValue formula
Press Ctrl-Shift-Enter. Excel will display curly brackets around the formula {...}
Now you should see that SQL*XL displays the values for all the cells you selected, showing you more of the table.
Back to top
 
 

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




Posts: 75
Re: SQLQueryValue as Array formula
Reply #1 - 16.11.06 at 07:34:20
 
SQLQueryValue will return a horizontal array. Rows are rows and columns are columns.
 
Sometimes you may want to display a row vertically instead of horizontally - rows as columns and columns as rows. You can use the Excel transpose function to do this.
 
Suppose you have this:
Select A1Cheesy1
Type the following formula into the Excel formula toolbar:
  =SQLQueryValue("select empno, ename, sal, deptno from emp")
Then press Ctrl-Shift-Enter to make it an array formula.
 
If you don't want to display this data horizontally but vertically instead, simply selecting A1:A4 does not work. Only the value in A1 will show and the A2 will show empno of row2, etc.  
 
To be able to transpose the array you can use Excel's transpose function:
Select A1:A4
Type the following formula into Excel's formula toolbar:
=Transpose( SQLQueryValue("select empno, ename, sal, deptno from emp") )
Then press Ctrl-Shift-Enter to make it an array formula.
Back to top
 
 

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




Posts: 75
Re: SQLQueryValue as Array formula
Reply #2 - 17.11.06 at 11:57:00
 
In foreign language versions of Excel the transpose function may be called differently. In the Dutch language (The Netherlands) the transpose function is called Transponeren.
Back to top
 
 

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


I Love SQL*XL

Posts: 2
Re: SQLQueryValue as Array formula
Reply #3 - 28.02.07 at 10:43:20
 
Quote from Gerrit-Jan Linker on 31.10.06 at 11:36:10:
[b][size=16]Select a range of cells in the spreadsheet...Now you should see that SQL*XL displays the values for all the cells you selected, showing you more of the table.
I tried this, but every field displays the value of the first column of the select.
Back to top
 
 
Email   IP Logged
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Re: SQLQueryValue as Array formula
Reply #4 - 28.02.07 at 21:26:07
 
Entering an array formula in Excel is a little tricky. However when you get the hang of it it is not too bad. Key is that  you need to select multiple cells at the start. E.g. A1..D4. Then type the formula in the formula pane just below the Excel toolbar. E.g. type =SQLQueryValue("select * from emp")
 
When you would just enter the formula to go into 1 cell you would press the enter key to put the formula in the cell. You would then look at the resulting value which for the above value would mean the value in column 1 in row 1.
 
When you are entering an array formula you don't press just enter in the formula pane. You need to press Ctrl-Shift-Enter. Excel will display your formula with curly brackets around it: { =SQLQuery..... }
This is a sign you entered an array formula. Bear in mind that you need to first select the range of cells before you can enter the array formula. Press the Ctrl-Shift-Enter when done and you will see that the data of the table is shown in your selected range. In A1 the value in row 1 and column 1, in B2 the value for column 2 in row 1, etc.
 
I have recently added some examples of dashboards as I call them. Excel sheets where I have used these SQLQueryValue statements to get data from the database. They also contain array formulas. Please have a look at them.
http://www.oraxcel.com/cgi-bin/yabb2/YaBB.pl?num=1172472828
 
You can also lookup array formulas in the Excel help to get you a bit more familiar with array formulas.
Back to top
 
« Last Edit: 01.03.07 at 17:55:44 by Gerrit-Jan Linker »  

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


I Love SQL*XL

Posts: 2
Re: SQLQueryValue as Array formula
Reply #5 - 01.03.07 at 09:19:14
 
Yes, I've downloaded that example, thank you.
 
I do see the curly brackets. In Excel 2003, the keystroke combo seems to be Ctrl-Shift-Enter, btw. Ctrl-Alt-Enter doesn't appear to do anything.
 
All the cells do indeed get filled from the query, the problem is that while my select looks like  
 
=SQLQueryValue("Select IMAO_NR, STUDENT_NR, VOLLEDIGE_NAAM, ACHTERNAAM, VOORNAMEN, VOORVOEGSELS, ROEPNAAM, VOORLETTERS, EMAILADRES, GESLACHT, BANK, GIRO, TELEFOON, GSMNR, NATIONALITEIT, ADRES, PLAATS, LAND, GEBOORTEDATUM, GEBOORTEPLAATS, GEBOORTELAND, OUDERS_NAAM, OUDERS_VOORLETTERS, OUDERS_VOORVOEGSELS, OUDERS_ADRES, OUDERS_PLAATS, OUDERS_POSTCODE, OUDERS_TELEFOON, START_STUDIE, EINDE_STUDIE, OPLEIDINGEN From MY_VIEW_INFO Where STUDENT_NR = " & A21)
 
all selected columns get filled with IMAO_NR.
Back to top
 
« Last Edit: 01.03.07 at 15:47:45 by emilianoheyns »  
Email   IP Logged
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Re: SQLQueryValue as Array formula
Reply #6 - 02.03.07 at 11:00:34
 
Sorry about the confusion with the keys. It is Ctrl-Shift-Enter.
 
Perhaps you run a version of SQL*XL prior to the array implementation of the SQLQueryValues function. Can you check please? The version that implements the array formula version of SQLQueryValue is 4.1.4.
 
See our release log:
http://www.oraxcel.com/cgi-bin/yabb2/YaBB.pl?num=1130772637
Back to top
 
 

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