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.
7 posts Page 1 of 1

progers

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

Post by 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

Support_Rick

Support Specialist
Posts: 590
Joined: Tue Jul 17, 2012 2:12 pm
Location: Phoenix, AZ

Post by 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
Lead Solutions Consultant
(402) 944.4242
(800) 949-4696

progers

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

Post by 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

Support_Rick

Support Specialist
Posts: 590
Joined: Tue Jul 17, 2012 2:12 pm
Location: Phoenix, AZ

Post by 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 [email protected] with a Subject of **FORUM** Attention Rick, and I'll help you get it working.
Rick Elliott
Lead Solutions Consultant
(402) 944.4242
(800) 949-4696

progers

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

Post by 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

Post by 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

KayGanda05

Posts: 2
Joined: Thu Jun 30, 2016 7:33 pm

Post by KayGanda05 » Wed Aug 17, 2016 10:04 pm
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>
7 posts Page 1 of 1