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
Queries that allow input from the user (Read 2518 times)
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Queries that allow input from the user
05.05.06 at 17:11:12
 
Queries that allow input from the user
 
Ronald from the USA asked the following question.
 
Question:
Can I create a query that would allow input from the user.  For example if the query was looking for information on a city, be able to pop up a box asking for city name as the query runs.  I know that is not part of SQL as such but it could be added as a VB type script that could be added. Looking around online I see a lot of people looking for that.
 
Answer:
This is already part of SQL*XL. SQL*XL allows most SQL*Plus commands. Through these commands you can do a lot of things you cannot do with standard SQL.
 
To use the SQL*Plus commands to do what you want I suggest you use the accept command. Consider the following SQL
Code:
accept mycity prompt 'Enter a city:";
select * from my orders where city = '&mycity'; 


 
Note that in the above code mycity is a substitution variable and that these variables are simply substituted into your SQL code. That is why you need th single quotes around the &mycity variable in the second statement.
 
Running this code will pop up a dialog asking for the city of choice. If you want to set a default value use the define command:
Code:
define mycity = 'NEW YORK';
accept mycity prompt 'Enter a city:";
select * from my orders where city = '&mycity'; 


 
I think you will also find the following topics useful to read:
How to use parameters in SQL:  
http://www.oraxcel.com/cgi-bin/yabb2/YaBB.pl?num=1130253885
Configure parameters through commands:
http://www.oraxcel.com/cgi-bin/yabb2/YaBB.pl?num=1138809673
Print parameters:
http://www.oraxcel.com/cgi-bin/yabb2/YaBB.pl?num=1138895880
 
 
Back to top
 
 

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