Subject Re: [ib-support] AVG() on char field.
Author Helen Borrie
At 01:29 PM 07-01-02 +0200, you wrote:
>Thanks Helen&Martin..
>
>Helen,
>That was a nice solution.
>But is it good to do something like this with 10millions of records?

Why not? You won't be doing it with 10,000,000 records all at once...it would be one at a time, when a row is inserted or updated...although if you already have 10M records, it will keep the db engine busy for a while doing this:

create procedure PopulateNumEquiv as
declare variable Handbag varchar(18);
declare variable SerNum numeric(18,0); /* or whatever the primary key is */
declare variable Dummy char(1);
begin
for select PK, RawEntry from aTable into :SerNum, :Handbag do
begin
update aTable set NumEquiv = cast(Handbag as Numeric(18,2))
where PK = :SerNum;
when any do /* just lose the error because the column is already null */
Dummy='X';
end
end

>Or is it a design error that I've done with the table?

Generally, if you have to perform arithmetic on a column, it shouldn't be character. You must have had a reason to make it character, yes?

Helen


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