Multiple query Automation

Toad Edge
1

I’m trying to figure out how to automate set variables, execute multiple queries that contain the variables, store the results in an excel file as tabs in the same file which is named after the values of the variables.

The queries (the first two) are stored as a file "level 1 debug.sql"in a project “car-model-monitoring”.

I think I get how to execute each query separately and export each result to an excel file but I want to set the variables once then execute multiple queries using those variables.

Manually, the steps are:

  1. connect to remote database
  2. open Project Manager
  3. open query file
  4. set variables
  5. execute queries with “F5”
  6. export each query result to a tab in a file labelled
    “car-model_str-user_start_date_user_end_date.xls”
  7. email file to email list

query file with variables yy, mm1, dd1, mm2, dd2, x:
set @user_start_date=‘yy-mm1-dd1’;
set @user_end_date=‘yy-mm2-dd2’;
set @car-model_str=‘A6-FDx%’;

#1-make-of-carallocationtables
SELECT aarowkey, logtimestamp, tripdate, trip_usage.format_fz_trip(tripnum) astripnum, depcity, arrcity, etd, eta, platenum, actype, passenger, crew,cavailflag, comment, status
FROM make-of-carallocationtable a
JOIN platenuminfotable t on (a.platenum = t.plate_num)
WHERE tripdate between @user_start_date and @user_end_date
AND platenum LIKE @car-model_str
ORDER BY platenum, etd desc;

#2-cmlogstable.csv
SELECT *
FROM cmlogstable
WHERE (date(logtimestamp) BETWEEN @user_start_date AND@user_end_date)
and (platenum like @car-model_str)
ORDER BY platenum DESC, logtimestamp DESC;