Modify Stage Column Name Change

lfong · Post by **lfong** » Tue Apr 03, 2007 9:24 am

I am using the modify stage to change a column name that has '-' (Specification = CAUSE_EN = Cause-en). When I run the job, I get the following error:

main_program: Syntax error: Error in "oraread" operator: Error in output redirection: Error in output parameters: Error in modify adapter: Error in binding: Expected '=', got: "-", line 30 [osl\osl.C:135]

I know the modify stage can change the datatype of a column, but can it rename a column name. If this is not possible should I drop and and a new column using the old column name definition.

michaeld · Post by **michaeld** » Tue Apr 03, 2007 9:29 am

Don't need to use a modify stage to change the name of a column. Use the copy stage (or the next stage reading the input). Copy stage gets optimized out of the job when it is compiled so there is no extra overhead.

PS: you can use a modify stage to rename columns, but it functions and rules are different than for other stages.

lfong · Post by **lfong** » Tue Apr 03, 2007 9:45 am

The error is occuring on the read(oraread). I an using the Oracle enterprise stage for the source.

ray.wurlod · Post by **ray.wurlod** » Tue Apr 03, 2007 10:42 pm

Modify stage certainly can change a column name. For example:

Code: Select all

Specification:  newcolumn=oldcolumn

As you note, the problem is reading from Oracle. Are you using direct read or SQL as the extraction method?
There may also be a problem in the Modify stage, but solve the Oracle issue first.

Madhusv · Post by **Madhusv** » Wed Apr 04, 2007 4:44 pm

Datastage does not supports '-' in the field names

lfong · Post by **lfong** » Thu Apr 12, 2007 10:07 am

The error message was pretty self-explanatory as to what the problem is. When I use the modify stage to rename the column, I am getting the same error on the oraread. Is there possibly another solution or should I create a view of the table and rename the column with the problem, or call IBM and create a case.

ray.wurlod · Post by **ray.wurlod** » Thu Apr 12, 2007 3:17 pm

Just put the column name in double quotes in the SELECT statement. Otherwise the "-" character could be interpreted as a request to perform subtraction. And/or you can use an alias column name in the SELECT statement; there's no need to create a view.

us1aslam1us · Post by **us1aslam1us** » Thu Apr 12, 2007 4:19 pm

As mentioned you can use alias name or double quotes or square bracket in the select statement, if it has something to do with the raeding the column in the Oracle Enterprise stage.

lfong · Post by **lfong** » Mon Apr 16, 2007 7:27 am

ray.wurlod wrote:Just put the column name in double quotes in the SELECT statement. Otherwise the "-" character could be interpreted as a request to perform subtraction. And/or you can use an alias column name in the SELECT statement; there's no need to create a view.

Do I have to do anything special with the column name that is imported as part of the table definition. I am still getting the same error. When I look at the OSH output, I still see the '-' in the column name, but I see \'\'column-name\'\' as part of the column name.

us1aslam1us · Post by **us1aslam1us** » Mon Apr 16, 2007 8:46 am

After importing the table definition change the column name to different one or the alias name on the columns tab.