precision issue in uniface and sybase

Author: lalitpct@gmail.com (lalitpct)

Hi all,

while passing value from uniface forms to sybase database I am having trouble with the precision , I know this can be more off envisonment issue then coding one .

But if any one has got a way to deal with it please let me know if you have better way of handling

Just before the write trigger I check the values which were as below

e adjs_eop_balance_lcy.financials_extra =0.000000000000909494701772928
e adjs_eop_balance_fcy.financials_extra =0.000000000000909494701772928

and while inserting  to database

 

(WRIT) WRIT:6  write         --------------> here when i check values for adjs_eop_balance_lcy.financials_extra and adjs_eop_balance_fcy.financials_extra they are 0.000000000000909494701772928
Server: I/O function: O, mode: 0, on file/table: FINANCIALS_EXTRA
Server:     field  -> FINANCIALS_EXTRA pos 0 len 0 0 type G0 G  pack 90 scalfmt 1 csfmt 0 dbfmt 0 index 0
Server:     field  -> COUNTRY          pos 0 len 2 0 type S1 S  pack 0 scalfmt 0 csfmt 0 dbfmt 0 index 1
Server:     field  -> BRANCH           pos 2 len 2 0 type N1 N  pack 10 scalfmt 0 csfmt 10 dbfmt 73 index 1
Server:     field  -> SEQUENCE         pos 4 len 4 0 type N1 N  pack 10 scalfmt 0 csfmt 15 dbfmt 75 index 1
Server:     field  -> TURNOVER_BS_DR_F pos 8 len 8 0 type N1 F  pack 11 scalfmt 2 csfmt 39 dbfmt 78 index 0
Server:     field  -> TURNOVER_BS_CR_F pos 16 len 8 0 type N1 F  pack 11 scalfmt 2 csfmt 39 dbfmt 78 index 0
Server:     field  -> TURNOVER_PL_DR_L pos 24 len 8 0 type N1 F  pack 11 scalfmt 2 csfmt 39 dbfmt 78 index 0
Server:     field  -> TURNOVER_PL_CR_L pos 32 len 8 0 type N1 F  pack 11 scalfmt 2 csfmt 39 dbfmt 78 index 0
Server:     field  -> UNADJ_PROFIT_LOS pos 40 len 8 0 type N1 F  pack 11 scalfmt 2 csfmt 39 dbfmt 78 index 0
Server:     field  -> REVS_PROFIT_LOSS pos 48 len 8 0 type N1 F  pack 11 scalfmt 2 csfmt 39 dbfmt 78 index 0
Server:     field  -> ADJS_PROFIT_LOSS pos 56 len 8 0 type N1 F  pack 11 scalfmt 2 csfmt 39 dbfmt 78 index 0
Server:     field  -> UNADJ_EOP_BALANC pos 64 len 8 0 type N1 F  pack 11 scalfmt 2 csfmt 39 dbfmt 78 index 0
Server:     field  -> REVS_EOP_BALANCE pos 72 len 8 0 type N1 F  pack 11 scalfmt 2 csfmt 39 dbfmt 78 index 0
Server:     field  -> ADJS_EOP_BALANCE pos 80 len 8 0 type N1 F  pack 11 scalfmt 2 csfmt 39 dbfmt 78 index 0
Server:     field  -> UNADJ_EOP_BALANC pos 88 len 8 0 type N1 F  pack 11 scalfmt 2 csfmt 39 dbfmt 78 index 0
Server:     field  -> REVS_EOP_BALANCE pos 96 len 8 0 type N1 F  pack 11 scalfmt 2 csfmt 39 dbfmt 78 index 0
Server:     field  -> ADJS_EOP_BALANCE pos 104 len 8 0 type N1 F  pack 11 scalfmt 2 csfmt 39 dbfmt 78 index 0
Server:     field  -> UNADJ_AVG_BAL_DR pos 112 len 8 0 type N1 F  pack 11 scalfmt 2 csfmt 39 dbfmt 78 index 0
Server:     field  -> REVS_AVG_BAL_DR_ pos 120 len 8 0 type N1 F  pack 11 scalfmt 2 csfmt 39 dbfmt 78 index 0
Server:     field  -> ADJS_AVG_BAL_DR_ pos 128 len 8 0 type N1 F  pack 11 scalfmt 2 csfmt 39 dbfmt 78 index 0
Server:     field  -> UNADJ_AVG_BAL_CR pos 136 len 8 0 type N1 F  pack 11 scalfmt 2 csfmt 39 dbfmt 78 index 0
Server:     field  -> REVS_AVG_BAL_CR_ pos 144 len 8 0 type N1 F  pack 11 scalfmt 2 csfmt 39 dbfmt 78 index 0
Server:     field  -> ADJS_AVG_BAL_CR_ pos 152 len 8 0 type N1 F  pack 11 scalfmt 2 csfmt 39 dbfmt 78 index 0
Server:     field  -> DR_POOL_CODE     pos 160 len 1 0 type N1 N  pack 10 scalfmt 0 csfmt 9 dbfmt 72 index 0
Server:     field  -> CR_POOL_CODE     pos 161 len 1 0 type N1 N  pack 10 scalfmt 0 csfmt 9 dbfmt 72 index 0
Server:     field  -> UPDATE_USER_ID   pos 162 len 2 0 type N1 N  pack 10 scalfmt 0 csfmt 11 dbfmt 73 index 0
Server:     field  -> UPDATE_DATE      pos 164 len 8 0 type E1 E  pack 0 scalfmt 0 csfmt 115 dbfmt 112 index 0
Server:     field  -> U_VERSION        pos 172 len 1 0 type S1 S  pack 0 scalfmt 0 csfmt 0 dbfmt 0 index 0
Server:     index  - > 1.1-1 field  COUNTRY
Server:     index  - > 1.2-1 field  BRANCH
Server:     index  - > 1.3-1 field  SEQUENCE
Server: /* Table existence check */
Server: if not exists (select 1 from sysalternates)
Server: begin select sysobjects.type, sysobjects.uid
Server:  from sysobjects, sysusers
Server:  where sysobjects.name = 'financials_extra'
Server:  and sysobjects.uid = sysusers.uid
Server:  and sysusers.suid = suser_id()
Server: end
Server: else begin
Server:
Server:  select sysobjects.type, sysobjects.uid
Server:  from sysobjects, sysusers, sysalternates
Server:  where sysobjects.name = 'financials_extra'
Server:  and sysobjects.uid = sysusers.uid
Server:  and (sysusers.suid = suser_id()
Server:      or (sysusers.suid = sysalternates.altsuid
Server:           and sysalternates.suid = suser_id() ) )
Server: end
Server: if @@rowcount = 0
Server: begin
Server:  select sysobjects.type, sysobjects.uid
Server:  from sysobjects, sysusers
Server:  where sysobjects.name = 'financials_extra'
Server:  and sysobjects.uid = sysusers.uid
Server:  and sysusers.name ='dbo'
Server: end
Server:
Server: I/O function: U, mode: 0, on file/table: FINANCIALS_EXTRA length: 173
Server: if (@@trancount = 0) begin transaction
Server: update financials_extra
Server: set turnover_bs_dr_fcy = -033247.56, turnover_bs_cr_fcy = 33247.56, turnover_pl_dr_lcy = 0.0, turnover_pl_cr_lcy = 0.0, unadj_pr
Server: ofit_loss_lcy = 0.0, revs_profit_loss_lcy = 0.0, adjs_profit_loss_lcy = 0.0, unadj_eop_balance_lcy = 0.0, revs_eop_balance_lcy =
Server:  0.0,adjs_eop_balance_lcy = 9.09, unadj_eop_balance_fcy = 0.0, revs_eop_balance_fcy = 0.0, adjs_eop_balance_fcy = 9.09, unadj_a
Server: vg_bal_dr_lcy = 0.0, revs_avg_bal_dr_lcy = 0.0, adjs_avg_bal_dr_lcy = -033247.56, unadj_avg_bal_cr_lcy = 0.0, revs_avg_bal_cr_lc
Server: y = 0.0, adjs_avg_bal_cr_lcy = 33247.56, dr_pool_code = 0, cr_pool_code = 0, update_user_id = 39, update_date = '04/05/2011 05:1
Server: 9:01.00', u_version = 'y'
Server: where ((country = 'ES') and (branch = 315) and (sequence = 558)) and ((u_version = 'x'))
Server:
(WRIT) WRIT:7  done<end of module>

 

