Toad World® Forums

Prompt for variable

What is the syntax and where must it be added to be prompted for variables i.e. Start Date & End Date to then run the report with those limitations?

Bind variables ought to work. e.g. ...
SELECT * FROM WHERE DATECOL = :dt

... where :dt is the bind variable. You may need to use a cast or a date-formatting function, as whatever is inputted for the bind variable is likely treated as a string input type. e.g. ...
SELECT * FROM WHERE DATECOL = FORMAT (:dt, 'MM-dd-yy')

... or similar date conversion function call.

Can you provide a simple example where this actually works in Toad?

Here is from Crystal Reports where I have defied the variables {?Start Date} and {?End Date} as parameters of the type "Date" and with a list of values "static" and then select as :

{ap_checks.a_check_date}>={?Start Date} and
{ap_checks.a_check_date}<={?End Date}


I have tried the following in Toad:

SELECT a_check_date
FROM ap_checks
WHERE (a_ap_check_date between prompt
('2007-01-01','yyyy-mm-dd')
AND prompt
('2007-12-31','yyyy-mm-dd'))

I error out on prompt, but think that is a check box somewhere in toad for this type of thing?
Bruce