Skip to Content

Archived discussions are read-only. Learn more about SAP Q&A

User Query Decimal Places

In the query below, T1.Price has 4 decimal places and T3.NumInSale has 4 decimal places but 'SalesPrice' (the result of multiplying these values) only has 2 decimal places. I have tried Cast based on information found in other threads but the Convert you see in the query is the only thing that does not return an error. The problem is: I still only get 2 decimal places. Can anyone offer advice please? Thank you in advance.

SELECT T0.CardCode, T2.CardName, T0.ItemCode, T3.ItemName,'Price/InvUOM'= T1.Price, T3.InvntryUOM AS 'InvUOM', T3.NumInSale AS 'Items/SalesUOM', 'USD/CAN'= T1.Currency, convert(numeric(10,4),(T1.Price*T3.NumInSale)) as 'SalesPrice', T3.SalUnitMsr, T1.FromDate AS 'BegDate', T1.ToDate AS 'EndDate'

FROM OSPP T0 LEFT OUTER JOIN SPP1 T1 ON T0.ItemCode = T1.ItemCode and T0.CardCode = T1.CardCode

INNER JOIN OCRD T2 ON T0.CardCode = T2.CardCode

INNER JOIN OITM T3 ON T0.ItemCode = T3.ItemCode

WHERE T2.CardType = 'C' and (T1.ToDate > getdate() OR T1.ToDate IS NULL)

ORDER BY T2.CardName, T3.ItemName, T0.ItemCode

Former Member
Former Member replied

Lorna,

Try this:

SELECT T0.CardCode, T2.CardName, T0.ItemCode, T3.ItemName,'Price/InvUOM'= T1.Price, T3.InvntryUOM AS 'InvUOM', T3.NumInSale AS 'Items/SalesUOM', 'USD/CAN'= T1.Currency, Cast(Cast(T1.Price*T3.NumInSale AS numeric (19,5)) AS nvarchar(100)) as 'SalesPrice', T3.SalUnitMsr, T1.FromDate AS 'BegDate', T1.ToDate AS 'EndDate'

FROM OSPP T0 LEFT OUTER JOIN SPP1 T1 ON T0.ItemCode = T1.ItemCode and T0.CardCode = T1.CardCode

INNER JOIN OCRD T2 ON T0.CardCode = T2.CardCode

INNER JOIN OITM T3 ON T0.ItemCode = T3.ItemCode

WHERE T2.CardType = 'C' and (T1.ToDate > getdate() OR T1.ToDate IS NULL)

ORDER BY T2.CardName, T3.ItemName, T0.ItemCode

0 View this answer in context
Not what you were looking for? View more on this topic or Ask a question