SQL SERVER select statement issue

[_chamak]

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

[suse_dk]

# 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

[_chamak]

Tried that but still no luck it shows the same error

[SURA]

Did you tried either ' or " for that column?

'column_name' as col
or
"column_name" as col


DS User

[_chamak]

yeah i tryed ['POOL #']....["POOL #"]....'POOL #'.... "POOL #"....as POOL
nothing works

[chulett]

# and $ sign are reserved signs in DataStage, so maybe it is possible to use the internal representation of the signs in your select statement?

Tried that but still no luck it shows the same error

Can you show us exactly what you tried? Did you notice that there are two underscores on each side of the replacement internal value?

[SURA]

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

[_chamak]

even tried it in ODBC enterprise still the same error

[jgreve]

yeah i tryed ['POOL #']....["POOL #"]....'POOL #'.... "POOL #"....as POOL
nothing works

Based on the the following quote from this link: http://publib.boulder.ibm.com/infocente ... d%65%22%20

* 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.

It sounds like you should be able to use SQL like this:
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?

[chulett]

I think that doc is a little short on details. From what I recall, you replace a "#" with "__035__", that's two underscores on both sides of the internal value.

[jgreve]

Ahh
That makes sense.

I think that doc is a little short on details. From what I recall, you replace a "#" with "__035__", that's two underscores on both sides of the internal value.