Subject | Re: [IBO] IB_WISQL problem (maybe) - the code |
---|---|

Author | Paul Little |

Post date | 2001-04-05T14:51:13Z |

Helen Borrie wrote:

corresponding left bracket e.g.

update fifthpercentile set DEPRECIATION = ((select sum(DEPRECIATION)

from incomestatement

where incomesattement.prd

= :curpnum)*0.05)

where period = :curpnum

(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

(3) No this SP is always called after the searched tables have been

populated. It will always be know exactly how many records are in these

tables and none of the fields will ever be null.

PL

>Understandable and entirely reasonable...

> 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...

>That's an odd number of right brackets, should there also be a

> 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;

corresponding left bracket e.g.

update fifthpercentile set DEPRECIATION = ((select sum(DEPRECIATION)

from incomestatement

where incomesattement.prd

= :curpnum)*0.05)

where period = :curpnum

>(1) possibly I guess.

> 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.

(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

(3) No this SP is always called after the searched tables have been

populated. It will always be know exactly how many records are in these

tables and none of the fields will ever be null.

>many thanks for the help

> Clues, anyway....

>

> Helen

PL