Column name contain special character(/) !!!

[dsscholar]

Hi,

I have a SQL server database where my table contains columns name like
Byte_Received/sec ,Byte_sent/sec etc.... so while selecting these columns in query using ODBC Enterprise stage in datastage i have only selected either through select * from table or select \"Byte_Sent/sec" from table .... but due to this in column metadata i have written both of the option Byte_Sent/sec or Byte_Sent_sec but it fails the jobs and doesnt recognize the / character

Error Description:
main_program: Syntax error: Error in "export" operator: Error in operator arg: In field "Bytes_Total": Expected ";", got: "/", line 15
In field "Bytes_Received": Expected ";", got: "/", line 16
In field "Bytes_Sent": Expected ";", got: "/", line 17
In field "Packets": Expected ";", got: "/", line 18

Also possible things i have tried
1-Use ascii value of this character also
2-tried select * from table that not working
3-using escape charater also
4-use enable quoted identifier but that also not working.
5-I havent a plugin of SQL Server stage so that i will not tried yet.


If anyone facing similar kind of situation and get the solution plz share with us or have any possible solution throughwhich it get done plz share

[pandeesh]

Have you tried "Bytes_sent\/sec"?

[dsscholar]

Have you tried "Bytes_sent\/sec"?

Yes i hv tried this also but it doesnt return anything,just a blank data window is returned , not even column name is shown.

[SURA]

Hi Friend

select "Byte_Received/sec " as BYTERECVD_SEC from table name;

use BYTERECVD_SEC as a colum name in your metadata.

DS will not accept special char.

DS User

[dsscholar]

Hi That we cant do if we do this then in datastage value inside the double quotes is considered as hardcode value for column so output like
BYTERECVD_SEC
Byte_Received/sec
Byte_Received/sec
Byte_Received/sec
Byte_Received/sec

so it will not return even in sql also provided anything in doubles quotes is considered as harccoded value...
so this will not work

[SURA]

Hi

I cant understand what you are tying to say.

If you have the colum like Byte_Received/sec , then in ODBC stage you need to give the column name as BYTERECVD_SEC and make it as user defined query and there you can give it like select "Byte_Received/sec " as BYTERECVD_SEC from table name;

I am sure it will work.

DS User

[dsscholar]

No it not working...
because because datastage will not recognize the column in double quotes ...and double quotes value is considered as hardcoded value in DS plz try at ur end.

[ray.wurlod]

Assuming ANSI standard SQL, double-quotes indicates an identifier while single-quotes indicates a constant value. So check your settings (perhaps on the ODBC driver or database client connection) to ensure that ANSI standard SQL is being used. You might also try qualifying the column name with a table name or, as a last resort, defining synonyms on the table itself.

[bicap]

No it not working...
because because datastage will not recognize the column in double quotes ...and double quotes value is considered as hardcoded value in DS plz try at ur end.

Try to use // for / in datastage.. Like/

Select bytereceived//sec...

Hope this helps..

[ray.wurlod]

Hope this helps..

Hope?!

Did you verify it?

[dsscholar]

Hi Guys,

I have find out the solution and it is
we have to use column name in select query like this

select
[Bytes_Total/sec] as Bytes_Total_sec ,
Bandwidth_Utilization_Percentage,
[Bytes_Received/sec] as Bytes_Received_sec,
[Bytes_Sent/sec] as Bytes_Sent_sec from table;


It is working in Datastage so if anyone get scenrio like this in SQL SERVER is the source then use this braces [] in datastage.


Thanks guys

Regards
rahul