Archives of the TeradataForum

Message Posted: Wed, 24 Oct 2001 @ 19:03:13 GMT

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

Hi Tony,

As John pointed out, there really is no easy way to do this. The Resolver phase of the parser does this work, although some of that work is done when you create/replace the view.

When you create a view the dbms inserts a row into dbc.tvm (to indicate that the view exists) and multiple rows into dbc.tvfields, one for each result column in the view.

In table dbc.tvm there are two particular columns which contain the view definition (and I think the following explanation has these the right way round.

- CreateText: this contains the text that you sent to the dbms

- RequestText: this contains the view definition, but with fully qualified table and column names. Hence, every table named in the FROM clause in your view definition will be fully qualified as "dbname"."tablename" and every column in the WHERE clause, select list etc will be fully qualified as "dbname"."tablename"."columnname" .

So, if you want/need to write your own resolver then the logic is something like the following;

- scan dbc.tables for the view. This will give you a list of all the column names as returned by the view.

- retrieve column dbc.tvm.requesttext (assuming I've got them the right way around) for the view in question and then parse the returned data. This will give you a first pass at matching the view columnname to the underlying source columns.

- HOWEVER, if the view you're parsing references another view, then you have to retrieve the column list and dbc.tvm.requesttext contents for the referenced one... and start all over again !

Cheers,

Dave

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