We've just got hold of TDA 2.7 which is proving to be a great piece of kit! I'm slowly finding my way around it, but I'm stuck on the automation side of things where variables are concerned.

I am running the attached query weekly and would like to automate it.

I have two blind variables :fromdate and :todate in the query, which come up as TIMESTAMP variables when running the query manually. The query is run weekly with 4 different date periods

:fromdate :todate
first of month current date
This financial yr(01-04-2010) current date
first of month (previous yr) current date (previous yr)
previous financial yr current date (previous yr)

I've attempted to add the variables option in the Automate section and have tried various options to generate the dates required (either by building an expression or a seperate sql, but I can't seem to get the format right.

I think it might be due to matching the formats of the date?

Can anyone point me in the right direction please??

Why don’t you try to tweak your query a bit by casting both your timestamp columns and bind variables to date? I’m not an Oracle expert but changing this line in your query:

AND (w.actfinish >= :fromdate AND w.actfinish < :todate)

to something like this:

AND (TO_DATE(TO_CHAR(w.actfinish, ‘DD-MM-YYYY HH24-MI-SS’)) >= TO_DATE(:fromdate) AND TO_DATE(TO_CHAR(w.actfinish, ‘DD-MM-YYYY HH24-MI-SS’)) < TO_DATE(:todate))

and then using Date(‘yyyy-MM-dd HH-mm-ss’) function for building condition expression for bind variable seems to solve the problem of matching formats of the date.

Apologies for the delayed response, and thank you very much for your reply. What you’ve written makes sense, and I’ve changed a couple things to get the basics working:

AND w.actfinish >= TO_DATE(:fromdate, ‘yyyy/dd/mm’) AND w.actfinish

I decided the hours, mins, secs were not important, so I’ve removed that part.

I’ve been able to input variables now, and appears to run.

I’m now stuck on using calculations for the variables (e.g. to calculate the first day of the current month) for the :fromdate variable. The :todate is simply the current date, so that is straight forward.

I’ve tried a few methods using SQL, such as:

select truc(sysdate,‘mm’) from dual;

but I cannot get the script to run with this calculated date. No matter how I format the sql, it either sets the variable as

“43.7173913043478” or

“01”/“02”/“2011 00:00:00” or

“1989” as it sees 2011-02-23 as a calculation (2011 minus 2 minus 23)

I don’t know if it’s possible to do the ‘first day of the current month’ calculation using expressions, I’m not familliar with them at all.

Apologies for the delayed response, and thank you very much for your reply. What you’ve written makes sense, and I’ve changed a couple things to get the basics working.

—had to remove sql, it wouldn’t post the rest of the message??–

I decided the hours, mins, secs were not important, so I’ve removed that part.

I’ve been able to input variables now, and appears to run.

I’m now stuck on using calculations for the variables (e.g. to calculate the first day of the current month) for the :fromdate variable. The :todate is simply the current date, so that is straight forward.

I’ve tried a few methods using SQL, such as:

select truc(sysdate,‘mm’) from dual;

but I cannot get the script to run with this calculated date. No matter how I format the sql, it either sets the variable as

“43.7173913043478” or

“01”/“02”/“2011 00:00:00” or

“1989” as it sees 2011-02-23 as a calculation (2011 minus 2 minus 23)

I don’t know if it’s possible to do the ‘first day of the current month’ calculation using expressions, I’m not familliar with them at all.

I’m trying to automate my daily/weekly reports, and was trying to work out the best method to input the variables in my queries (dates).

I was looking too much into setting variables in automation, when all I really need to do is amend the query, so that every time it runs, it picks up the new dates. e.g.

trunc(sysdate, ‘mm’) --for first of month

add_months(trunc(sysdate, ‘mm’),-12) --for this time last year

Apologies if this seems very basic, I’m still pretty new to SQL!