Toad World® Forums

ER Diagram Relationship


#1

I wanted to know why when I pull a table from the Object explorer i am not able to see the relationship to it? One of my friends mentioned that it could be that the privileges are strict. I am connecting to a Oracle data and was hoping you may help!


#2

What type of connection are you using? ODBC or OCI Client or Direct? OCI or Direct is the best and you should see all relationships in the ER Diagramer. What is your dependency level in the Er Diagramer. Please post a screenshot with more detail so I can debug.

Debbie


#3

Here is the information.
ER Diagram.doc (184 KB)


#4

It looks like the attachment didn’t make it. Can you click on link below. From the community site when you reply you can attach files and they will be included.

Debbie


#5

Lets try again.
ER Diagram.doc (184 KB)


#6

Your objects are all views. Relationships would be associated to tables.
If you would like to draw relationships, just drag from one column to another. This relationship can be saved in the Er Diagram and then used as a template for Queries.

Debbie


#7

Thanks for the information. The tables tab i can not use to create relationship because it is restricted/ privileges are denied. I was told by the tech person that we should be using the “view tab” to create relationship and build queries. Is that possible to do or not? Also can you define relationship in toad and how it is used? I am having difficulties working with it TOAD although it is user friendly i can see. Is there a toad for data analyst book in the market to use?

Thanks


#8

To use views you will need to draw your relationships/joins. Add the views to the diagram and drag between columns. See attached screenshot. When you are done push the send to query button on the wizard bar. This will send the views and joins to the Query Builder so you can add filters and execute the query.

You can save the Er Diagram as a *.ter file and add to the project manager for reuse or send to other associates for collaboration. Often the DBA or senior member of the team would make these diagrams as they are familiar with the schemas. Deploying them for building queries makes the discovery step much faster. The ER Diagram also has notes that can be attached to columns, tables or the while diagram. This also helps communicate to others the objects involved.

If you are not sure what views and columns to join you can use the Look-up Table tool or Object search to look for views with similiar column names. Most often join columns will have similiar names.

We do not currently have a book out for Toad for Data Analysts. But I’m here to help…
ERDiagram1.png


#9

You’re the BEST!!!

Thanks for eveything! I will most definitely contact you for any questions.

PS
What order topics you cover for TOAD for Data Analysts?


#10

Does it matter what direction the join is going?


#11

The direction of the join will determine the driving table. In the screenshot above the driving table is Contact. If you change the join direction the driving table will be Customer.

When you send the tables to the Query Builder and execute you will see an explain plan tab. Click on this to see how the optimizer is executing the query. For best optimization the driving table should be the table that returns the least number of rows. But now that puts into the SQL Tuning topic…
joinDirection.jpeg


#12

I pretty much can discuss all topics. If I don’t know the answer I will find someone on the team who does.


#13

Good morning Debbie

Is it possible to take a csv. excel file and dump it in toad to open it? The file is so huge i can not open it!


#14

You could do any of the following things:

  1. From TDA, click on the (3)Reports workflow button. Then using the Open Excel Wizard button, open the csv file using Excel.

  2. Define an ODBC connection to the csv file using the Microsoft text (*.csv) driver. Connect and query the file like a database.

  3. Import the file into an RDMS. To do this, use the Import Wizard from the Tools menu.

I would advise #3. Working with large amounts of data is best done by a relational database. If you can not create a table in Oracle or some other database you can use the Access Sample database (Toad Sample Database) that is bundled with the product. Once you have the data imported you can browse the data from the Database Explorer Data tab or query it from the Query Builder or Editor. I like the Data tab for basic filtering and exploring.

Debbie


#15

Hi Debbie

I have tried to import the csv file and i have gotten an error message. See attachment. I have tried using selection #3 which sounds the best thing to do, but it did not work.
Import Excel.doc (351 KB)


#16

How was the csv file exported? Is the first row the name of the columns?
If the cell was null is it omitted or an empty value entered?
Does the table you are imported it into match the data coming in?

The error says that a row has more or less columns then it is expecting. In the import wizard there is a screen to preview the csv data and another to map the columns from the file to the columns in the table. Do these columns match?

While it is available to create a new table from the csv, in the case I would suggest creating a new table before importing to have greater control on the column sizes.

Debbie
ImportPreview.jpeg


#17

How was the csv file exported? Is the first row the name of the columns?
If the cell was null is it omitted or an empty value entered?
Does the table you are imported it into match the data coming in?

The error says that a row has more or less columns then it is expecting. In the import wizard there is a screen to preview the csv data and another to map the columns from the file to the columns in the table. Do these columns match?

While it is available to create a new table from the csv, in the case I would suggest creating a new table before importing to have greater control on the column sizes.

Debbie
ImportColMap.jpeg


#18

Hi Deb
Is there a way I can select multiple months on my query or select multiple time hours as well? Do I have to write a formula or use the condition formula for it. Example: I need Mar 2009 and June 2009 for my comparison. Also for the hours I need to select 6pm and 6am.

Thanks


#19

Is there a way to select mutiple months or time in the query?


#20

I will investigate this today and let you know.