Query Decimal rounding

Question

Query Decimal rounding

andrew 64169

Grasshopper

Points: 19
More actions
December 13, 2016 at 6:32 pm

#316782

Hi All, i am hoping for some assistance with an rounding issue i have with my current trigger. any assistance would be greatly appreciated.
'BEGIN ' + #13 +
' SELECT cast(STDBUYPRICE as decimal(18,2)) ' + #13 +
' FROM itemmaster ' + #13 +
' WHERE ITEMCODE = NEW.linecode' + #13 +
' into new.ADDITIONALFIELD_5;' + #13 + ++++THIS FIELD AT THE MOMENT HAS 10 DECIMAL PLACES, I NEED ONLY 4+++++++
' ' + #13 +
' NEW.ADDITIONALFIELD_6 = ((CAST(new.ADDITIONALFIELD_5 AS DECIMAL (18,2)) * NEW.PERBATCHQTY));' + #13 +
'END;'; ++++++THIS FIELD AT THE MOMENT HAS 10 DECIMAL PLACES, I NEED ONLY 4++++++++++++
ExecuteSQL(SQLStr);
end.
end

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

HanShi SSC-Dedicated Points: 33511 More actions · Answer 1

andrew 64169 (12/13/2016)
Hi All, i am hoping for some assistance with an rounding issue i have with my current trigger. any assistance would be greatly appreciated.
'BEGIN ' + #13 +
' SELECT cast(STDBUYPRICE as decimal(18,2)) ' + #13 +
' FROM itemmaster ' + #13 +
' WHERE ITEMCODE = NEW.linecode' + #13 +
' into new.ADDITIONALFIELD_5;' + #13 + ++++THIS FIELD AT THE MOMENT HAS 10 DECIMAL PLACES, I NEED ONLY 4+++++++
' ' + #13 +
' NEW.ADDITIONALFIELD_6 = ((CAST(new.ADDITIONALFIELD_5 AS DECIMAL (18,2)) * NEW.PERBATCHQTY));' + #13 +
'END;'; ++++++THIS FIELD AT THE MOMENT HAS 10 DECIMAL PLACES, I NEED ONLY 4++++++++++++
ExecuteSQL(SQLStr);
end.
end

Sorry, I don't recognize the code you're posting although there is some SQL in it. But i will try to provide an answer:

Is it possible the number of decimals is defined in the datatype of the "new.ADDITIONALFIELD_5" and the "NEW.ADDITIONALFIELD_6" object?

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

kevaburg SSCoach Points: 18131 More actions · Answer 2

Sorry, your code isn't really that easy to read.

Is there a reason you aren't ROUNDing the decimal numbers you want to format?

Query Decimal rounding

Cookies on SQLServerCentral