User Query Decimal Places

[Former Member]

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 · Accepted solution]

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

[Former Member · Accepted solution]

Pat, thank you SO much! That does the trick! Do you happen to know how to make the result align to the right (so the decimals align down the column)? Accountants are particular about such things.