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?
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
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?
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
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
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
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
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.
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
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
but i want to reverse view to know which table is related with this view.
can you advice me how to do that?
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)
