Access DB and spaces in column names
Moderators: chulett, rschirm, roy
-
- Premium Member
- Posts: 503
- Joined: Wed Jun 29, 2005 8:14 am
Access DB and spaces in column names
--- Moderator add on ---
Originally split from this post
(release, job type and Os were arbitrarily selected)
--- End moderator add on ---
I will like to add up my question here itself. Correct me I am wrong ,So accroding to the reply I can use MDB as a source if I create a ODBC to it?
I have to use it but problem with the MDB file is that the MDB file has column names with spaces something like "a b c". so when I try to use it as a ODBC stage the query which gets generated is select 'xyz'.'a''b''c' from 'xyz' , which is obviously wrong and doesnot work. I tried doing select 'xyz'.[a b c] from 'xyz' but stil it does not work. I dont have much knowledge of Access so dont know how to the best I can use it as a source in datastage.
Anyone who can help me in this?
Originally split from this post
(release, job type and Os were arbitrarily selected)
--- End moderator add on ---
I will like to add up my question here itself. Correct me I am wrong ,So accroding to the reply I can use MDB as a source if I create a ODBC to it?
I have to use it but problem with the MDB file is that the MDB file has column names with spaces something like "a b c". so when I try to use it as a ODBC stage the query which gets generated is select 'xyz'.'a''b''c' from 'xyz' , which is obviously wrong and doesnot work. I tried doing select 'xyz'.[a b c] from 'xyz' but stil it does not work. I dont have much knowledge of Access so dont know how to the best I can use it as a source in datastage.
Anyone who can help me in this?
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
That's very close to hijacking the thread!
Set up the ODBC stage, with the DSN filled in. Then click GetSQLInfo. This goes out to the DSN and retrieves the SQL quote character (which is "`" for MS Access) and delimiter character.
So access needs queries like
Set up the ODBC stage, with the DSN filled in. Then click GetSQLInfo. This goes out to the DSN and retrieves the SQL quote character (which is "`" for MS Access) and delimiter character.
So access needs queries like
Code: Select all
select `table`.`col1`, `table`.`col2` from `table`;
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.
-
- Premium Member
- Posts: 503
- Joined: Wed Jun 29, 2005 8:14 am
Yeah thats true but I dint want to create a seperate thread for the same topic.
Thats true ray but the thing which is bothering is that the column names have a space in between which I am not able to decipher in the ODBC stage as it puts quotes everywhere it finds space. I tried User define query but that also give sme the same error. Is it like Column Name should not have spaces in there??
Thats true ray but the thing which is bothering is that the column names have a space in between which I am not able to decipher in the ODBC stage as it puts quotes everywhere it finds space. I tried User define query but that also give sme the same error. Is it like Column Name should not have spaces in there??
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
The ODBC standard forbids spaces so the ODBC driver enforces the rule. That MS Access does not is just Microsoft not following its own standards!
About your only choice is to use user-defined SQL. Let DataStage generate the SQL and then modify it.
About your only choice is to use user-defined SQL. Let DataStage generate the SQL and then modify it.
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.
-
- Premium Member
- Posts: 503
- Joined: Wed Jun 29, 2005 8:14 am
Use User Defined SQL as Ray says, then remove all quotes from the SQL, enclose in square brackets ("[" and "]" ) the column names that contain spaces.
The DataStage column names must be renamed to contain no spaces.
Example User Defined SQL :
SELECT Table1.FirstColumn, [Table1.Second Column] FROM Table1;
The DataStage column names must be renamed to contain no spaces.
Example User Defined SQL :
SELECT Table1.FirstColumn, [Table1.Second Column] FROM Table1;
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Dates can also be strange in Access; depending on the circumstances you may need to surround with "#" characters. For example The inner "#" characters identify a reference to a job parameter, the outer "#" characters identify an MS Access date value.
Code: Select all
select * from `Parts_Table` where `Parts_Table`.`Last_Updated` between ##FirstDateOfSample## and ##LastDateOfSample##
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.
-
- Participant
- Posts: 2
- Joined: Wed Oct 12, 2005 6:34 am
I ran into the same problem as Deepak today, and I simply did what ray and crouse suggested.DeepakCorning wrote:Thats the problem. That was th efirst thing that came in to my mind but it doesnot work.
Code: Select all
SELECT `Table 1`.FirstColumn, `Table 1`.`Second Column` FROM `Table 1`;
-
- Participant
- Posts: 57
- Joined: Wed Oct 21, 2009 4:46 am
- Location: India