Sunday, October 17, 2010

Find column type using built in function!!!

Did you know that datatype of column could be found  using a built in function called "TYPE" .

Till I came across this function, I used to follow the ancient method of digging into Data  Dictionaries(dbc.columns) to find the type of given column.

There are some cases in which people use CASE function to find the match for type of the column and based on which they do  execute steps.
E.g:  Check for column as timestamp , if yes extract date or so...

To avoid usage of dbc.columns table , we can use Type(Column Name)  function

SELECT TYPE(TAB1.COL1);

Type(COL1)
-----------
INTEGER

3 comments:

  1. I'm trying to use this function but I'm having difficulty.

    I created a volatile table from a view :

    create volatile table Tbl1 as
    (
    select * from db1.?Tablename
    ) WITH DATA ON COMMIT PRESERVE ROWS;

    I’m trying to develop some SQL code that allows me to test a column’s type and format, however the data for my volatile table doesn’t appear in dbc.columns.

    The result from the command “help table Tbl1” gives me the exact type and format that I would expect to see: “TS” and “YYYY-MM-DDBHH:MI:SS.S(6)” for columns that are Timestamp, “DA” and “YYYY-MM-DD” for columns that are date, etc.

    However, if I execute “select format('EXTRACT_DT') from Tbl1 group by 1;” for a column that “help table Tbl1” told me was timestamp, I receive “X(10)” as the result. Likewise, if I execute “select type('EXTRACT_DT') from Tbl1 group by 1;”, I receive “VARCHAR(10) CHARACTER SET UNICODE” as the result.
    Why don’t the Type() and Format() functions return the same value as the “help table” command?

    ReplyDelete
    Replies
    1. Hi,

      Please try using permanent tables. Also note that type tells you the datatype of the column where as format tells you how data is displayed.

      try to create table explicitly by defining columns and run the type & format functions on columns. It should work.

      Delete
  2. nice one...dude :)
    naveen

    ReplyDelete