Assign a variable value

Post any question you may have in regards to GoAnywhere Director and let our talented support staff and other users assist you.

Assign a variable value

Postby progers » Mon Feb 10, 2014 1:25 pm

I need to create a project to calculate a yyyymm value from a database. Then need to issue a multiple delete files task with the yyyymm value as part of the filename on a local file system.

I have been able to retrieve the yyyymm string from the database (and write it to a .csv file) but have not been able to put the value of that string into an "include" element of a fileset/wildcardfilter task to delete the multiple files.

Any suggestions on the best approach?

Thanks
Paul
progers
 
Posts: 5
Joined: Mon Feb 10, 2014 10:33 am

Re: Assign a variable value

Postby Support_Rick » Mon Feb 10, 2014 3:17 pm

Paul,

The value needed for filtering will need to be ISO (yyyy-MM-dd).

If you have been able to retrieve the string from your database, then you should be able to manipulate that value into a local Variable ... something like:

setVariable name="cutOffDateISO" value="${ concat( myYear, '-', myMonth, '-', myDay ) }"

Then, you could use ${cutOffDateISO} as the From/To variable (depending on your needs) to select based on that date.

If you have any other questions, please let us know!
Rick Elliott
Sr. Product Specialist
(402) 944.4242
(800) 949-4696
Support_Rick
Support Specialist
 
Posts: 200
Joined: Tue Jul 17, 2012 2:12 pm

Re: Assign a variable value

Postby progers » Tue Feb 11, 2014 11:31 am

Thanks Rick.

I retrieved the date using a SQL task, with a variable in the Output Variable option.

1) When I write the retrieved and formatted date to a text file (using Write CSV task) I see the correct date with format yyyymm which is what I'm expecting.

2) When I assign this value to another variable using the Set Variable task as you suggest and then display (using Print task), I see:
"com.linoma.dpa.rowset.DatabaseRowSet@2ba88010" in the log.

Not sure where the value of the variable is getting lost. Is the SQL task output a rowset that somehow(?) needs to be converted to a single value variable.

fyi - I'm using GAD 4.0

Thanks
Paul
progers
 
Posts: 5
Joined: Mon Feb 10, 2014 10:33 am

Re: Assign a variable value

Postby Support_Rick » Tue Feb 11, 2014 6:19 pm

Paul,

When you select the date from your SQL Table, are you selecting just the Date Value only? Or, is it a rowset of data? (multiple records)

The value you see is the "pointer" the to the rowset that you just created with the SQL Statement.

You will need to set that value to "${MySQLRowset[1]}" to get the actual value assigned to a variable.

If you have issues getting this, please post your project XML and I'll help you tweak it to get the information you're looking for. Otherwise, please email a copy of the XML Project to Support@Linoma.com with a Subject of **FORUM** Attention Rick, and I'll help you get it working.
Rick Elliott
Sr. Product Specialist
(402) 944.4242
(800) 949-4696
Support_Rick
Support Specialist
 
Posts: 200
Joined: Tue Jul 17, 2012 2:12 pm

Re: Assign a variable value

Postby progers » Wed Feb 12, 2014 11:14 am

Thanks Rick - appreciate the help.

It is a rowset being returned from the database (Teradata in this case). I did try adding the [1] to the rowset name and got the following:

Project '/PaulR/TestDeletes' failed. Job number is '1387133248712'.
[8099 - Set Variable PrevMnth] An unexpected error occurred. [Teradata JDBC Driver] [TeraJDBC 13.00.00.07] [Error 1325] [SQLState HY000] Cannot get data value for column 1 because the cursor position is before the first row of the result set. The ResultSet.next method can be used to advance to the first row of the result set. Original expression: LstYrMnth[1].

This is the project XML:

LstYrMnth contains the date string as a rowset.
PrevMnth is the variable I'm trying to assign the value from LstYrMnth.

<project name="TestDeletes" mainModule="Main" version="2.0" logLevel="debug">
<variable name="LstYrMnth" value="YYYYMM" />
<variable name="PrevMnth" value="" />

<module name="Main">

<sql label="Teradata" resourceId="D_BI_USRDB" version="1.0">
<query label="Get LastYrMnth" outputVariable="LstYrMnth" createScrollableRowSet="false">
<statement>SELECT ADD_MONTHS(CURRENT_DATE, -7) (format &apos;yyyymm&apos;) (char(6))</statement>
</query>
</sql>


<setVariable label="Set Variable PrevMnth" name="PrevMnth" value="${LstYrMnth[1]}" version="2.0" />


<print label="Print PrevMnth" version="1.0">
<![CDATA[${PrevMnth}]]>
</print>


<writeCSV label="Store Retrieved Date" inputRowSetVariable="${LstYrMnth}" outputFile="C:\DataWarehouse\SumTotal\Test Data\DateString" whenFileExists="overwrite" version="1.0" />


<delete label="Delete on terapp1" version="1.0" logLevel="debug">
<fileset dir="C:\DataWarehouse\SumTotal\Test Data\" recursive="false">
<wildcardFilter>
<include pattern="DataWarehouseExportFileSumTotal_${PrevMnth}*.txt" />
</wildcardFilter>
</fileset>
</delete>

</module>

</project>

I realise I could use the timestamp function to get a date string relative to current date but may need to use other dates which I can only get from the database.

Thanks again for the help

Regards
Paul
progers
 
Posts: 5
Joined: Mon Feb 10, 2014 10:33 am

Re: Assign a variable value

Postby progers » Thu Feb 13, 2014 10:08 am

Rick

As we discussed I changed the version number on the Set Variable task from 2.0 to 1.0 and the project worked!

The older version doesn't require setting the cursor to the first row.

<setVariable label="Set Variable PrevMnth" name="PrevMnth" value="${LstYrMnth[1]}" version="1.0" />

Thanks again for the help.

Paul
progers
 
Posts: 5
Joined: Mon Feb 10, 2014 10:33 am


Return to Community Forum

Who is online

Users browsing this forum: Bing [Bot] and 1 guest

cron