Getting percentage of a number with 5 decimal points

Question

I am working on a inventory table with a field of Estimated Years Life of the item. I would like to get the % Month Depreciation of the Estimated Years Life field.

SUPPLY_TABLE

Field  SUPPLY_ITEM SUPPLY_LIFEYEARS  GET_MONTHDEPRE_PERCENT(Desired-output)
         Item A           20                   0.41667%
         Item B           5                    1.66667%

I tried running a formula in excel for the % Month Depreciation of the Estimated Years Life field and it displays a correct data. But when I tried applying the formula in the select procedure it displays different and wrong result.

Excel formula = (1/(20*12))*100

This my sample select procedure

CREATE PROCEDURE AAAAAAAAAAA_SAMPLE
RETURNS(
  SUPPLY_LIFEYEARS DECIMAL(12, 2),
  GET_MONTHDEPRE_PERCENT DECIMAL(12, 5))
AS
BEGIN
  FOR
    SELECT   
      SUPPLY_LIFEYEARS,
      (1/(SUPPLY_LIFEYEARS*12)) * 100

    FROM SUPPLY A
    INTO
      :SUPPLY_LIFEYEARS,
      :GET_MONTHDEPRE_PERCENT

  DO
    BEGIN
      SUSPEND;
    END
END;

Output of this select procedure (actual result)

SUPPLY_LIFEYEARS   GET_MONTHDEPRE_PERCENT
     20                     0
     5                      1

Desired or expected output

SUPPLY_LIFEYEARS   GET_MONTHDEPRE_PERCENT
     20                     0.41667%
     5                      1.66667%

Your problem is probably that you are doing integer arithmetic because all the values are integers. Try changing the `1` in `(1/(SUPPLY_LIFEYEARS*12)) * 100` to `1.0` — Nick, May 17 '19 at 04:03
Possible duplicate of [How to get a float result by dividing two integer values?](https://stackoverflow.com/questions/11719044/how-to-get-a-float-result-by-dividing-two-integer-values) — Nick, May 17 '19 at 05:19
Possible duplicate of [SQL Force show decimal values](https://stackoverflow.com/questions/18987484/sql-force-show-decimal-values) — Mark Rotteveel, May 17 '19 at 09:39
why would you do a `Stored Procedure` here ? adding a `Computed By` column to the table seems to be easier there: `alter table SUPPLY add GET_MONTHDEPRE_PERCENT computed by (100.0/12/SUPPLY_LIFEYEARS); Select * from SUPPLY` — Arioch 'The, May 17 '19 at 10:32
@Arioch'The Agreed, and when adding a computed column is not an option, the stored procedure as shown would be better as a view. — Mark Rotteveel, May 17 '19 at 11:36
@MarkRotteveel deleting a table and creating a view with the table's name might not be an option due to FB's black-n-white dependency system. If something is dependent upon a table - you can not atomically commit "drop table + create view", you are stuck. I just recently stuck with a field in a legacy app, which holds parameters description to user-made formulas. They mixed different domain-specific aspects into one enumeration type, then they keep list of parameters annotated with indexes of enum values as VARCHAR(255). I "normalize" the enum into two types and want to store constant literals — Arioch 'The, May 17 '19 at 12:28
Because, well, decoupling the enum field would need removing few values in-between and indices would cease being valid, standard fragility problem. Now, that would require extending field to VarChar(380), while 90% of rows would only have 30-40 characters there. Granted, normal solution would be introducing details table of parameters per formula and maybe dictionary of enum values literals too, yet... I can not drop the table easily. I can make computed field using `list` that would be building this string from d-table, but not trigger that would parse NEW.COMPUTED_FIELD into d-table — Arioch 'The, May 17 '19 at 12:31
So until I can track all places in legacy app that reads/writes that field and rework them all, I am suck with extending VARCHAR.... It is sad FB tables can not have COMPUTED fields made explicitly modifiable by triggers... — Arioch 'The, May 17 '19 at 12:33
@Arioch'The I was actually talking about replacing the stored procedure in question with a view, not about replacing the table with a view. And regarding the computer fields thingy. I suggest you file an improvement ticket for `GENERATED BY DEFAULT AS (expression)` support in the tracker. — Mark Rotteveel, May 17 '19 at 14:19
@MarkRotteveel do not think they would truly consider it, partially because it would require very well thought out SQL syntax change, and the one that only addresses one niche case. Little chance it would ever be done — Arioch 'The, May 17 '19 at 16:33

Getting percentage of a number with 5 decimal points

0 Answers0