help with blind variables in automation - dates

Toad Data Point
1

Hi all,

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??

Thanks in advance

James

query.jpeg

query2.jpeg
query2.jpeg750×627 46.7 KB

2

Hi James,

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.

Is this what you’re asking for?

Thanks,

Igor.

3

Hi Igor,

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.

Any ideas??

Thanks,

James

Message was edited by: JamesR

Message was edited by: JamesR

4

I can’t get the rest of my message to display??!

Message was edited by: JamesR

5

Hi Igor,

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.

Any ideas??

Thanks,

James

6

I probably don’t understand exactly what you are trying to do, but in TDA, I could go in the query builder and easily build a query like this:

select *
from my_table
where date_column between trunc (sysdate, ‘MM’) and sysdate ;

Which would return all rows with date_column having a value >= beginning of the month and

Below are a few examples of Oracle date functions to find first day of month or last day of month

I don’t know if this will help.

SQL> alter session set nls_date_format = ‘SYYYY/MM/DD HH24:MI:SS’ ;
Session modifiée.

SQL> select sysdate from dual ;
SYSDATE

7

Also, did you know that the Query Builder builds some common date ranges for you? See screenshot.
Debbie
DateRanges2.png

8

Hi,

Many thanks for the replies.

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!

Thanks again to all,

James