[Gambas-user] Help with converting text in field to number

[richard terry]

As a prefix to this post all contuctive criticism of my flaws/poor logic etc 
here welcomed (with open arms!). I hope this is not too confusing, which it 
is likely to be as I don't really know what I'm doing, so its hard to 
explain, but in essence:

I've a table containing my lab results which is as follows and rightly or 
wrongly I've kept all results initially in a text field as the hl7 I'm 
dealing with the value field can be numerical or text:

CREATE TABLE clin_requests.requests_results
(
  pk serial NOT NULL,
  episode_key integer NOT NULL, -- episode key is unique to a group of results
  						         -- all of a fbc
  result text,		 -- the actual value eg could be potassium or white cell count
  "value" text,					 -- could be eg 134 or 1.1 or  text
  units text, 					 -- the units for the above
  reference_range text,		 -- the reference range
  abnormal boolean,			 -- if true the result is abnormal
  probability text,				 -- ?currently not used
  obx text,		 -- obx= the entire pipe (|) delimited Hl7 row for this result
  CONSTRAINT requests_results_pkey PRIMARY KEY (pk)
)

Now that I can import my data I want to be able to graph it, for example look 
at the number of patients with hba1c (a diabetic measurement) between certain 
ranges, dates etc.

I pull this information out of a view:

I want to end up with a list of data like this by using union queries for each 
range I want (is there a better way to do this?) which I can pass to the 
graphing tool

xaxes-text|count|display_order
-------------------------------------------
 5-6             5               1
 6-7           100            2
over 7       10              3

this query:

select      
      '5-6.0' as XAxes_text, count (value) as count,'1' as display_order From 
clin_requests.vwResults  where fk_lu_request = 872

gives for example
xaxes-text|count|display_order
6-6.5            100            1

But obviously the query needs a bit more as that is all the values in the 
view, not the specific range I want so  I have to be able to convert the 
value field to a number and then test if that number is between my ranges.

I tried various combinations and permutation of the above base query plus 
using the function  to_number("value","9D9"), to no avail.

It must be a matter of correct syntax.

Any help appreciated, plus any help pointing me to any references about 
graphing values in postgres (besides the doc's which I have - ie a tutoral 
would be useful.

Regards, and thanks in anticipation.

Richard