Generate SQL views from a SELECT AS statement

When modeling views for RDBMS or a SQL-like target, you can manually select the columns to be included in the view, by following the instructions in this page.

 

You may also specify a SELECT AS statement in the corresponding property in the Properties Pane.  This information is stored as text and used to generate the appropriate script in forward-engineering.  

 

 

However, it does not automatically create the view attributes in the model.

 

 

 

 

If you want the view attributes to appear in your model (ERD and ERDVs), you can use the following steps:

1. Perform a forward-engineering to generate the script to file, via Tools > Forward-Engineer > DDL.  Make sure that all the tables referenced by the view are included in the script — this is required for the process to work.

2. Perform a reverse-engineering in the same model, with Tools > Reverse-Engineer > Data Definition Language File..., then select the matching database, choose the DDL file created in step 2, and select the same schema:

 

 

3. During the reverse operation, a conflict detection dialog appears, where you must choose to Merge (plus it is suggested that you check the Apply to all option.)

 

 

This process recreates the view in the model, with its columns properly represented as attributes, based on the SELECT statement originally submitted: