ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

Precision exceeded the allowable maximum

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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
    Re: Precision exceeded the allowable maximum

    Try using precision 31 (max allowable) instead.
    Code:
    sum(decimal(glu * .01, [COLOR=red][B]31[/B][/COLOR], 2))
    The syntax of DECIMAL Built-in function is :
    Code:
    [U]Character to Decimal[/U]
    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

    Comment


    • #3
      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....

      Comment


      • #4
        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

        Comment


        • #5
          Re: Precision exceeded the allowable maximum

          Good thing to know. Thanks Birgitta.
          Philippe

          Comment

          Working...
          X