Defining an Automation Variable that shows the previous month

Toad Data Point
1

I’m using Toad Data Point 3.2.0.916.

I have setup a Automation Script that runs my report and assigns a variable to the beginning of the file name in the Date(‘yyyy’-_mm’) format. It shows that date as 2013_10. Since I’m running my date for the previous month I would like to have the variable show as 2013_09. Is that possible in Toad Datapoint?

2

I’m not sure I correctly understand what you are trying to do. Can you please provide more details?

Thanks,

Igor.

3

Hi Igor,

I would like to automate a report that I run monthly.

The report is run during the 1st week of each month and it shows information for the previous month. Since we are now in October 2013, the report would show data for Septemeber 2013. I would name the report 2013_09_PO20_BB. I’m trying to use a variable as part of my automate script so that I can have the newly created file be named appropriately. I’ve gotten the structure of the date format correct, but the variable shows the current year and month (2013_10). I would like to know if it is possible to have the variable show the previous month (2013_09) in the format that I’ve described.

Gibran

4

If I understand you correctly, you need a file suffix that should contain a date in a specific format (only year and month) and that date should represent a previous month of the current date.

Okay, it’s a bit tricky in TDP 3.2 but is still doable. Use VBA functions for this. For some reason Format() is not supported so you’ll need to go a longer way. The following expression returns year and month part of a date which is one months earlier in yyyy_dd format:

CStr(Year(DateAdd(“m”, -1, Now))) + “_” + String(2 - Len(CStr(Month(DateAdd(“m”, -1, Now)))), “0”) + CStr(Month(DateAdd(“m”, -1, Now)))

Let’s say we put this as a value of some variable SomeVar. In this case something like

#SomeVar#_PO20_BB

should produce what you need. See an example in the attached script. This script uses connection to Toad Sample Database.

[View:~/cfs-file.ashx/__key/communityserver-discussions-components-files/36/Script_5F00_yyyy_5F00_dd.tas:550:0]

5

Hi Igor

I am Gibran’s co-worker and I have version 3.3 version of TDA. I was testing his script and it failed on the variable. When I went into the variable and hit test it did not error but it did not bring back the correct answer. It just repeated the formula below. What has changed in 3.3? Please help.

Thanks

Liz

6

Hi,

Variables were redesigned in 3.3 and with 3.3 this problem is much easier to address. Try the following formula for SomeVar variable value:

To_char(Add_months(Current_date(), -1), ‘YYYY_MM’)

Igor.

7

Why would this not work in 3.2? I know I used To_Char(Add_Months to set variables in 3.1 and I think they ported over to 3.2. When you drag in the Set Variables just click on the SQL button and type in Select To_Char(Add_Months(sysdate, -1), ‘YYYY_MM’) as LastMonth From Dual. I have seen some quirks where sometimes this stopped working (not sure why because in other jobs it continues to work) but when I saved the SQL to a file and then linked to the file instead of just typing it in then it worked again.

8

Greg,

Of course, this should work in 3.2, 3.3, etc. But I think you’re talking about different scenario. In your example it’s a SQL variable and SQL statement is to be run on server. To_Char(), Add_Month(), etc are all Oracle functions. At the other hand, I was explaining how to calculate the same thing locally on TDP side. Variables along with internal TDP functions were redesigned in 3.3. To_Char(), Add_Months() and Current_Date() in my example are NOT Oracle functions. They are built-in TDP functions that just mimic Oracle functions behavior. In my example the variable is of a string-type, not SQL-type. Its value is calculated w/o accessing any SQL server.

Igor.