Watch, Follow, &
Connect with Us
Public Report
Report From: InterBase/Interfaces/BLR    [ Add a report in this area ]  
Report #:  6202   Status: Reported
Views in System Tables
Project:  InterBase Build #:  WI-V7.1.0.131
Version:    7.1 Submitted By:   Patrick Moloney
Report Type:  Minor failure / Design problem Date Reported:  10/15/2003 12:21:29 PM
Severity:    Infrequently encountered problem Last Updated:
Platform:    Windows 95/98/NT 4.0 on Intel Internal Tracking #:  
Resolution: None  Resolved in Build: : None
Duplicate of:  None
Voting and Rating
Overall Rating: No Ratings Yet
0.00 out of 5
Total Votes: None
Description
In RDB$Relation_Fields, for IB6.5 I was able to use the RDB$Base_Field to distinguish Table columns from View columns without problem. (Tables were null).

IB7.1 behaves differently.  This column is no longer reliable (for this) and is not consistent for Views. (This column is still null for tables). Some Views have this column populated and some do not.
Steps to Reproduce:
Some investigation of the problem indicates that Views created from Unions cause RDB$Base_Field to be null.  This probably makes sense.

Other Views that contain calculated fields (eg: Max(column) are also null.  In these cases, the entire view is null, not only the calculated column.  While this too makes sense for the calculated column, it is not obvious why the regular columns are also null.

In investigating the RDB$View_Context column as a possible alternative, it corresponds directly with the RDB$Base_Field column.

This leaves no direct technique available (to this writer) to identify the columns associated with views.  However, identifying Views in RDB$Relations seems reliable and could be linked to this RDB$Relation_Fields table, although with significant additional processing.

And, it leaves the question of the intent of these two unreliable columns, RDB$Base_Field and RDB$View_Context.
Workarounds
None
Attachment
None
Comments

Craig Stuntz at 12/19/2003 7:27:28 AM -
I don't see what is so difficult about JOINing RDB$RELATIONS into your query.  If RDB$RELATIONS.RDB$VIEW_BLR is non-NULL then you have a VIEW, if it's NULL you do not.  This technique can never fail, and a JOIN hardly counts as "significant additional processing."

Patrick Moloney at 1/12/2004 2:53:42 PM -
It's not Views I'm having a problem with, it's identifying the columns that make up a view.  

Server Response from: ETNACODE01