while searching for a possibility to get the datatype of the columns of a view I came
HELP COLUMN dbname.viewname.*
That gives me in SQL Assistant the result that I need. But is there a possibility to use this result in a SELECT or MACRO or STORED Procedure?
Any help or suggestions are appreciated!
Thanks in advance an best regards
P.S. I know the possibility of using
SELECT TYPE(column1), TYPE(column2)
But for using this, the view has to contain data. But in my case I cannot guarantee that...
You could use BTEQ to export the result of the HELP statement and then use the exported data in your SELECT statement within BTEQ using shell script or batch script.
not nice but might be sufficient for you...
1. create a table where you store the results.
2. generate per view 3 statements which
a. create a table dummy as select * from your view
b. insert select the dbc.columns info into your table from 1
c. drop the table
3. run the generated code
my test on DBC run into a  The Maximum Possible Row Length in the Table is too Large
- so it will not work always...You can change this into a column approach but this would mean much more statements to generate and execute...
below the code - you need to replace YourDB with one DB where you have create & drop rights. It also assume that your user has appropriate rights on the views.
create table YourDB.view_columns
select * from dbc.columns
) with no data
primary index (databasename, tablename);
create table YourDB.dummy
select * from ' !! trim(databasename) !! '.' !! trim(tablename) !!'
) with no data
no primary index;
insert into YourDB.view_columns
select '''!! trim(databasename) !! ''',
''' !! trim(tablename) !!''',
where databasename = ''YourDB''
and tablename = ''dummy'';
drop table YourDB.dummy;
where databasename = 'dbc'
and tablekind = 'V'
order by 1