At 11:54 AM 07-01-02 +0200, Arda Tunccekic wrote:

>Hi , I have a field that user enter data like;
>
>"POZITIVE",
>"5.34",
> "+++",
> "3.43",
> "7.00",
> "NEGATIVE",
>
>This field mainly stores numeric values, but users also enter data as
>characters as needed.
>
>Now I need to get the average of valid values. Obviously, I can't use AVG()
>on these.
>I have done this. I get all the data to the client, and calculate the
>average by iterating the rows.
>Of course I know it's not the best way.
>
>Is it possible to do this with stored procedures?
>Is there any keywords that validate the numeric values in stored procedures?

A trigger that automatically populates a "helper" column would be a route to take. Define a suitable column (e.g. NumEquiv Numeric ( 18,4 )) for your column (e.g. RawEntry varchar( 18 )) and make insert and update triggers that do this:

create trigger BI_populate_helper for aTable
active before insert as
begin
new.NumEquiv = cast(new.RawEntry as Numeric( 18,4 );
when any do
new.NumEquiv = NULL;
end

create trigger BU_populate_helper for aTable
active before update as
begin
if new.RawEntry <> old.RawEntry then
begin
new.NumEquiv = cast(new.RawEntry as Numeric( 18,4 );
when any do
new.NumEquiv = NULL;
end
end

If you need the non-numeric RawEntry values to be included in the averages, then you would need to store zero instead of NULL in the NumEquiv column.

cheers,
Helen

All for Open and Open for All
Firebird Open SQL Database · http://firebirdsql.org
_______________________________________________________