TDA

shaimaa · January 21, 2011, 5:12pm

i have some questions if anyone can help me:

1-how i can get graphical report if i want to get report for example about ER diagram for database?

2-if i have different databases and i want to query info from different databases for example i want to query data exist in different databases…how can i make this?

3-if i have view and i want to see from which tables this view get data(i want to see that graphically as i see relations between tables?

Debbie_Peabody · January 21, 2011, 7:08pm

The Er Diagram report seems to be broken in 2.7. Sorry CR81712.
See help files for hetrogenious queries. This is done by using the Query Builder. Connect to first datasource and drag table onto Query Builder. Then change connections to second datasource and drag on table. Add columns and run. This will join two disparate databases.
To graphically show view, point to view from the Object palette. Go to the script tab and copy the select statement. Put into editor and send to Query BUilder. The Query Builder will reverse engineer the SQL and show the table relationships in the top part of the Query Builder.

Debbie

shaimaa · January 21, 2011, 7:37pm

Thanks alot debbie for your reply
about the third question i tried to follow your steps but it is not successful done and appear the attached screenshot..can you review it?

Debbie_Peabody · January 21, 2011, 7:44pm

You would need to look on the Query tab to see more details. But the first thing I would check is being able to exectue it in the editor before you send it to the Query Builder. Does the Select statement execute? If not that needs to be resolved first. You may need to change the current schema if tables are not fully qualified. (Or add the schema name to the table.)

Debbie

shaimaa · January 21, 2011, 9:15pm

i tried test it in Editor before send it to query builder and it is executed … the below script for view which i want revers engineer for view which i want graphical diagram for this view and it’s relation with other tables

CREATE OR REPLACE FORCE VIEW “HR”.“EMP_DETAILS_VIEW”
(
“EMPLOYEE_ID”,
“JOB_ID”,
“MANAGER_ID”,
“DEPARTMENT_ID”,
“LOCATION_ID”,
“COUNTRY_ID”,
“FIRST_NAME”,
“LAST_NAME”,
“SALARY”,
“COMMISSION_PCT”,
“DEPARTMENT_NAME”,
“JOB_TITLE”,
“CITY”,
“STATE_PROVINCE”,
“COUNTRY_NAME”,
“REGION_NAME”
)
AS
SELECT e.employee_id,
e.job_id,
e.manager_id,
e.department_id,
d.location_id,
l.country_id,
e.first_name,
e.last_name,
e.salary,
e.commission_pct,
d.department_name,
j.job_title,
l.city,
l.state_province,
c.country_name,
r.region_name
FROM employees e,
departments d,
jobs j,
locations l,
countries c,
regions r
WHERE e.department_id = d.department_id
AND d.location_id = l.location_id
AND l.country_id = c.country_id
AND c.region_id = r.region_id
AND j.job_id = e.job_id
WITH READ ONLY

Debbie_Peabody · January 21, 2011, 9:24pm

e.job_id,

e.manager_id,

e.department_id,

d.location_id,

l.country_id,

e.first_name,

e.last_name,

e.salary,

e.commission_pct,

d.department_name,

j.job_title,

l.city,

l.state_province,

c.country_name,

r.region_name

HR.departments d,

HR.jobs j,

HR.locations l,

HR.countries c,

HR.regions r

AND d.location_id = l.location_id

AND j.job_id = e.job_id

AND c.region_id = r.region_id

i woudl try this SQL. I have added the HR schema to each table and took off WITH READ ONLY.

SELECT e.employee_id,FROM HR.employees e,WHERE e.department_id = d.department_idAND l.country_id = c.country_id

shaimaa · January 22, 2011, 4:14pm

Hi Debbie
I tried added the HR schema to each table and took off WITH READ ONLY as you said but also appear the same message

Debbie_Peabody · January 22, 2011, 5:36pm

That’s odd. It executes in the editor and reverse engineers for me. What version of TDA are you using? I am using 2.7. This SQL is not very complicated. I have listed below the only things that can cause it issues.

Not schema referencesing the tables.
Query Builder not recognizing non Ansi joins(there is a toggle button in the Query Builder. Older versions of TDA had to have this manually set.
The query uses synonyms that are not resolving.

Check those things. Then send to the QB again and go to the Query Tab. The red sqwiggle lines will tell you what part it is having problems with. I can reproduce the error when I take out the HR references.

Debbie

shaimaa · January 23, 2011, 6:01am

i am using version 2.7
can you check the attached screenshot

Debbie_Peabody · January 23, 2011, 7:13pm

The Query Builde can only reverse engineer Select statements. The screenshot shows the whole View statement. Take the SQL I posted on Jan 21 and copy into an editor, then send to query builder. That should resovle things.

Debbie

shaimaa · January 24, 2011, 3:14am

but i want to reverse view to know which table is related with this view.
can you advice me how to do that?

Debbie_Peabody · January 24, 2011, 2:39pm

A view is the result set of an executed SQL statement. So if you use the SQL statement in the Query Builder it will give the tables, columns, and references that made up the view. Try using the attached Query Builder file.
HRViewTables.tsm (109 KB)