using column notaition in SQL

LPrendergast Verified User: Posts: 14; Joined: Tue May 26, 2009 8:54 am

by LPrendergast » Fri Jun 05, 2009 2:49 pm

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.

Re: using column notaition in SQL

Support_Sai

by Support_Sai » Sun Jun 07, 2009 9:34 am

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:

Code: Select all

<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)) || &apos; &apos; || 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>

The above project reads persons data from a CSV file as shown below:

Code: Select all

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

The data from the CSV file is inserted into a database table with the following columns:

Code: Select all

Field Name   Type/Size
PERSON_ID    INTEGER
FNAME        CHAR(30)
LNAME        CHAR(30)
NAME         VARCHAR(61)
ADDRESS      VARCHAR(255)
ZIP          CHAR(10)

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.

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

	<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" />

The above parameter mapping hints GoAnywhere to:

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.

As you might have already noticed, we did not use the city and state data that was available in the input CSV file.

Re: using column notaition in SQL

LPrendergast Verified User: Posts: 14; Joined: Tue May 26, 2009 8:54 am

by LPrendergast » Tue Jun 09, 2009 11:40 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.