ODBC stage - Column name with pound sign (#) not accepted
Moderators: chulett, rschirm, roy
ODBC stage - Column name with pound sign (#) not accepted
We're new with DataStage so please bear with me.
I'm setting up a server job and trying to write to an Oracle table using the ODBC stage. Everything was going good until I tried mapping (via Transformer stage) a value to an Oracle column containing a pound sign (PCLIN#E). When I loaded the table meta data from the Oracle table itself, everything loaded fine including this column name. But, when I went to compile it, I am getting an error due to having this pound sign in the column name. So, I tried removing the # and it compiles fine but obviously errors when it runs since that column doesn't exist. I then tried adding the # back in manually, and DataStage tells me that a column name cannot contain a #.
I did some searching on these forums and found one similar post but it was dealing with a table name with a pound sign. There were a few suggested work arounds (prefix with \ to escape it and putting quotes around it) so I tried them but that didn't work for me with the column name.
This table is a production table and I cannot change the column name.
Is there a solution for this?
- Mark
I'm setting up a server job and trying to write to an Oracle table using the ODBC stage. Everything was going good until I tried mapping (via Transformer stage) a value to an Oracle column containing a pound sign (PCLIN#E). When I loaded the table meta data from the Oracle table itself, everything loaded fine including this column name. But, when I went to compile it, I am getting an error due to having this pound sign in the column name. So, I tried removing the # and it compiles fine but obviously errors when it runs since that column doesn't exist. I then tried adding the # back in manually, and DataStage tells me that a column name cannot contain a #.
I did some searching on these forums and found one similar post but it was dealing with a table name with a pound sign. There were a few suggested work arounds (prefix with \ to escape it and putting quotes around it) so I tried them but that didn't work for me with the column name.
This table is a production table and I cannot change the column name.
Is there a solution for this?
- Mark
Sorry, but the only option that I know works is to have a view created on the production server that aliases the column under a new name.
Kenneth Bland
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
There is something in one of the manuals about these kinds of characters - I've read it quite recently. (As is usual on Saturdays I'm logged in from an airport lounge and can't check the details.) It may have been for DB2 stage. It may have been for parallel jobs. Open the Server Job Devloper's Guide anyway and do a search for "#".
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
One such place would be the Oracle OCI Stage Guide. You've got to enable DS_ENABLE_RESERVED_CHAR_CONVERT and then # is represented by _035_ and $ by _036_ in the metadata. They suggest you do this and then import the metadata so that DataStage can handle the conversion for you.
Don't know if any such functionality exists for ODBC, however.
Don't know if any such functionality exists for ODBC, however.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
'Best' is a relative term.
It would certainly work and Mark has probably already gone and done exactly that. Just trying to discover some alternatives.
Kumar - as an aside and just to be a little anal, you seem to be awfully fond of the little 'rolling eyes' icon. Not sure if you understand the connotations of it. One rolls ones eyes to express exasperation or frustration, typically in response after someone else has said something exceedingly stupid. I doubt you mean it like that.
Of course now, the next response might very well be another visit from Mr Rolly Eyes and a message about "sticking it" and a place where "the sun don't shine".
It would certainly work and Mark has probably already gone and done exactly that. Just trying to discover some alternatives.
Kumar - as an aside and just to be a little anal, you seem to be awfully fond of the little 'rolling eyes' icon. Not sure if you understand the connotations of it. One rolls ones eyes to express exasperation or frustration, typically in response after someone else has said something exceedingly stupid. I doubt you mean it like that.
Of course now, the next response might very well be another visit from Mr Rolly Eyes and a message about "sticking it" and a place where "the sun don't shine".
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
Thanks everyone for all the suggestions!
We haven't tried implementing any change yet. I'll have to run some of these ideas past the other team members to see how we want to proceed.
ray.wurlod
I checked the Server Job Developer's Guide but didn't find anything about # as a column name.
chulett
This sounds like the best solution to me. The person we have in that is are "DataStage expert" is supposed to do something to get this to work for us. I'm not sure what he needs to do. So, for the time being, we're using ODBC.
loveojha2
I thought about this right away but from what I can tell at least, there is no such "derivation" attribute when writing to a table, just reading. If I'm wrong, please let me know.
Oh, and does anybody know if you can actually write to a view? I'm no DBA and haven't worked too much with Oracle, but I was under the belief that views were read-only.
Again, thanks for all the ideas everyone!
- Mark
We haven't tried implementing any change yet. I'll have to run some of these ideas past the other team members to see how we want to proceed.
ray.wurlod
I checked the Server Job Developer's Guide but didn't find anything about # as a column name.
chulett
This sounds like the best solution to me. The person we have in that is are "DataStage expert" is supposed to do something to get this to work for us. I'm not sure what he needs to do. So, for the time being, we're using ODBC.
loveojha2
I thought about this right away but from what I can tell at least, there is no such "derivation" attribute when writing to a table, just reading. If I'm wrong, please let me know.
Oh, and does anybody know if you can actually write to a view? I'm no DBA and haven't worked too much with Oracle, but I was under the belief that views were read-only.
Again, thanks for all the ideas everyone!
- Mark
HI Mark,
A simple syntax for creation of View would be
If you want to create a view for the full table, then the select command use
And now the New view name can be used very similar to the existing table.
Yes there are some constrainsts in using view.
-Kumar
A simple syntax for creation of View would be
Code: Select all
CREAT VIEW <View_Name> AS SELECT <list of columns> FROM <table_name>
Code: Select all
....SELECT * FROM <table_name>
Yes there are some constrainsts in using view.
-Kumar
I did an experiment with User Defined SQL and it worked. (I tested it on SQL Server).
My target table is like this
create table Abc (a# int primary key,b# int,c# int)
The Column names I used on the target link were a,b,c
The User defined SQL I used
INSERT INTO Northwind.dbo.Abc (a#,b#,c#) VALUES (?,?,?)
And It is working very fine, try it with Oracle and let us know the result.
My target table is like this
create table Abc (a# int primary key,b# int,c# int)
The Column names I used on the target link were a,b,c
The User defined SQL I used
INSERT INTO Northwind.dbo.Abc (a#,b#,c#) VALUES (?,?,?)
And It is working very fine, try it with Oracle and let us know the result.
Success consists of getting up just one more time than you fall.
Hi -
Had a similar problem once where special Danish characters were used in the field name.
Another way of solving this issue is by using custom sql the following way.
Import all the field names into the source stage.
Change the name of the fields that have the special characters to something appropriate.
Use custom sql.
In the custom sql you can write " select * from TableName"
This solution obviously requires that the table definition isn't changed and that the order of the fields are correctly specified.
Regards
Peter
Had a similar problem once where special Danish characters were used in the field name.
Another way of solving this issue is by using custom sql the following way.
Import all the field names into the source stage.
Change the name of the fields that have the special characters to something appropriate.
Use custom sql.
In the custom sql you can write " select * from TableName"
This solution obviously requires that the table definition isn't changed and that the order of the fields are correctly specified.
Regards
Peter
Last edited by peterbaun on Tue Jan 24, 2006 5:39 am, edited 1 time in total.
How do you tell it you want to use user defined SQL? When I connect an input to the ODBC stage, it isn't giving me the option to select "user defined SQL" as it does on an Output.loveojha2 wrote:I did an experiment with User Defined SQL and it worked. (I tested it on SQL Server).
My target table is like this
create table Abc (a# int primary key,b# int,c# int)
The Column names I used on the target link were a,b,c
The User defined SQL I used
INSERT INTO Northwind.dbo.Abc (a#,b#,c#) VALUES (?,?,?)
And It is working very fine, try it with Oracle and let us know the result.