Archives of the TeradataForum

Message Posted: Tue, 08 Feb 2011 @ 23:16:01 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Additionally

BTEQ can operate over CLI in either Field mode or Record Mode

In Recordmode it is the data as presented by the sql query that is returned - any naked format statements are ignored. The internal represnetation for the client host is returned (eg two byte binary for smallint, 4 byte binary for date)

In Field mode - the format statements are obeyed and a character string returned per column.

In Recordmode an integer is returned as its binary bit represnetation not as a charcater string. In Field mode data is returned as character representations (using default formats for each datatype if none is present).

Fieldmode is the default so many users only ever see the character string reports from Bteq but if doing an Export then there os a choice and one can inspect the exported records in either mode and see the difference. Recordmode is very similar to fastload format. It is alos possible to merely do a select in recordmode and look at the result - worth doing to understand what is happening

What do I mean by 'naked' format statements - well if one selects a column and Casts it as well as formatting it one effecttvely formats it inside teradata and casts it to a character string inside teradata in the spool and the returned column then is actually a character string not the original number, date etc.

Judicious use of brackets is needed to ensure this happens. If one does this one can get exactly the same result in Bteq or SqlA. It is not enough to bracket bracket the format. One must format and cast and then bracket the expression to be sure

SQL Assistant uses ODBC (or .Net now I think) but underneath it all the ODBC still has to be translated to CLI - my guess is that the odbc driver operates in recordmode so naked format clauses are ignored unless one casts and brackets the cast to become a character result expression as above to be returned by CLI in the record. If one doesn't do this then odbc will get the original data type and then pass that through some of its own numeric/date interpretation in the windows environment obeying windows defaults etc for date presentation etc. ODBC alwys returns the data as characters.

If one knows this one can usually develop and test reformatting/castings inside SQLA and transfer the statements to BTEQ - it just takes a bit of casting and bracketing as well as formatting. One has to be careful with high precision numbers when going thjrough odbc/sqla though as odbc has a limit of 15 digits precision and thereafter uses a floating point number and truncates the precision after 15-16 digits to the zero digit.

This is the reason why one has an option in SQLA for SQLA/TD ODBC driver to reformat a large precision number (> dec(16), bigint) as a character string.

Regards

Andy Baker

	<Prev	Next>		<<First	<Prev	Next>	Last>>