On Sequel Server 2005 I am calling a procedure on the iseries using linked server:
My procedure is:Code:insert into #JDE_EqmTable Exec ('Call QGPL.get_eqmqty(?)', @JobNumber1) AT AS400SRV_MSDASQL
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.
Try using precision 31 (max allowable) instead.
The syntax of DECIMAL Built-in function is :Code:sum(decimal(glu * .01, 31, 2))
precisionCode:Character to Decimal DECIMAL or DEC ( character-expression, precision, scale )
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.
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.
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
So, I wonder what is so special with the proc I'm having problems with compared to the other ones? Just a question....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;
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).
Good thing to know. Thanks Birgitta.