Are Your Views Not Validating Properly? Here Is A Guide To Fix That

Description

If you have a view that does not validate, there are a few possible reasons:

1. There might be an error in the view’s definition (user error).
2. The view’s definition might reference objects not included in the model (Like external tables).
3. The wrong parser was selected.
4. The syntax/feature being used in the view might not be supported.

For the first reason, not much can be done except validating that the sql is correct against the database. We also have limited options for number 2, as Data Architect currently does not support referencing tables on other models/schemas. If the syntax is not supported then we recommend opening an enhancement request in a Support Case to implement this feature in a future release of the product.

Steps To Fix This

Even if the view does not validate, it can still be used, and the view’s definition (DDL) will remain visible and included in forward engineering. However, when a view is not validated, certain functionalities will be limited. For example, table relationships and the column list will not be displayed on the diagram, and other features may also be restricted.

If you need further assistance on View validation you can certainly contact our Support team. To investigate validation issues, we typically require the full model since validation is done against other objects in the model. Once we have the model, we can determine which of the potential causes mentioned above is at play.

If you are having an error that shows a syntax issue near the “WITH” clause, commonly used for Common Table Expressions (CTEs) then we have additional information for you. Support for CTEs depends on the platform:

• If you are using SQL Server, CTEs are supported, so this could be a bug.
• If you are using Snowflake, we are in the process of adding support for CTEs, which should be available in version 20.5.

It is important to note that complex features like CTEs are not supported in logical views. Logical models are designed to be more generic and are not tied to the specific syntax of any platform.

There are two ways to handle this:

1. You can leave the view as invalid. The SQL will still be saved, but with reduced functionality. If you do this, it is recommended to disable the SQL Validation Dialog from the Options menu:

2. You can set the Logical Model to use the view parser for the specific platform you are working with. To do this, go to the **Model Options** and change the view parser from **ANSI** to the platform you are using. While this might help, it may not completely resolve the issue depending on the complexity of the feature being used.