|
The Query Analzyer supports parameterized
scripts.
From the query toolbar the user may enable/disable the
parameterized scripts. By enabling them, the user may have scripts
with defined parameters so that Aqua Data Studio will prompt for
the values of the parameters before executing the script.
Parameters are defined by a leading "&".
- Simple parameter query -
Example: SELECT * FROM DEPARTMENTS WHERE
DEPARTMENT_ID = &id
- Parameter query in a quoted string
Example: SELECT * FROM DEPARTMENTS WHERE
DEPARTMENT_NAME = ’\&name’
Parameters are also supported in server side
comments. Client side and server side comments may be defined in File->Options->Scripts
for each database. If a parameter is defined in a client side
comment it will be ignored. Parameters in server side comments will
be prompted and replaced.
Parameters also support default values. Default values may be
defined by appending an "=" and the value after the name of the
parameter. Default values may be single quoted to support values
with spaces and special characters.
- Simple parameter query -
Example: SELECT * FROM DEPARTMENTS WHERE
DEPARTMENT_ID = &id=10
- Parameter query in a quoted string
Example: SELECT * FROM DEPARTMENTS WHERE
DEPARTMENT_NAME = ’\&name=’Sales
Dept’’
Date and time expressions in parameter values
scripts.
This feature allows a user to specify a default
value of a parameter to be a date expression.
File->Options:General:"Parameterized Scripts" has the
option to specify the tags for the date and time expressions and
whether to prompt
- File->Options:General:"Parameterized
Scripts"
- DateTime Tag: dt
- Date Tag:
- Time Tag:
- DateTime at Midnight Tag
- Prompt for parameter values
- Date & Time Expressions
[Configurable in File->Options:Parameterized Scripts]
Current Units
-------------------
d = current date
t = current
dt = current date time
dm = current date at midnight
Time Units
--------------------
y Year
M Month
d day
h Hour
m Minute
s Second
S Millisecond
Examples:
{d-1y} = current date - 1 year
{dt-2h-3m-8s-340S} = current date and time minus 2 hours, minus 3
minutes, minus 8 seconds, minus 340 milliseconds
{dm+6h} = current date at midnight, plus 6 hours ... basically
today at 6am.
- SQL Example:
[This query will return a list of orders that have occurred in the
last month]
SELECT * FROM ORDERS WHERE ORDER_DATE >
&myparam={dt-1M}
select * from orders
where orderdate > &myparam={dt-10y-4M-18d} |
