Archives of the TeradataForum

Message Posted: Wed, 02 Aug 2006 @ 13:14:55 GMT

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

This question is asked quite often. Perhaps because the answer is not quite easy in general. Try one of the previous discussions:

www.teradataforum.com/teradata/20051108_190825.htm .

Some of the SQLA secrets (of that time) have been revealed there.

A natural way of looking up the type would be to use TYPE like in

     select TYPE(I.InfoKey)
     from dbc.DBCInfo I;

but TYPE goes into the trouble of calculating the data type for each row in the view. I think it used to be possible to suppress this by using the fully qualified names or something but this might have changed somehow. I am confused.

However, what still seems to work is the following:

     select type(dbc.DBCInfo.InfoKey);

      *** Query completed. One row found. One column returned.
      *** Total elapsed time was 1 second.

     Type(InfoKey)
     ---------------------------------------
     VARCHAR(30)

SHOW QUALIFIED can provide the answer as well but you need to have access to the base tables:

     show qualified (select * from dbc.DBCInfo);

      *** Text of DDL statement returned.
      *** Total elapsed time was 1 second.

     ------------------------------------------------------------------
     CREATE SET TABLE DBC.DBCInfoTbl ,FALLBACK ,
          NO BEFORE JOURNAL,
          NO AFTER JOURNAL,
          CHECKSUM = DEFAULT
          (
           InfoKey VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
           InfoData VARCHAR(16384) CHARACTER SET LATIN NOT CASESPECIFIC)
     UNIQUE PRIMARY INDEX ( InfoKey );


      *** Text of DDL statement returned.
     ------------------------------------------------------------------
     CREATE VIEW "DBC"."DBCInfo" AS SELECT "DBC"."DBCInfoTbl"."InfoKey","DBC"."
     DBCInfoTbl"."InfoData" FROM "DBC"."DBCInfoTbl"  WITH CHECK OPTION ;

Just a few options. Take your pick.

Regards,

Victor

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