Results 1 to 5 of 5

Thread: Precision exceeded the allowable maximum

  1. #1
    Experienced Forum Member
    Join Date
    Oct 2005
    Location
    Florida
    Posts
    191
    Rep Power
    854

    Precision exceeded the allowable maximum

    On Sequel Server 2005 I am calling a procedure on the iseries using linked server:

    Code:
    insert into #JDE_EqmTable 
    Exec ('Call QGPL.get_eqmqty(?)', @JobNumber1) AT AS400SRV_MSDASQL
    My procedure is:

    Code:
    declare c1 scroll cursor with return for                          
    select  glsub, sum(decimal(glu * .01, 38, 2))                     
    from vgiprddta/f0911li                                            
    where glmcu = @jobnumber and                                      
    (globj = '63500 ' or globj = '73500 ' or globj = '73510 ') and    
    (gllt = 'AA' or gllt = 'AU') and                                  
    glexa = 'Equipment Distribution'                                  
    group by  glsub;                                                  
    open c1;

    I am getting error on "sum(decimal(glu * .01, 38, 2)) " saying:

    Msg 7354, Level 16, State 1, Line 36
    The OLE DB provider "STREAM" for linked server "(null)" supplied invalid metadata for column "00004". The precision exceeded the allowable maximum.

  2. # 666
    Circuit advertisement
    Join Date
    Aug 1965
    Location
    Yakutsk, Russia
    Posts
    1,000,000
     

  3. #2
    Programmer Mercury's Avatar
    Join Date
    Feb 2007
    Location
    Paris, France
    Posts
    1,831
    Rep Power
    5426

    Re: Precision exceeded the allowable maximum

    Try using precision 31 (max allowable) instead.
    Code:
    sum(decimal(glu * .01, 31, 2))
    The syntax of DECIMAL Built-in function is :
    Code:
    Character to Decimal
    DECIMAL or DEC ( character-expression, precision, scale )
    precision
    An integer constant that is greater than or equal to 1 and less than or equal to 31. If not specified, the default is 15.

    scale
    An integer constant that is greater than or equal to 0 and less than or equal to precision. If not specified, the default is 0.

    HTH
    Philippe

  4. #3
    Experienced Forum Member
    Join Date
    Oct 2005
    Location
    Florida
    Posts
    191
    Rep Power
    854

    Re: Precision exceeded the allowable maximum

    Mercury, thank you, I will try.

    The strange thing is that in the past I've been using 38 in other procs and it works fine, like below

    Code:
    select  gbsub, decimal(sum(gban01 + gban02 + gban03 +    
    gban04 + gban05 + gban06 + gban07 + gban08 +             
    gban09 + gban10 + gban11 + gban12 + gban13 +             
    gban14) * .01, 38, 2) as sum_qty                         
    from vgiprddta/f0902lc                                   
    where gbmcu = @jobnumber and                             
    gbobj = ' ' and                                          
    (gblt = 'AA' or gblt = 'AU')                             
    group by  gbsub;                                         
    open c1;
    So, I wonder what is so special with the proc I'm having problems with compared to the other ones? Just a question....

  5. #4
    Analyst
    Join Date
    Sep 2005
    Location
    Germany
    Posts
    1,590
    Rep Power
    9580

    Re: Precision exceeded the allowable maximum

    Hi,

    with release V5R4 the maximum precision was enhanced to 63 (before it was 31), but the default within CRTSQLRPGI or in other SQL environments is 31 and must be explicitely set to 63 (for examples with SET OPTION).

    Birgitta

  6. #5
    Programmer Mercury's Avatar
    Join Date
    Feb 2007
    Location
    Paris, France
    Posts
    1,831
    Rep Power
    5426

    Re: Precision exceeded the allowable maximum

    Good thing to know. Thanks Birgitta.
    Philippe

  7. # 666
    Circuit advertisement
    Join Date
    Aug 1965
    Location
    Yakutsk, Russia
    Posts
    1,000,000
     

Facebook Comments


Similar Threads

  1. Regarding Journaling
    By Sandeep D in forum RPG/RPGLE
    Replies: 3
    Last Post: November 16th, 2009, 07:37 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •