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 3) set variables 4) execute queries with "F5" 5) export each query result to a tab in a file labelled "car-model_str-user_start_date_user_end_date.xls" 6) 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;