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
read values stored in excel cells to modify the s (Read 2628 times)
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
read values stored in excel cells to modify the s
04.09.06 at 20:46:42
 
Read values stored in excel cells to modify the sql query.
 
Brian asked the following question:
 
Question:
I need a tool in Excel which can read values stored in excel cells to modify the sql query.
 
So I would like to issue a command such as  
 
Select * from orders where month= 'July'
 
The end user would be able to modify the result by changing the value of an excel cell from July to August., but woul dnot have to edit the sql query itself.
 
Can SQL*XL do this?
 
Answer:
Yes it can. There are various ways you can achieve this:

  • Through a bind variable:
    Use cells as bind variables. Example:
    select * from mytable where month = :A1;
  • Through a substitution variable and a prompt so the user can type it in:
    accept mymonth prompt 'Enter the month:';
    select * from mytable where month = &mymonth;

  • Trough dynamic SQL.
    Type the following formula in a cell to build the SQL text:
    =concatenate("select * from mytable where month = '", A1 , "';")
    Then select the cell and open the SQL dialog. Choose to use the SQL from the worksheet.
Back to top
 
« Last Edit: 04.09.06 at 21:11:59 by Gerrit-Jan Linker »  

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