At 03:51 PM 05-04-01 +0100, you wrote:

>Helen Borrie wrote:
> >
> > At 02:36 PM 05-04-01 +0100, you wrote:
> > >Ok, so this list (or is it Yahoogroups) trims off attachments....
> >
> > That is by design - it avoids the problem we were having with list members unknowingly posting viruses to the list...
>
>Understandable and entirely reasonable...
>
> >
> > This looks like a repeating error in the code:
> >
> > update fifthpercentile set DEPRECIATION = (select sum(DEPRECIATION)
> > from incomestatement
> > where
> > incomestatement.prd = :curpnum)*0.05
> > where period = :curpnum;
> >
> > Needs to be
> >
> > update fifthpercentile set DEPRECIATION = (select sum(DEPRECIATION)
> > from incomestatement
> > where
> > incomestatement.prd = :curpnum)*0.05 ) <-- missing right bracket
> > where period = :curpnum;
>
>That's an odd number of right brackets, should there also be a
>corresponding left bracket e.g.
>
>update fifthpercentile set DEPRECIATION = ((select sum(DEPRECIATION)
>from incomestatement
> where incomesattement.prd
>= :curpnum)*0.05)
>where period = :curpnum

Correct. You must isolate the where clause of the subselect from the where clause of the update statement.

> >
> > Also, I think there is potential for these subselects (1) to be non-scalar and/or (2) for there to be a data type mismatch occurring as a result of performing the multiplication and/or (3) for no match to occur because of precision problems, i.e. you could be trying to operate on NULLs.
>
>(1) possibly I guess.
>(2) Not likely in this instance - this is just my hamfisted way of
>obtaining the 5% (and the 25%, 75% and 95%) value of the data in the
>incomestatement (and balansheet and accountingratios) table - it's a
>shame there is not a percentage function in SQL like avg

Ahem, likely (read CERTAIN) because the calculation will change the scale of the result. You would need to recast the result back to match the precision of the column into which you are writing it.


H.
All for Open and Open for All
InterBase Developer Initiative · http://www.interbase2000.org
_______________________________________________________