I tried to find out the reason but I couldn't find it .. my Question is if I use only LENGTH() function in SQL Assistant it works and when I use it in BTEQ it doesn't ...
I need to know the reason and if only LENGTH() is not to be used then why it works in SQL Assistant. :)
LENGTH() is a function of the ODBC layer, not Teradata native. BTEQ uses CliV2, which is not ODBC, and hasn't these functions.
LENGTH is not part of Teradata SQL syntax, it's ODBC syntax.
In your case the ODBC driver replaced it with valid Teradata syntax (probably CHAR_LENGTH) before submitting it. CLI/JDBC/.NET don't do this translation, so this query fails.
It will also fail when you try to use it in a CREATE VIEW or CREATE TABLE AS in SQLA.
In the ODBC manual there's a list of those ODBC Scalar Functions, but it's not recommended to use them for the reason you encountered. You can switch off the translation by checking the "Disable Parsing" ODBC option.
In TD14 there's a built-in function LENGTH and in TD13.10 you could easily create a SQL UDF doing the translation.