as you can see 0.000000000000909494701772928 become 9.09 I think it makes the value as 9.09E something and then E (exponential  part ) is ignored  while passing it to sybase

 

 

 

 

 

 

5 Comments

  1. Can you please check (send us ) the defintion of the field in modell

    data type = ?

    interface  = ?

    display format = ?

     

     

     


    Author: istiller (i2stiller@gmx.de)
  2. data type = ?floating decimal point

    interface  = ? C32.2

    display format = ?DIS(-zzz,zzz,zzz,zzz,zz9Pzzz)

     


    Author: lalitpct (lalitpct@gmail.com)
  3. I was thinking of passing it as string and then sybase will take entire value , but in that  case sybase will thow error as in the table it has been defined as float

     


    Author: lalitpct (lalitpct@gmail.com)
  4. Hi

    There is a mismatch between your defintion and the data you want to store

    Neither your interface defintion nor your display format fits the value in your variable.

    For your interface just use F and leave the display empty (in modell and form)

     

    Explanation:

    UnifAce only know a few data type in the program language and this do not have any limitation.

    So you can have 0.000000000000909494701772928 in into with out an error or truncation. But when it comes to storing, UnifAce "remebers" the defintion and store only the part you have defined. In your case both defintions say: "Only two digits behind the point" and let UnifAce try to scale it.

    ------ Extract from manuell -------

     Scaling

    The syntax for scaling with the C packing code is:

    C m{. n}

    where m is the total length of the field to be stored in bytes and n is the (optional) number of digits after the decimal. If n is present, a period (.) must separate n from m.

    Uniface maps the C packing code for numeric data to the most suitable numeric storage format in the underlying DBMS.

     

    ---------------------------

     

    Ingo


    Author: istiller (i2stiller@gmx.de)
  5. ... looks you want to force uniface to store a two-part info (mantissa plus exponent) into a single  storage.
    So the exponent information is lost and you will get only the mantissa back.

    But I think you should have got a warning during "analyse model", because of the improper combination.

    I JUST CHECKED IT WITH 8.4.06, you will NOT get a warning, worth placing a call?

    As Ingo stated, the interface for a floating point should be one of the "F" family.

    Uli


    Author: ulrich-merkel (ulrichmerkel@web.de)