Oracle database experiences

Oracle database blog

Plotting graph with SQL*Plus – Part III

Enhancement

The query described in the Part I, does not allow to have a proportional y axis. You will find below an example with the function y=1/x for x belonging to [0.01,1.1].

SQL> @plot_graph4
  +100.0 *.............................................................................................................
   +50.0 .*............................................................................................................
   +33.3 ..*...........................................................................................................
   +25.0 ...*..........................................................................................................
   +20.0 ....*.........................................................................................................
   +16.6 .....*........................................................................................................
   +14.2 ......*.......................................................................................................
   +12.5 .......*......................................................................................................
   +11.1 ........*.....................................................................................................
   +10.0 .........*....................................................................................................
    +9.0 ..........*...................................................................................................
    +8.3 ...........*..................................................................................................
    +7.6 ............*.................................................................................................
    +7.1 .............*................................................................................................
    +6.6 ..............*...............................................................................................
    +6.2 ...............*..............................................................................................
    +5.8 ................*.............................................................................................
    +5.5 .................*............................................................................................
    +5.2 ..................*...........................................................................................
    +5.0 ...................*..........................................................................................
    +4.7 ....................*.........................................................................................
    +4.5 .....................*........................................................................................
    +4.3 ......................*.......................................................................................
    +4.1 .......................*......................................................................................
    +4.0 ........................*.....................................................................................
    +3.8 .........................*....................................................................................
    +3.7 ..........................*...................................................................................
    +3.5 ...........................*..................................................................................
    +3.4 ............................*.................................................................................
    +3.3 .............................*................................................................................
    +3.2 ..............................*...............................................................................
    +3.1 ...............................*..............................................................................
    +3.0 ................................*.............................................................................
    +2.9 .................................*............................................................................
    +2.8 ..................................*...........................................................................
    +2.7 ...................................**.........................................................................
    +2.6 .....................................*........................................................................
    +2.5 ......................................**......................................................................
    +2.4 ........................................*.....................................................................
    +2.3 .........................................**...................................................................
    +2.2 ...........................................**.................................................................
    +2.1 .............................................**...............................................................
    +2.0 ...............................................***............................................................
    +1.9 ..................................................**..........................................................
    +1.8 ....................................................***.......................................................
    +1.7 .......................................................***....................................................
    +1.6 ..........................................................****................................................
    +1.5 ..............................................................****............................................
    +1.4 ..................................................................*****.......................................
    +1.3 .......................................................................*****..................................
    +1.2 ............................................................................*******...........................
    +1.1 ...................................................................................*******....................
    +1.0 ..........................................................................................**********..........
     +.9 ....................................................................................................**********

54 rows selected.

Furthermore, it does not allow to display y-axis values in a specific range.

Modified query

The following query allows to display values on the y axis in a specific range. It displays the function y=cos(x) for x belonging to [0.1,11] and y belonging to [-0.9,1.0].

SELECT y_axis,
  listagg(decode(y1_val,1,'*','.'),'')  WITHIN GROUP (ORDER BY x_val) graph_val
FROM
  (SELECT tab_val.x_val,
    tab_y_axis.y_axis,
    decode(tab_y_axis.y_axis,tab_val.y_val,1,0) y1_val
  FROM
    (SELECT LEVEL x_val,
      trunc(cos((LEVEL/10)),1) y_val
    FROM dual
      CONNECT BY LEVEL <= 110
    ) tab_val
  CROSS JOIN
    (SELECT trunc((LEVEL/10)-1,1) y_axis FROM dual CONNECT BY LEVEL <= 20
    ) tab_y_axis
  )
GROUP BY y_axis
ORDER BY 1 DESC;

Result

SQL> @plot_graph_e
         1 ..............................................................................................................
        .9 ****......................................................*********...........................................
        .8 ....**..................................................**.........**.........................................
        .7 ......*...............................................**.............*........................................
        .6 .......**............................................*................**......................................
        .5 .........*..........................................*...................*.....................................
        .4 ..........*........................................*.....................*....................................
        .3 ...........*......................................*.......................*...................................
        .2 ............*....................................*.........................*..................................
        .1 .............*..................................*...........................*.................................
         0 ..............**..............................**.............................**.............................**
       -.1 ................*............................*.................................*...........................*..
       -.2 .................*..........................*...................................*.........................*...
       -.3 ..................*........................*.....................................*.......................*....
       -.4 ...................*.....................**.......................................*.....................*.....
       -.5 ....................**..................*..........................................*...................*......
       -.6 ......................*................*............................................**................*.......
       -.7 .......................*.............**...............................................*.............**........
       -.8 ........................**.........**..................................................**.........**..........
       -.9 ..........................*********......................................................*********............

20 rows selected.
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: