Archives of the TeradataForum

Message Posted: Mon, 28 Feb 2000 @ 16:05:42 GMT

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

Mark, I had a similar problem some time ago...I think the following mails will clarify the NCR position about formats.

This is the mail that I sent to NCR describing my problem.

We are in an upgrade process to Teradata V2R3, actually we have Teradata V2R2.1.0. In the upgrade process we had install a new ODBC Version 2.04.01.00 and Queryman V4.2.1 in our NT Stations, and we have problems running SQL sentences with format clauses.

For example... If you run the following query with an older ODBC release:

          select databasename,
          sum(maxperm)/1048576 (FORMAT 'ZZZ,ZZZ,ZZZ,ZZ9.99') as DB_Size,
          sum(currentperm)/1048576  (FORMAT 'ZZZ,ZZZ,ZZZ,ZZ9.99') as DB_Use,
          DB_Use/DB_Size*100 (FORMAT 'Z9.99') as "%_Use"
          from dbc.diskspace
          group by databasename
          where maxperm > 0

You will receive the answer in a adecuate format, but if you try to run the same sentences in a machine with the ODBC V2.4.1, it doesn't work with the formats. I tried to run the same query using BTEQ and it works fine, so I think that Queryman is not the problem, instead ODBC will be. I will appreciate all your help.

Thanks in Advance,

And that is the response I received from they.

. . . comments fro the DR

The query should only return a whole number (integer) on two columns (db_size & db_use), but instead the two columns returned some decimal values

This problem exist on all Unix flavors and also in Windows.

******* HOST set to 5150 by ETA on 28 Sep 1999 at 07:54 (was UNIX).
******* DR NOTE added by ODBC SatyamGroup (NDFSODBC)
***cont via DOCNOTE on 27 Oct 1999 at 00:48.

The following is the response from Rudy Ezquerro regarging this Problem. This is NOT a bug in the ODBC driver. This is the way the ODBC driver was designed to operate. You must remember that BTEQ and the ODBC driver are not the same. BTEQ operates in a different mode where everything is returned as characters, therefore displaying FORMATTED data is not an issue.

Supporting the FORMAT clause in the ODBC driver is an issue because FORMAT is not ANSI compliant. FORMAT is merely a different way of displaying the data, but the data value remains constant. The ODBC specification expects data types to be treated/returned in a specific way. Applying the FORMAT clause to data would violate the ODBC specification and it would also break 3rd party general purpose applications that didn't expect the data in a different format.

WORKAROUNDS:

A workaround would be for an application to retrieve data specifying the FORMAT clause they wanted and then cast it into a CHAR or VARCHAR. From there it could extract/display the formatted data

For example:

SELECT birthdate (FORMAT 'mm/dd/yyyy')( CHAR(10)) FROM employee

There are also a couple of extensions in the Teradata ODBC driver to help identify Teradata column FORMATs. These are documented in the Installation and User Guide.

1. The result set returned by SQLColumns actually returns a few additional columns of information specific for Teradata. Column 14 of the result set is the FORMAT for the table column

2. SQLColAttributes will also return the Teradata FORMAT when fDescType = 1302

--Rudy

Keeping in view the explanation given above, and the suggested workaround, this DR may be closed.

******* HOST set to WINNT4.0 by DKJ on 29 Oct 1999 at 09:55 (was 5150).
******* DR NOTE added by Diane Jones (DKJ) on 29 Oct 1999 at 10:00.

Closed per Rudy's comments above. Workaround is available.

Regards,

Kenneth Stiglich M
Serv. Data WareHouse

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