Gotcha - Teradata Nested Views & Functions

Here's another interesting "gotcha" involving Teradata v13.1 and how it handles metadata for views, this time when using multiple layered views and a custom function. We encountered this error when creating a summary-style view in our data warehouse to push data aggregation into the database layer instead of building the query in Cognos Framework Manager.

The exact Teradata error that we received while attempting to create a new View was:

REPLACE VIEW Failed. 3822: Cannot resolve column 'DATE_ENROLLED'. Specify table or view.

A very generic error message, but what is unique about this particular column in the View is that it is the only one that is being operated on by a function.

The structure of the View query is (very abbreviated) as follows:

REPLACE VIEW view_db.new_view
(DAY_KEY)AS
SELECT
    COMMON_DB.CONVERT_TIMESTAMP_TO_KEY(DATE_ENROLLED) AS DAY_KEY
FROM
view_db.person

The query by itself runs without any problems and returns the correct results. But as soon as I put it into a CREATE or REPLACE VIEW statement it failed. Please note that the source of the query (view_db.person) is a View as well that merges records together from a source table (data_db.person) to produce an accurate list of current people.

Now during our investigation we discovered that replacing the source of the query (view_db.person) by referencing the source table of that view (data_db.person) we did not see the error anymore. The column name was unaffected because DATE_ENROLLED is a field in both the View and Table that is unmodified. However this was not a solution because it defeated the purpose of building the view_db.person View in the first place, and shoe-horning the query into a sub-select would be very complex and nearly unmaintainable.

So the structure of our new view and the column source was structured as follows:

view_db.new_view [DAY_KEY]
  - COMMON_DB.CONVERT_TIMESTAMP (function)
      - view_db.person [DATE_ENROLLED]
          - data_db.person [DATE_ENROLLED]

The Solution

We suspect that the Teradata database is encountering problems resolving the source of the DATE_ENROLLED column because of the identical naming between the view_db.person and the data_db.person which is confounded by applying the function call.

We were able to resolve the issue by creating a special sub-select query on the view_db.person that has the sole purpose of renaming the DATE_ENROLLED column.

The resulting fixed REPLACE VIEW statement is as follows:

REPLACE VIEW view_db.new_view
(DAY_KEY)
AS
SELECT
  COMMON_DB.CONVERT_TIMESTAMP_TO_KEY(DATE_ENROLLED_VIEW) AS DAY_KEY
FROM
  (SELECT DATE_ENROLLED AS DATE_ENROLLED_VIEW FROM view_db.person) a

This allowed the DATE_ENROLLED column to be correctly resolved and the view to be created successfully.