Home » Other » General » SQL Statement into Diagram
icon9.gif  SQL Statement into Diagram [message #449862] Thu, 01 April 2010 12:33 Go to next message
aimy
Messages: 220
Registered: June 2006
Senior Member
Hello.

I'm just wondering why it is so hard for me to find a tool that could transform a SQL script into a meaningful diagram?

Does such tool never exists?

I was expecting a simple tool that could visualize this simple SQL statement into a diagram:
select a.empid, a.ename, a.job, b.salary 
from employee a, emp_details b
where a.empid=b.empid;


Thank you.
Re: SQL Statement into Diagram [message #449864 is a reply to message #449862] Thu, 01 April 2010 12:34 Go to previous messageGo to next message
aimy
Messages: 220
Registered: June 2006
Senior Member
In fact, the tool could be a standalone that need not to connect to the real database to create the diagram Cool
Re: SQL Statement into Diagram [message #449865 is a reply to message #449864] Thu, 01 April 2010 12:48 Go to previous messageGo to next message
Michel Cadot
Messages: 67950
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And what should be the diagram for your example?

Regards
Michel
icon12.gif  Re: SQL Statement into Diagram [message #449891 is a reply to message #449862] Fri, 02 April 2010 01:52 Go to previous messageGo to next message
aimy
Messages: 220
Registered: June 2006
Senior Member
OK, let me extend the query with a WHERE condition:

select a.empid, a.ename, a.job, b.salary 
from employee a, emp_details b
where a.empid=b.empid;


So, the diagram should be something like this:

http://www.glowfoto.com/static_image/01-234652L/7204/gif/04/2010/img6/glowfoto

Actually, of course the main objective here is to translate a more complex SQL statement into a diagram.

Thank you.
icon7.gif  Re: SQL Statement into Diagram [message #449893 is a reply to message #449862] Fri, 02 April 2010 01:55 Go to previous messageGo to next message
aimy
Messages: 220
Registered: June 2006
Senior Member
In case the image above is not visible...

/forum/fa/7660/0/

Thank you.
Re: SQL Statement into Diagram [message #449899 is a reply to message #449893] Fri, 02 April 2010 02:24 Go to previous messageGo to next message
Michel Cadot
Messages: 67950
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
How should be represented: order by, group by, connect by, flashback query, with subqueries when they are reused multiple times and so on?

Your case is very very simple.
Actually I don't see any utility of such tool. If the query is simple you have no use of it, if the query is complex the diagram should be too complex to also read it (if it is possible to generate it).

Regards
Michel
Re: SQL Statement into Diagram [message #449900 is a reply to message #449893] Fri, 02 April 2010 02:34 Go to previous messageGo to next message
Littlefoot
Messages: 21688
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Where did you find "working_years > 5" displayed under the EMP_DETAILS in your diagram? Where is it in a query? Or, heh, should this diagramming tool read your mind, too? /forum/fa/3971/0/
Re: SQL Statement into Diagram [message #449901 is a reply to message #449899] Fri, 02 April 2010 02:37 Go to previous messageGo to next message
aimy
Messages: 220
Registered: June 2006
Senior Member
Michel Cadot wrote on Fri, 02 April 2010 15:24
How should be represented: order by, group by, connect by, flashback query, with subqueries when they are reused multiple times and so on?

Your case is very very simple.
Actually I don't see any utility of such tool. If the query is simple you have no use of it, if the query is complex the diagram should be too complex to also read it (if it is possible to generate it).

Regards
Michel

Thanks your response.

Let me show the real example why I need this.

This whole SQL..
select
/*+ index(A, ADPO_CABC_IDX) index(B, ABM_CABC_IDX) index(AA, ADPI_PORT_IDX) index(A, ADPO_PORT_IDX) index(AA, ADPI_CABC_IDX) index(C, APV_EQUP_IDX) */
port_name, EQUP_LOCN_TTNAME||'_'||EQUP_index ADSLAM_IDENT_CODE,
case instr(PORT_NAME, '-P')
when 0 then '0000'
else substr(PORT_NAME,-4,4)
end DSLAM_Pair_num,
FRAA_POSITION ADSL_pair_num, FRAC_LOCN_TTNAME||' '||BB.FRAU_POSITION||' '||FRAU_POSITION
ADSL_BAR_IDENT_CODE,
FRAA_STATUS INVENT_STATUS_CODE1, a.CIRT_STATUS INVENT_STATUS_CODE2,
case when a.PORT_STATUS='BADPORT' or a.PORT_STATUS='LOCK' then a.PORT_STATUS
when a.CIRT_STATUS is not null then a.CIRT_STATUS
when a.CIRT_STATUS is null and FRAA_STATUS = 'INSERVICE' then 'SPARE'
else FRAA_STATUS end INVENT_STATUS_CODE,
EQUP_INDEX DSLAM_IND,
start_port START_PORT_NUM, end_port END_PORT_NUM, tonumeric(start_vci) START_VCI_NUM, tonumeric(end_vci) END_VCI_NUM,
case when instr(A.CIRT_DISPLAYNAME, 'SDSL') >0 then substr(A.CIRT_DISPLAYNAME,-16,8)
when instr(A.CIRT_DISPLAYNAME, 'MIPS') >0 then null
when instr(A.CIRT_DISPLAYNAME, 'MS') >0 then null
when instr(A.CIRT_DISPLAYNAME, 'ADSL') >0 then substr(A.CIRT_DISPLAYNAME,-11)
else substr(A.CIRT_DISPLAYNAME,1,11)
end SERVICE_NUM,
CARD_NAME CARD_TYPE,
case instr(PORT_NAME, '-V') when 0 then substr(PORT_NAME,instr(PORT_NAME,'-',-1,1)+1) else substr(PORT_NAME,instr(PORT_NAME,'-V',1)+2,4) end VCI_NUM,
-- nvl(POST_SERT_ABBREVIATION,CIRT_SERT_ABBREVIATION) PRODUCT_OWNERSHIP_CODE, 
PRODUCT_OWNERSHIP PRODUCT_OWNERSHIP_CODE, FRAC_fran_name FRAME_TYPE,
null cabinet_ident_code, null dpsdf_ident_code
from t_nis_adslmpr_dslam_pots_out A join t_nis_adslmpr_bar_mdf B on A.CACE_CABC_ID = B.CACE_CABC_ID
left join  t_NIS_adslmpr_dslam_pots_in AA on AA.PORH_PARENTID = A.PORT_ID
join T_NIS_adslmpr_bar_mdf BB on AA.CACE_CABC_ID = BB.CACE_CABC_ID
left join t_nis_adslmpr_port_vci C on C.EQUP_id = A.EQUP_id


could be turned into this:

/forum/fa/7662/0/
Re: SQL Statement into Diagram [message #449902 is a reply to message #449900] Fri, 02 April 2010 02:39 Go to previous messageGo to next message
aimy
Messages: 220
Registered: June 2006
Senior Member
Littlefoot wrote on Fri, 02 April 2010 15:34
Where did you find "working_years > 5" displayed under the EMP_DETAILS in your diagram? Where is it in a query? Or, heh, should this diagramming tool read your mind, too? /forum/fa/3971/0/

hahaha.. sorry first..

i just forgot to edit the query.

of course i do not expect that! Laughing
Re: SQL Statement into Diagram [message #449904 is a reply to message #449862] Fri, 02 April 2010 02:45 Go to previous messageGo to next message
aimy
Messages: 220
Registered: June 2006
Senior Member
Unfortunately, I could no longer edit that post.. Sad

Well, let me continue with my previous post..

So from that diagram.. One of the table i.e. T_NIS_ADSLMPR_BAR_MDF needs another diagram:

/forum/fa/7663/0/

The original script:
select FRAC_fran_name, FRAC_LOCN_TTNAME, FRAU_POSITION, FRAA_POSITION, fraa_status, FRAA_CIRT_NAME, 
cirt_displayname, cirt_status, fraa_cace_id, CACE_CABC_ID
from frame_containers join FRAME_UNITS on frac_id = frau_frac_id
join FRAME_APPEARANCES on FRAU_ID = FRAA_FRAU_ID
left join circuits on FRAA_CIRT_NAME = cirt_name
left join CABLE_CORE_ENDS on CACE_ID = FRAA_CACE_ID
where FRAU_NAME like 'DSL_IN%' or FRAU_NAME like 'DSL_OUT%' and FRAC_fran_name = 'MDF'
and FRAA_SIDE = 'REAR';


So I am tired of doing this so-called 'translation'.. Sad

But as you all can see, the diagram is 90% more readable rather than seeing the whole chunk of the complex codings.

So, my boss asked me to do this for the purpose of documentation.. Cool

Anyway, it is very useful to me as well to discuss with the other parties in a meeting.

Thanks.

[Updated on: Fri, 02 April 2010 02:49]

Report message to a moderator

Re: SQL Statement into Diagram [message #449906 is a reply to message #449904] Fri, 02 April 2010 03:08 Go to previous messageGo to next message
Littlefoot
Messages: 21688
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Well, I'm impressed! (I hope your boss feels the same as well) Unfortunately, I don't know such a tool so, as far as I'm concerned, you'll have to continue producing those wonderful diagrams manually. (Did you consider painting them? You know, tables blue, views green, primary keys red, etc.?)
Re: SQL Statement into Diagram [message #449910 is a reply to message #449901] Fri, 02 April 2010 03:38 Go to previous messageGo to next message
Michel Cadot
Messages: 67950
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Regarding your example in message #449901, do you really think the diagram is more understandable than the query itself?

Regarding your next example, may be if the query was well formatted and indented and commented, it would be easier to understand than the diagram. In addition, the explaination would then also be in the code itself and so easier to maintain.

Regards
Michel
Re: SQL Statement into Diagram [message #449913 is a reply to message #449910] Fri, 02 April 2010 03:53 Go to previous messageGo to next message
Littlefoot
Messages: 21688
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Just don't forget the basic difference, Michel: your boss and Aimy's boss is (probably) not the same person.
Re: SQL Statement into Diagram [message #449919 is a reply to message #449913] Fri, 02 April 2010 04:45 Go to previous messageGo to next message
Michel Cadot
Messages: 67950
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
your boss and Aimy's boss is (probably) not the same person.

Yes I can imagine what kind of person he/she is.
We have some architects we call "powerpoint architect" because as soon as they can and have put a design in a powerpoint presentation they "know" that it works.

Regards
Michel
Re: SQL Statement into Diagram [message #449924 is a reply to message #449919] Fri, 02 April 2010 05:50 Go to previous messageGo to next message
ThomasG
Messages: 3211
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Of course the "real example" query you posted is pretty unreadable. But when you just format it more sensibly (perhaps with the SQL Formatter), it becomes quite readable:

SELECT /*+ index(A, ADPO_CABC_IDX)  index(B, ABM_CABC_IDX)
           index(AA, ADPI_PORT_IDX) index(A, ADPO_PORT_IDX) 
           index(AA, ADPI_CABC_IDX) index(C, APV_EQUP_IDX) */
       port_name,
       equp_locn_ttname
        || '_'
        || equp_index       adslam_ident_code,
       CASE Instr(port_name, '-P')
         WHEN 0 THEN '0000'
         ELSE Substr(port_name, -4, 4)
       END                  dslam_pair_num,
       fraa_position        adsl_pair_num,
       frac_locn_ttname
        || ' '
        || bb.frau_position
        || ' '
        || frau_position    adsl_bar_ident_code,
       fraa_status          invent_status_code1,
       a.cirt_status        invent_status_code2,
       CASE
         WHEN a.port_status = 'BADPORT'
               OR a.port_status = 'LOCK' THEN a.port_status
         WHEN a.cirt_status IS NOT NULL THEN a.cirt_status
         WHEN a.cirt_status IS NULL
              AND fraa_status = 'INSERVICE' THEN 'SPARE'
         ELSE fraa_status
       END                  invent_status_code,
       equp_index           dslam_ind,
       start_port           start_port_num,
       end_port             end_port_num,
       Tonumeric(start_vci) start_vci_num,
       Tonumeric(end_vci)   end_vci_num,
       CASE
         WHEN Instr(a.cirt_displayname, 'SDSL') > 0 THEN Substr(a.cirt_displayname, -16, 8)
         WHEN Instr(a.cirt_displayname, 'MIPS') > 0 THEN NULL
         WHEN Instr(a.cirt_displayname, 'MS') > 0 THEN NULL
         WHEN Instr(a.cirt_displayname, 'ADSL') > 0 THEN Substr(a.cirt_displayname, -11)
         ELSE Substr(a.cirt_displayname, 1, 11)
       END                  service_num,
       card_name            card_type,
       CASE Instr(port_name, '-V')
         WHEN 0 THEN Substr(port_name, Instr(port_name, '-', -1, 1) + 1)
         ELSE Substr(port_name, Instr(port_name, '-V', 1) + 2, 4)
       END                  vci_num,
       -- nvl(POST_SERT_ABBREVIATION,CIRT_SERT_ABBREVIATION) PRODUCT_OWNERSHIP_CODE, 
       product_ownership    product_ownership_code,
       frac_fran_name       frame_type,
       NULL                 cabinet_ident_code,
       NULL                 dpsdf_ident_code
FROM   t_nis_adslmpr_dslam_pots_out a
       JOIN t_nis_adslmpr_bar_mdf b
         ON a.cace_cabc_id = b.cace_cabc_id
       LEFT JOIN t_nis_adslmpr_dslam_pots_in aa
         ON aa.porh_parentid = a.port_id
       JOIN t_nis_adslmpr_bar_mdf bb
         ON aa.cace_cabc_id = bb.cace_cabc_id
       LEFT JOIN t_nis_adslmpr_port_vci c
         ON c.equp_id = a.equp_id  


Which (at least for me) is much more readable than the diagram.

[Updated on: Fri, 02 April 2010 05:55]

Report message to a moderator

Re: SQL Statement into Diagram [message #449931 is a reply to message #449924] Fri, 02 April 2010 06:59 Go to previous messageGo to next message
Michel Cadot
Messages: 67950
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I agree, above all if you organize the conditions/selected exporessions in logical/functional order and also add comments on the purpose of each part (maybe refering to a paragraph of specifications document if there is any).

Regards
Michel
Re: SQL Statement into Diagram [message #449933 is a reply to message #449931] Fri, 02 April 2010 07:35 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
I disagree to a certain point: Some people just cannot read code. They are "visually oriented", so they can understand images and abstractions, but not lines and lines of text that make no sense to them.
Very often you find this type of people in management positions or in the "design" layer.

However, I already said that I disagree to a certain point. On the one hand I think these people probably should not (want to) get involved into seeing/understanding the query; they should abstract to functionality rather than the implementation.
On the other hand will very complex queries still result in very complex diagrams.

Just my two cents.
Re: SQL Statement into Diagram [message #506268 is a reply to message #449933] Sat, 07 May 2011 15:26 Go to previous message
pascal_
Messages: 4
Registered: April 2005
Junior Member
The tool exists, it is called Reverse snoflakes join (rvj on sourceforge) and as a demo site on snowflakejoins dot com

Here is a simplified example of the result
/forum/fa/8942/0/

Regards
PAscal
Previous Topic: Oracle Partner Network
Next Topic: SYSAUX Tablespace
Goto Forum:
  


Current Time: Fri Sep 24 20:06:02 CDT 2021