March 11, 2003 at 2:19 pm
I want to update my pricing table but I am having a problem with the rounding issues. I can not change the field type which is a
data type Numberic with a legnth of 9 with a precision 19 and a scale of 5 I want the number to look like this 555.54 in the database. How would I change the statement to round this and just putting round around the statemt fixes some and others it does not.
NewPrice = RGPSOPRC * SOPPriceIncrease.AMOUNT
This does not work it fixes some but intern gives some that look like this
NewPrice = ROUND(RGPSOPRC * SOPPriceIncrease.AMOUNT,2)
17.71999
This is the update statement I am going to use
UPDATE SOP90002 SET RGPSOPRC = RGPSOPRC * SOPPriceIncrease.AMOUNT, RGPSTPRC = RGPSTPRC * SOPPriceIncrease.AMOUNT, RGPDSPRC = RGPDSPRC * SOPPriceIncrease.AMOUNT
FROM SOP90002 JOIN SOPPriceIncrease ON SOP90002.CUSTNMBR = SOPPriceIncrease.CUSTNMBR AND SOP90002.SALSTERR = LTRIM(SOPPriceIncrease.SALSTERR) WHERE SOPPriceIncrease.INCREASE = 1
March 12, 2003 at 2:10 am
Is NewPrice defined as numeric?
Can you post data types for all fields (NewPrice,RGPSOPRC,SOPPriceIncrease.AMOUNT etc) and sample (non sensitive) data as well to show what you are getting and what you expect.
Far away is close at hand in the images of elsewhere.
Anon.
March 12, 2003 at 7:25 am
Dave:
The answer to the question is to use the cast command. This is what the statement should look like. Thanks for responding to my question.
UPDATE SOP90002 SET RGPSOPRC = CAST(ROUND(RGPSOPRC * SOPPriceIncrease.AMOUNT,2) as money), RGPSTPRC = CAST(ROUND(RGPSTPRC * SOPPriceIncrease.AMOUNT,2) as money), CAST(ROUND(RGPDSPRC = RGPDSPRC * SOPPriceIncrease.AMOUNT,2) as money)
FROM SOP90002 JOIN SOPPriceIncrease ON SOP90002.CUSTNMBR = SOPPriceIncrease.CUSTNMBR AND SOP90002.SALSTERR = LTRIM(SOPPriceIncrease.SALSTERR) WHERE SOPPriceIncrease.INCREASE = 1
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply