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

[Benoit Minisini]

On lundi 26 janvier 2009, richard terry wrote:
> 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
>

I just want to suggest the following: add a numeric field in your table so 
that you store the data both in text form and numeric form. 

When you have to store the result field in a record, you have to convert it to 
a number and update the numeric field accordingly.

Then your range queries should be easier to do.

Regards,

-- 
Benoit Minisini