Toad World® Forums

View creation in Toad data modeler

Hi, I have an oracle script to create a view, the script is complex as it uses different kinds of joins and CTE (common table expression). I want to create that view in the Universal Physical Model using the script. Also when i convert the model to Oracle, SQL Sever and Mysql then the script to create that view syntax should be compatible with different RDBMS.

How to achieve this??

Hello there,

You can create an empty view in your Universal Model, open its properties, paste your script to the SQL tab and check Generate SQL Only checkbox in General tab.

However, when you convert your Universal Model to a Database-specific Physical Model, the script inside your view will not be converted. It will be inside the SQL tab of your view, but it will be commented out, since TDM does not convert scripts.

I’m afraid that in your situation you will need to edit your script syntax for each specific database platform.

Regards,

Lukas

Hi Lukas, Thanks for the suggestion. Actually i have changed my View in such a way that it can be easily executed in all forms of databases SQL Server, Oracle and Mysql. But when i convert the universal model to Physical model and generate DDL, the view is commented out. Is there a way such that the create view statements are not commented out in Generate DDL script??

Hello again,

You can choose not to comment out database-specific items in the Model Convert wizard:

This should leave your code uncommented after conversion to Physical Model.

Regards,

Lukas

Thanks that was really helpful