using column notaition in SQL
Post any question you may have in regards to GoAnywhere Director and let our talented support staff and other users assist you.
3 posts
Page 1 of 1
- Verified User
- Posts: 14
- Joined: Tue May 26, 2009 8:54 am
Is there a way to use column notation (${DATA[1]}) in side a query, instead of paremeter markers? I have an sql update that uses code as so:
SPACE(4 - LENGTH(TRIM(CCSCO1)) ) || TRIM(CCSCO1)
This doesn't seem to work in GA as the markers are applied sequentially, and I cant seem make it work. I have been able to work around this by first updating the table and then applying an update query, but when in production it will hitting about 200,000 to 300, 000 rows and for performance reasons I'd like to do it in a single pass.
SPACE(4 - LENGTH(TRIM(CCSCO1)) ) || TRIM(CCSCO1)
This doesn't seem to work in GA as the markers are applied sequentially, and I cant seem make it work. I have been able to work around this by first updating the table and then applying an update query, but when in production it will hitting about 200,000 to 300, 000 rows and for performance reasons I'd like to do it in a single pass.
-
Support_Sai
The SQL Task does not support the notion you have mentioned (e.g. $DATA[1]}), however, it is possible to map columns in a different order than the default (sequential) order. This can be achieved by defining one or more parameters under the query element of the task. By defining parameters, you can map columns of input row set in any order you like as well as map a column in the input rowset to more than one parameter. Parameter mapping also allows you to skip one or more columns of the input rowset.
Below is a sample project to demonstrate how parameter mapping can be done in the SQL task:
The insert Query used in the project is -
insert into wpsai.persons(person_id, name, fname, lname, address, zip) values(?, cast(? as varchar(30)) || ' ' || cast(? as varchar(30)), ?, ?, ?, ?)
The above query populates the name field by concatenating the first name, followed by a white space and last name. Then it also populates the normal first name and last name fields. This is done by defining a custom parameter mapping.
Below is a sample project to demonstrate how parameter mapping can be done in the SQL task:
Code: Select all
The above project reads persons data from a CSV file as shown below: <project name="SQL Task Parameter Mapping" mainModule="Main" version="1.0">
<description></description>
<module name="Main">
<readCSV inputFile="C:\Users\spullabhotla\Desktop\persons.csv" outputRowSetVariable="data" />
<sql label="Connect to DB" resourceId="Production">
<query inputRowSetVariable="${data}">
<statement>insert into wpsai.persons(person_id, name, fname, lname, address, zip) values(?, cast(? as varchar(30)) || ' ' || cast(? as varchar(30)), ?, ?, ?, ?)</statement>
<param index="1" type="INTEGER" mapFrom="1" />
<param index="2" type="CHAR" mapFrom="2" />
<param index="3" type="CHAR" mapFrom="3" />
<param index="4" type="VARCHAR" mapFrom="2" />
<param index="5" type="CHAR" mapFrom="3" />
<param index="6" mapFrom="4" />
<param index="7" mapFrom="5" />
</query>
</sql>
</module>
</project>
Code: Select all
The data from the CSV file is inserted into a database table with the following columns: 1001,John,Doe,1 Main St.,Omaha,NE,68135
1002,John,Smith,3820 Dodge St.,Omaha,NE,68125
1003,Cindy,Clark,7000 S 100th PLZ,Omaha,NE,68128
Code: Select all
The table above has a NAME filed to hold the concatenated value of first name and last name. Also, the table does not have city and state columns. Field Name Type/Size
PERSON_ID INTEGER
FNAME CHAR(30)
LNAME CHAR(30)
NAME VARCHAR(61)
ADDRESS VARCHAR(255)
ZIP CHAR(10)
The insert Query used in the project is -
insert into wpsai.persons(person_id, name, fname, lname, address, zip) values(?, cast(? as varchar(30)) || ' ' || cast(? as varchar(30)), ?, ?, ?, ?)
The above query populates the name field by concatenating the first name, followed by a white space and last name. Then it also populates the normal first name and last name fields. This is done by defining a custom parameter mapping.
Code: Select all
The above parameter mapping hints GoAnywhere to: <param index="1" type="INTEGER" mapFrom="1" />
<param index="2" type="CHAR" mapFrom="2" />
<param index="3" type="CHAR" mapFrom="3" />
<param index="4" type="VARCHAR" mapFrom="2" />
<param index="5" type="CHAR" mapFrom="3" />
<param index="6" mapFrom="4" />
<param index="7" mapFrom="5" />
- Map 1st parameter (?) in the query from 1st column of the input rowset.
- Map 2nd parameter (?) in the query from 2nd column of the input rowset.
- Map 3rd parameter (?) in the query from 3rd column of the input rowset.
- Map 4th parameter (?) in the query again from 2nd column of the input rowset.
- Map 5th parameter (?) in the query again from 3rd column of the input rowset.
- Map 6th parameter (?) in the query from 4th column of the input rowset.
- Map 7th parameter (?) in the query from 5th column of the input rowset.
- Verified User
- Posts: 14
- Joined: Tue May 26, 2009 8:54 am
Thanks Sai, I dont know how missed that in the manuals. I'll try this as soon as I get a chance and let you know the outcome.
3 posts
Page 1 of 1