I have these fields in a table (translated fieldnames):

Count: integer default = 1;
Factor: float;
Price: float;
Sum: float generated (Count * Factor * Price);

When inserting some thousands of test records by using stored procedure
I noticed some hundred null-values in the field "Sum". I had a hard time
to find what caused the problem:

  Insert into posten (Count, Factor, Price) values (?, ?,?);
  execute stmt using 1, MyFactor, MyPrice;

works and gives the correct sum

whereas

  Insert into posten (Factor, Price) values (?,?);
  execute stmt using MyFactor, MyPrice;

works (Count = 1 by default) but the sum is null.

I guess it's a timing problem when the result of "sum" is calculated.

Regards Uli

Uli,

The default value is being stored in the column after the value for
"Sum" is generated, and I can't say if this is "as designed" or not -
Tim can later clarify this - meanwhile, you can set the generation
expression for the "Sum" column to:

COALESCE(Count, 1) * Factor * Price

--
Fernando Dias
[Team Elevate]

Fernando,

> COALESCE(Count, 1) * Factor * Price

Good workaround! My compliments. Thank you.

Regards Uli

Uli,

It is exactly as Fernando indicates.  However, I can change around the
sequence so that the defaults are populated first, but I will have to
double-check on this to make sure that there aren't any scenarios where this
could be a problem.  I don't think most people would need a default value
for a generated column, but it pays to be careful with this type of thing.


--
Tim Young
Elevate Software
www.elevatesoft.com

Tim
> It is exactly as Fernando indicates.  However, I can change around the
> sequence so that the defaults are populated first, but I will have to
> double-check on this to make sure that there aren't any scenarios where this
> could be a problem.  I don't think most people would need a default value
> for a generated column, but it pays to be careful with this type of thing.
>

Thanks. No need to change anything. With the coalesce-function it works
fine. It's just important to know about.

Regards Uli

Uli,

<< Thanks. No need to change anything. With the coalesce-function it works
fine. It's just important to know about. >>

Yeah, I've been beating this around today in my head, and it seems that
there might be a case where someone with a generated column will still want
a default value.  It would be rare, probably, but a generated column is
supposed to behave just like a normal column, except for the "generated"
part.

--
Tim Young
Elevate Software
www.elevatesoft.com