Tool for making diagram from SQL query

Question

I have this complicated SQL query for Oracle that I want to visualize in a diagram to make it understandable for my co-workers. I tried at http://snowflakejoins.com but it just chokes on it.

Has someone a better suggestion? I prefer a web-app on the internet and if not a desktop app for windows.

with 
  logs as (
    select 
      l.job_id, 
      l.subjob,
      sum(l.verwerkt) verwerkt, 
      sum(l.errors) errors, 
      max(l.datum) laatst
     from 
      dinf_monitor_logs l, 
      dinf_monitor_jobs j 
     where 
      l.datum>sysdate-j.dagen
      and j.job_id=l.job_id(+)
     group by 
      l.job_id, 
      l.subjob
  ),
  alllogs as (
    select job_id, subjob, max(datum) laatst from dinf_monitor_logs group by job_id, subjob
  )
  select row_number() over(order by alllogs.job_id, alllogs.subjob) r,
    alllogs.job_id,
    alljobs.naam,
    alllogs.subjob,
    logs.verwerkt, 
    logs.errors, 
    alllogs.laatst datum,
    alljobs.wikilink,
    alljobs.loglink,
    alljobs.contact,
    case 
      when alllogs.laatst is null then 1
      when round(sysdate-(alllogs.laatst+alljobs.dagen))<0 then 0
      else round(sysdate-(alllogs.laatst+alljobs.dagen))
    end overtijd,
    case 
      when logs.errors-alljobs.max_errors>0 then 5
      when logs.verwerkt-alljobs.min_verwerkt<0 then 7
      when round(sysdate-(alllogs.laatst+alljobs.dagen))>0 then 3
      else 11
    end status
  from logs, alllogs, (select job_id, naam, wikilink, loglink, contact, dagen, min_verwerkt, max_errors from dinf_monitor_jobs) alljobs
  where 
    logs.job_id(+)=alllogs.job_id 
    and logs.subjob(+)=alllogs.subjob
    and alllogs.job_id=alljobs.job_id
  order by alllogs.job_id, alllogs.subjob

Have you tried the "Query Builder" tab of the Oracle SQL Developer? — , Jan 20 '12 at 16:27
No, some colleagues use SQL developer it but i use Toad, does that just build or can you reverse engineer a diagram from a query ? Most of the ERD programs just build a database structure. — peter, Jan 20 '12 at 22:39
If you create the query and change the tab to "query builder" the tool will create the diagram. — , Jan 23 '12 at 17:52
thanks, but you first have to create the query in developer, you can't start with the sql above ? — peter, Jan 24 '12 at 21:08
see the output of your query: http://imageshack.us/photo/my-images/41/sqldeveloperoutput.png/ — , Jan 25 '12 at 12:17
great ! is a relatively new feature i believe, my collegues who use developper didn't know that was possible. If you add this as an answer i will accept it. — peter, Jan 25 '12 at 15:21
Cool! Yeap, I think this is a new feature. I posted an answer. — , Jan 25 '12 at 15:45

score 3 · Accepted Answer · answered Jan 25 '12 at 15:43

3

You can use the "Query Builder" tab of the Oracle's SQL Developer.

The result of your sample query will be:

query in query builder

answered Jan 25 '12 at 15:43

score 0 · Answer 2 · answered Jan 20 '12 at 19:43

0

Each of the sub queries are data sets, I would just make a plain English statement of what the query does, then describe the data sets and how they relate to one another in an entity-relationship manner, then show how the query satisfies the plain English statement. You can represent the E-R with any variety of tools.

answered Jan 20 '12 at 19:43

Roger Cornejo

1,507
1
8
7

1

seems like a lot of work and you know, a picture (diagram) says more than a thousand words, also would prefer something reusable – peter Jan 20 '12 at 22:40

score 0 · Answer 3 · answered Jan 26 '12 at 15:33

Have found how to do it in Toad, which i prefer above Sql Developer. Open the editorwindow, paste the sql, rightclick in the editorwindow and select "Send to queryviewer" My sql above is too complicated to use this technique but it's nice to know i can use it in the future with more "normal" queries. Points to Sergio.

Tool for making diagram from SQL query

3 Answers3