SQL SERVER select statement issue
Moderators: chulett, rschirm, roy
SQL SERVER select statement issue
select [PMT AMT] as PMT_AMT
[POOL #] as POOL
ect....
from table_name
this is the sample sql i am trying to read data using ODBC from sql server DB but having issue since one of the column is having # .
ODBC_GE_MONEY_INPUT,0: ODBC Info: SQLSTATE = 42S22: Native Error Code = 207: Msg = [IBM(DataDirect OEM)][ODBC SQL Server Driver][SQL Server]Invalid column name '"POOL ,[FICOScore'.
this is the error i am encountering. Can any one help me with this.
Thanks,
Keerthi
[POOL #] as POOL
ect....
from table_name
this is the sample sql i am trying to read data using ODBC from sql server DB but having issue since one of the column is having # .
ODBC_GE_MONEY_INPUT,0: ODBC Info: SQLSTATE = 42S22: Native Error Code = 207: Msg = [IBM(DataDirect OEM)][ODBC SQL Server Driver][SQL Server]Invalid column name '"POOL ,[FICOScore'.
this is the error i am encountering. Can any one help me with this.
Thanks,
Keerthi
# and $ sign are reserved signs in DataStage, so maybe it is possible to use the internal representation of the signs in your select statement?
http://publib.boulder.ibm.com/infocente ... d%65%22%20
http://publib.boulder.ibm.com/infocente ... d%65%22%20
http://publib.boulder.ibm.com/infocente ... d%65%22%20
http://publib.boulder.ibm.com/infocente ... d%65%22%20
Re: SQL SERVER select statement issue
Tried that but still no luck it shows the same error
Re: SQL SERVER select statement issue
Did you tried either ' or " for that column?
'column_name' as col
or
"column_name" as col
DS User
'column_name' as col
or
"column_name" as col
DS User
Re: SQL SERVER select statement issue
yeah i tryed ['POOL #']....["POOL #"]....'POOL #'.... "POOL #"....as POOL
nothing works
nothing works
Re: SQL SERVER select statement issue
Can you show us exactly what you tried? Did you notice that there are two underscores on each side of the replacement internal value?suse_dk wrote:# and $ sign are reserved signs in DataStage, so maybe it is possible to use the internal representation of the signs in your select statement?_chamak wrote:Tried that but still no luck it shows the same error
Last edited by chulett on Tue Aug 16, 2011 7:42 am, edited 1 time in total.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
Re: SQL SERVER select statement issue
Ok, tell me which ODBC are you using?
ODBC connector / ODBC Enterprise?
If you tried with ODBC connector , then try to use ODBC Enterprise. It will work!
Just i tried the below query in ODBC Enterprise and it worked for me (But i tried it in windows os)
select "deptno#" as dept from dbo.emp1
DS User
ODBC connector / ODBC Enterprise?
If you tried with ODBC connector , then try to use ODBC Enterprise. It will work!
Just i tried the below query in ODBC Enterprise and it worked for me (But i tried it in windows os)
select "deptno#" as dept from dbo.emp1
DS User
Re: SQL SERVER select statement issue
even tried it in ODBC enterprise still the same error
Re: SQL SERVER select statement issue
Based on the the following quote from this link: http://publib.boulder.ibm.com/infocente ... d%65%22%20_chamak wrote:yeah i tryed ['POOL #']....["POOL #"]....'POOL #'.... "POOL #"....as POOL
nothing works
It sounds like you should be able to use SQL like this:* The column names are used except when the external datasource column name contains a character that WebSphere DataStage does not support. In that case, two underscore characters replace the unsupported character.
not working: <s><e><l><e><c><t>< ><P><O><O><L><#><,>< ><F><I><C><O><S><c><o><r><e>< ><f><r><o><m>< ><G><E><_><M><O><N><E><Y]
might work: <s><e><l><e><c><t>< ><P><O><O><L><_><_><)><,>< ><F><I><C><O><S><c><o><r><e>< ><f><r><o><m>< ><G><E><_><M><O><N><E><Y>
The angle-brackets are meant to specifically emphasise the sequence of characters, I wanted to emphasise replacing POOL# with POOL__ and I wasn't sure how the two underscores would look once they posted.
Just for fun, could you post the actual SQL statement you're trying to use in addition to the error message?