soundex field name in schema

datisaq · Post by **datisaq** » Wed Feb 08, 2012 9:49 am

we are using a generic job for loading the data from sequential file to a teradara(using RCP and schema file).

In our schema file we are having a field as "soundex" which has to be mapped to the target teradata table "soundex".

we can able to create the table in teradata by putting double quotes around soundex field in the ddl since soundex is a keyword/function in teradata.

Now the table got created but we cannot able to load the data because while insert into table datastage is throwing error as keyword soundex found in the generated insert statement.

As per our agreement we cannot rename the field name so how can we handle this??? Anything we can add escape sequence in schema file for soundex field...

ray.wurlod · Post by **ray.wurlod** » Wed Feb 08, 2012 6:36 pm

Can you use quoted identifiers in Teradata? That is, "SOUNDEX" is a column name, whereas SOUNDEX is the name of a function.

datisaq · Post by **datisaq** » Sun Feb 12, 2012 12:25 am

We have alreadt enable quote identifiers option in the teradata connector stage(In Datastage 8.5) its not working since we have "SOUNDEX" in the value statement of the insert query(ORACHESTRATE.SOUNDEX) which won't be in quotes right, there its showing error.

Can we have something like "REDEFINE/RENAME" clause as in cobol schema definition, in our sequential file schema file??

qt_ky · Post by **qt_ky** » Sun Feb 12, 2012 7:37 am

You may be able to find a workaround but since it is a keyword, consider it may be the first workaround of many more to come on the same issue. I would guess you didn't know it was a keyword when you made the agreement. Acknowledge that and revise the agreement or raise it as an issue so you can implement a solution, such as renaming the column to SNDX or any name that's not a keyword.

pandeesh · Post by **pandeesh** » Sun Feb 12, 2012 7:50 am

As per my understanding, you are able to create the table with that keyword(if it's ORACLE, you can't do this also).
One way you can try is:
Have the table created:

a)In the before job routine, change the column name to some other name "soundex1"

B)have the mapping for soundex1 and load the data into soundex1

c)In After job subroutine, change the column name "soundex1" to "Soundex".

I dont think,there's any other simpler way.

Thanks