Can a column in a rowset be changed?

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

Can a column in a rowset be changed?

Postby rutledge_tx76 » Wed Jan 04, 2012 4:31 pm

Vendor wants a name's suffix to be one character. i.e. JR or JR. becomes J, I is 1, II is 2 etc. I thought of creating a table to do the conversion but because of file sizes (over 600,000 records) it is not very practical. So I looked at the set variable task to see if it would but can't see how do it. Can anybody help.
rutledge_tx76
 
Posts: 1
Joined: Fri Dec 11, 2009 9:22 am

Re: Can a column in a rowset be changed?

Postby Support_Duane » Tue Feb 14, 2012 2:39 pm

The only way for GoAnywhere Director to change one (or more) columns in a rowset, would be to process the file one row at a time. This would not be a speedy process.
Support_Duane
Support Specialist
 
Posts: 66
Joined: Thu Mar 05, 2009 3:49 pm

Re: Can a column in a rowset be changed?

Postby RElliott63 » Tue Feb 28, 2012 11:27 am

If you are retrieving your dataset from a database (ie, DB2, MSSQL, etc) and are using an SQL Function to retrieve that data, you can do the following:

Code: Select all
<setVariable label="Set sqlError" name="sqlError" value="0" version="1.0" />
<sql label="Connect to Server" resourceId="${ Server }" version="1.0" onError="setVariable:sqlError=1">
    <query label="Get Doc Recs" outputVariable="DocRecs" whenNoDataFound="error">
        <statement>
Select FirstName, LastName,
          Case When upper( Suffix ) like 'JR%'     Then 'J'
               When upper( Suffix ) like 'I'       Then '1'
               When upper( Suffix ) like 'II'      Then '2'
               When upper( Suffix ) like 'SR%'     Then 'S'
               Else ' '
          End as Suffix,
          Address1, Address2, City, State, Zip, Phone
  From MyLib.MyFile
Where MyField = 'MyValue'
        </statement>
    </query>
</sql>


This assumes that the Suffix is a separate field value.

-Rick
RElliott63
 
Posts: 14
Joined: Thu Jul 01, 2010 10:42 am


Return to Community Forum

Who is online

Users browsing this forum: No registered users and 1 guest