When I run the SELECT statement below in Teradat Studio version 14.02, I get error 'Executed as Single statement. Failed [3996 : HY000] Right truncation of string data. Elapsed time = 00:00:00.963 STATEMENT 1: SEL failed.' The same query runs fine in SQL Assistant. The issue has to do with CAST(USER AS INTEGER) where USER is the numeric sign-on ID. Does Teradata Studio or JDBC return USER as alphanumeric?
SEL * FROM cfdw2_SAMADHOC_avws.A_BORROWER B
INNER JOIN CFDW2_SAM_SECURITY_SVWS.A_USER_SECURITY C
ON CAST(C.SSO AS INTEGER) = CAST(USER AS INTEGER);
The reason is that Teradata Studio sets the JDBC TMODE property to its recommended value of ANSI in the connection. SQLA uses TERA mode. You can change the connection profile JDBC TMODE property to TERA and try again. The problem with TERA mode is that it is masking the right truncation string problem in your SQL statement.
I agree with Francine. DannyChin, you said "The same query runs fine in SQL Assistant." No, it doesn't "run fine". What it actually does is truncate your data silently without telling you.
If you want silent truncation of your data without your knowledge, then use TERA mode. On the other hand, if you prefer to keep all your data intact, then use ANSI mode.