how to create view definition using DBC tables??

Database
Enthusiast

how to create view definition using DBC tables??

Hello All,

I am trying to create view definition from DBC tables below is the query whihc i have come up with, but the problem is listing out all the columns in the select list of the underlying table .

Can any one suggest how this can be achived.

select  

'REPLACE VIEW DB_NAME.'||regexp_replace(b.TableName,'tfcs','VF',1,0,'i')||' as LOCKING ROW FOR ACCESS SELECT' || b.ColumnName || ' from DB_NAME.'||b.TableName||';'

from DBC.ColumnsV b where b.DatabaseName='DB_NAME' and b.TableName = 'TABLE_NAME';

Reagdrs

Anvesh 

1 REPLY
Enthusiast

Re: how to create view definition using DBC tables??

You can get the col names from the dbc.columnsV joining with the DBC.TablesV.

Generate 2 Ranks.

1. based on Columnid in the asc order

2. based on Columnid in the desc order

Use a case statement to see if the asc rank is 1, then include 'Replace view Viewname locking row for access Select ' else, ','.

Similarly use another case to chk if the desc order rank is 1, then 'From DB Name.TBL Name;', else ''.

*Not able to cut n paste code from my Sql assistant. Sorry!*

- Ranga