All Forums Database
12 Mar 2012
Datatype of a Column of a View

I need to be able to select the datatypes of all columns of a view.  Since the Columns view does not provide this information (i.e. the ColumnType and other useful fields are null), I've seen two answers:  The first suggests using "help columns <viewname>.*".  The second suggests using "select type (<viewname>.<columnname>)".

The first is insufficient because I need a Select query that can be nested and whose columns can be manipulated.  The second is insufficient because the select operates on only one column at a time.

Is there a way to select the datatypes of all columns of a view with one Select statement?

 

 

ulrich 816 posts Joined 09/09
12 Mar 2012

Why can't you select all column types in one select?

something like

select top 1 type(col1),type(col2),...,type(coln) from view

But even this is not realy elegant...

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

ulrich 816 posts Joined 09/09
12 Mar 2012

Maybe the following would be an alternative

1. create a tmp table with no data from the view.

create table v_tmp as (select * from yourView) with no data.

2. retrieve the column types from dbc.tables

3. drop v_tmp table.

would overcome the issue that if business logic is applied to the view even a top 1 might take a long time to come back.

 

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

13 Mar 2012

Thanks for the replies.  I will give the temp table suggestion a try, but am open to any better alternatives.  I have an additional requirement/constraint on my problem:  I will be getting the metadata for all columns in all tables and views in all schemas.  Thus creating and dropping a thousand temp tables, one for each view, is not ideal. 

ulrich 816 posts Joined 09/09
13 Mar 2012

a Stored Procedure might help - but agree that it is not ideal

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

ulrich 816 posts Joined 09/09
13 Mar 2012

this might take a while to run but it is only one script and does not need to create and drop tables.

I used a volatile table for demo purposes only but you might want a permanent one....

.run file = /yourpath/logon.txt ;

.set width 500;

.OS rm /yourpath/view_col_type_sql.txt;

.export report file=/yourpath/view_col_type_sql.txt

select 'insert into view_column_data_type Select ''' !! Trim(databasename) !! ''','''!!Trim(tablename) !! ''','''!!Trim(columnname)!!''',type('!!trim(databasename)!! '.' !! trim(tablename)!! '.' !! trim(columnname) !!');'(title '')
from dbc.columns 
where (databasename, tablename) in (select databasename, tablename from dbc.tables where tablekind = 'V')
;

.export reset;

create volatile table view_column_data_type
( 
  databasename varchar(30),
  tablename varchar(30),
  columnname varchar(30),
  columntype varchar(30)
) primary index (databasename, tablename)
on commit preserve rows;

.run file /yourpath/view_col_type_sql.txt;

select *
from view_column_data_type
order by 1,2,3
;

.logoff;

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

ulrich 816 posts Joined 09/09
13 Mar 2012

I used the same approach for create table, store column info in table drop table.

so generate the statements and call the generated script.

Seems to run much faster.

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

15 Aug 2016

I have the following CASE statement in my view:
SELECT
  CAST((CASE EV.EVNT_CD
    WHEN 'A001' THEN 'SOMETHINGxxxxxxxx'
     ELSE 'UNKNOWN'
END) AS VARCHAR(17) ) AS EVNT_DESC
When I query the data dictionary and look for the columntype of CV, i get a datatype length of 34.
why is the length double what the view has declared?
            

dnoeth 4628 posts Joined 11/04
16 Aug 2016

All literals default to Unicode character set, thus the CASE results in a Unicode string.
dbc.ColumnsV returns the ColumnLength as physical size in bytes, for Unicode (CharType=2) you must divide by two to get the logical size.
 
If you want to get Latin you must apply TRANSLATE( CASE .... USING UNICODE_TO_LATIN)
 

Dieter

You must sign in to leave a comment.