GoAnywhere Forum

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

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!

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

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 [email protected] with a Subject of **FORUM** Attention Rick, and I'll help you get it working.

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

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

Hi guys,
I would like to ask for your help
here is the problem
*
Initial Record Counts
====================================================
JDA Vendor Master Records : com.linoma.dpa.rowset.DatabaseRowSet@624e624e
JDA Vendor Detail Records : com.linoma.dpa.rowset.DatabaseRowSet@71327132

JBA Vendor Master Records : com.linoma.dpa.rowset.DatabaseRowSet@1c6b1c6b
JBA Vendor Detail Records : com.linoma.dpa.rowset.DatabaseRowSet@36983698


Question? How can I view the records result, please help.

Here is my xml
<project name="APSUPP Extraction" mainModule="Main" version="2.0" logLevel="verbose">

<module name="Main">

<createWorkspace version="1.0" />


<sql label="SQL : Connect to JDA" resourceId="IBMDB" version="1.0">
<query label="Variable : APSUPP COUNT" outputVariable="JDA_VNDR_HDR_PRE">
<statement>select count(1) as JDA_VNDR_HDR_PRE from MM4R4LIB.APSUPP</statement>
</query>
<query label="Variable : APXSUPP COUNT" outputVariable="JDA_VNDR_DTL_PRE">
<statement>select count(1) as JDA_VNDR_DTL_PRE from MM4R4LIB.APXSUPP</statement>
</query>


<sql label="MySQL : Connect to JDA" resourceId="MySQL SPLAN on Atlas" autoCommit="true" version="1.0">
<query label="Variable : JBA APSUPP COUNT" outputVariable="JBA_VNDR_HDR_PRE">
<statement>select count(1) as JBA_VNDR_HDR_PRE from jda.apsupp</statement>
</query>
<query label="Variable : JBA APXSUPP COUNT" outputVariable="JBA_VNDR_DTL_PRE">
<statement>select count(1) as JBA_VNDR_DTL_PRE from jda.apxsupp</statement>
</query>
</sql>

<sendEmail label="Email" resourceId="l" toList="" version="2.0">
<from address="system_admin" name="System Admin" />
<subject>
<![CDATA[${Concat('Extract APSUPP - ',CurrentDate())}]]>
</subject>
<message>
<![CDATA[Hi,

This is a system generated message. Please do not reply on this e-mail account.

Initial Record Counts
====================================================
JDA Vendor Master Records : ${JDA_VNDR_HDR_PRE}
JDA Vendor Detail Records : ${JDA_VNDR_DTL_PRE}

Atlas Vendor Master Records : ${JBA_VNDR_HDR_PRE}
Atlas Vendor Detail Records : ${JBA_VNDR_DTL_PRE}


Post Process Record Count
====================================================
Atlas Vendor Master Records : ${JBA_VNDR_HDR_POST}
Atlas Vendor Detail Records : ${JBA_VNDR_DTL_POST}


]]>
</message>
</sendEmail>


<deleteWorkspace version="1.0" />

</module>

<description>APSUPP Extraction
From JDA to ATLAS.JDA Database</description>
</project>