Hi,
I have an Access Database input source. Most column names have spaces between them. When I import the table definition using Manager using an ODBC connection, all the columns import fine.
In my server job, I have an ODBC stage that connects to the Access Database through an ODBC DSN. I then load the imported table definition into that stage and that works fine.
However, when I do some mapping in a Transformation Stage, the columns that have spaces in between their names highlight red. Also, the job doesn't complie and it states that there is a general syntax error. I understand that column definitions in stages only allow alphabetical characters, periods, underscores but no spaces. I know that I could manually change the column names and replace any spaces with underscores.
Is there any solution to avoid changing the column names?
Any help would be greatly appreciated.
Thanks
Vinal.[/i]
Spaces between Column names
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Make sure that the column names are correctly quoted. Click the GetSQLInfo button. At worst, insert the backquotes into the Derivation column on the Columns tab, or use user-defined SQL into which you've pasted the backquotes. Don't forget the Selection tab; any SQL you've placed there must also have delimited identifiers (backquoted column and table names).
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.
Does that mean in any case I will have to change the column name and replace any spaces with some other character (like underscores) eg. Equipment ID must be changed to Equipment_IDray.wurlod wrote:Make sure that the column names are correctly quoted. Click the GetSQLInfo button. At worst, insert the backquotes into the Derivation column on the Columns tab, or use user-defined SQL into which you've pasted the backquotes. Don't forget the Selection tab; any SQL you've placed there must also have delimited identifiers (backquoted column and table names).
Thanks
Vinal.
Hi,
Other options include building views with proper column names hence bypassing the issue.
Another option would be to change hte table definition and use the original column names with aliases (i.e. column name = column_name , and the derivation would be
just pick the best way for you.
How about user defined select * and manually change the table definitions to have DS legal column names?
IHTH,
Other options include building views with proper column names hence bypassing the issue.
Another option would be to change hte table definition and use the original column names with aliases (i.e. column name = column_name , and the derivation would be
)column name column_name
just pick the best way for you.
How about user defined select * and manually change the table definitions to have DS legal column names?
IHTH,
Roy R.
Time is money but when you don't have money time is all you can afford.
Search before posting:)
Join the DataStagers team effort at:
http://www.worldcommunitygrid.org
![Image](http://www.worldcommunitygrid.org/images/logo.gif)
Time is money but when you don't have money time is all you can afford.
Search before posting:)
Join the DataStagers team effort at:
http://www.worldcommunitygrid.org
![Image](http://www.worldcommunitygrid.org/images/logo.gif)
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
It's not so much DS-legal as ODBC-legal. ODBC is very strict about adherence to standards. One of these is that column names containing "illegal" characters (including space) must be quoted. The standard specifies double-quote, but the Access driver ignores this and uses backquotes instead. Aren't standards wonderful?!
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.
Thanks for the reply.ray.wurlod wrote:It's not so much DS-legal as ODBC-legal. ODBC is very strict about adherence to standards. One of these is that column names containing "illegal" characters (including space) must be quoted. The standard specifies double-quote, but the Access driver ignores this and uses backquotes instead. Aren't standards wonderful?!
So are there any other connection types that I can use to fix the problem.
Thanks
Vinal.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact: