[owtchart-users] not so elegant query

[Carlo A. Bertelli]

Hello,

I use owtchart to produce charts -- it should be obvious -- on statistical
database results.
As I understand the documentation, the following could be a first answer.
It works, even if it's owful. Could someone suggest a better one?

In the following meta-script you have mixed html and sql lines; it
shouldn't be difficult to understand:

------------------------ begin owtchart meta-script
------------------------------

html <img src=/cgi-bin/owtchart.cgi?Type=3DBar&W=300&H=200
	&XTitle=mesi&XLabelFont=T&YTitle=n.&XLabelFont=T&NumSets=1&NumPts=

sql select
	to_char(count(distinct to_char(date_time_posted,'yyyy-mm'))+1)
    from v_urp
    where
	date_time_posted BETWEEN ADD_MONTHS(sysdate,-6)
		AND sysdate
    order by date_time_posted asc

html &Vals=

sql select
	to_char(count(*))||'!'
    from v_urp
    where
	date_time_posted BETWEEN ADD_MONTHS(sysdate,-6)
		AND sysdate
    group by to_char(date_time_posted, 'yyyy-mm')
    order by to_char(date_time_posted, 'yyyy-mm') asc

html &XLabels=

sql select
	to_char(date_time_posted,'yyyy-mm')||';'
    from v_urp
    where
	date_time_posted BETWEEN ADD_MONTHS(sysdate,-6)
		AND sysdate
    group by to_char(date_time_posted, 'yyyy-mm')
    order by to_char(date_time_posted, 'yyyy-mm') asc

html >

------------------------ end owtchart meta-script
------------------------------

Just to be explicit:
* every line beginning with html is a part of html
* every line beginning with sql is a query
* every indented line continues the preceding html tag or sql query

About the environment:
* the table (in fact a view) is names V_URP;
* the only column which is used is DATE_TIME_POSTED

Performing the queries results in the following owtchart 'script' (I still
keep aside html and sql results for clarity)

html <img src=/cgi-bin/owtchart.cgi?Type=3DBar&W=300&H=200
	&XTitle=mesi&XLabelFont=T&YTitle=n.&XLabelFont=T&NumSets=1&NumPts=
sql  7
html &Vals=
sql  1723!1312!3175!4211!3973!3877!
html &XLabels=
sql  2002-07;2002-08;2002-09;2002-10;2002-11;2002-12;
html >

Aside from grouping and ordering, which should be obvious, I also use some
conversion (to_char) and a kludge (+1). As you could note:

NumPts=7 (i.e. I declare 7 points, when I have 6; I add it in the query)
	but
Vals=1723!1312!3175!4211!3973!3877! (6 plus an empty one, sorry an unuseful
separator)
	and so the labels
XLabels=2002-07;2002-08;2002-09;2002-10;2002-11;2002-12; (6 labels plus an
unuseful separator).

The chart has an empty space for a bar and a missing label, but there is no
conditional construct (which I should avoid). Better than nothing...

Could someone suggest a better way to get rid of those unuseful separators?
Is there a way in owtchart to use simple sql queries and still getting the
work done?

TIA
	Carlo
--------------------------------------------------------------------------
Carlo A. Bertelli
   Charta   servizi e sistemi per il territorio e la storia ambientale srl
            Dipendenze del palazzo di Clemente Doria,
            vc. alla Chiesa della Maddalena 9/2 16124       Genova (Italy)
            tel. +39 010 2475439  fax +39 010 2475439  mobile: 333 2298345
    e-mail: bertelli@charta.acme.com      http://www.charta.acme.com
--------------------------------------------------------------------------