PLEASE HELP! - Mapping Views

Toad Data Modeler
1

Hi Everyone,

I am new to toad as well as databases in general. I am currently trying to create a diagram of the views I frequently use. I need to able to see what views are available, what data they contain and how they are connected. This would go a long way in helping me understand where to I can go to get information and the best way to go about getting it.

I was able to create an ERD as well as a code road map which has gotten me about 95% of the way there, but I am stuck on the last part. The issue is that it isn’t specific enough as to what links the views together. It just shows that they are linked. Futher, this is taking me to the actual tables and how the view is linked to the table. I am just needing to access views.

Any help would be appreciated. Thanks!

2

Hello there,

Just to make sure, you would like to know what exactly makes up a relationship between two views or a view and an entity? For example, something like a relationship between two entities which are linked by Primary Key and Foreign Key? Or do you have something else in mind?

I am also not sure what do you mean by needing to access views. Could you perhaps rephrase that?

Regards,

Lukas

3

Thank you for the response!

I would like to know the relationship between two or more views.

Essentially all of my work is done with about 100 views that have been created by other people. I am frequently running into issues with my queries duplicating / not grabbing neccessary data when I join two or more views together. I want to better understand what I should be joining on. Since there are so many views going through them one at a time to see the relationships between them would take a lot of time. I am trying to get toad to create a map to basically show all the ways that View #1 is related to View #2, etc.

What I mean by just needing access the views is that I don’t need to see how the actual tables are creating the view. That is what I have been getting with the Code Road Map. It brings in all the tables and relates them to all the views. Way too much information.

Thank you for you help!

4

toad1010, in your diagram you could move all of the views to their own workspace and this would isolate them from the rest of the objects in the model. You would still be able to see the source of the view but you wouldn’t be bothered with all of the other objects in the model. Simply right-click on a view and choose ‘add into new workspace’ from the menu.

5

Mark, this workspace is very helpful. Is there anyway for Toad to finish by mapping (drawing lines to the relating column names) or am I on my own for this step? Thanks.

6

Views are mapped to tables. Once you create a workspace you can bring in the associated tables and they will be mapped automatically.

7

I would have to play with the idea of mapping a view to another view. I will work on that later this morning unless someone else has already done this and can share their answer.

8

So there is no way to map one view to another view without viewing the table?

9

Thank you!

10

There are basically two kinds of View Relationships:

1) Automatically created View Relationships

These are created when one view contains attribute(s) from another view. When you hover your mouse over them, the linked attributes are highlighted.

views1.png

Note that these View Relationships are only created when you link two views via View Properties | Attribute tab. The second option is to write the view SQL code in the View Properties | SQL tab. But in this case no View Relationship will be automatically created. If any of your colleagues creates views this way, you will need to organize them manually.

2) Manually created View Relationships

These are useful when you work with views that are linked via View Properties | SQL tab. Simply choose View Relationship from Model Objects toolbar and create a relationship between two views. Look at the following picture:

3683.views2.png

There are two views. View7 is linked to Entity9 via View Properties | Attribute tab. An automatic View Relationship has been created and when you hover your mouse over it, you can clearly see the linked attributes.

views3.png

View5 is linked to View7. However, it’s not linked via View Properties | Attribute tab, but rather via manually written SQL code in **View Properties | SQL tab **(you can also call them Text Views). In this case, no automatic View Relationship is created and the view itself also doesn’t show the linked attributes. So, to organize things a little I added a View Relationship12 between View5 and View7 and I also added a note to View5 (View Properties | Note) which displays the linked attributes every time I hover my mouse over View5. It’s not as nice as automatically created View Relationships, but it’s better than nothing.

If you have moved some of your views to a new workspace, some of the automatically created View Relationships may not have been moved. They can be added to a new workspace easily. In new workspace, right-click anywhere and choose Fill Relationships to Workspace. This will add all View Relationships to your workspace that might have been missing (alternatively you can right-click a specific workspace in Model Explorer | Edit and choose which objects and relationships you want to move to the new workspace.)

One more thing which is useful for organizing your model is to use colors. Right-click any Entity/View, select Edit and change Brush Color to whatever you like.

Hopefully this cleared up things a little for you! If you have any more questions, feel free to ask!

Regards,

Lukas