I have this Table-Valued Function and would like that all the amounts have 5 decimal places. How should this be done please?
set ANSI_NULLS ON set QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: <Author,,Name> -- Create date: <Create Date,,> -- Description: <Description,,> -- ============================================= ALTER FUNCTION [dbo].[f_DealerCommissions] --<Inline_Function_Name, sysname, FunctionName> ( @FromDate as datetime , @ToDate as datetime -- Add the parameters for the function here --<@param1, sysname, @p1> <Data_Type_For_Param1, , int>, --<@param2, sysname, @p2> <Data_Type_For_Param2, , char> ) RETURNS TABLE AS RETURN ( -- Add the SELECT statement with parameter references here select t0.DocNum , t0.DocDate , t0.CardCode , t0.CardName , t3.GroupName , t2.ListNum , t1.ItemCode , t9.ItmsGrpNam , t1.Dscription , t1.Quantity , t1.Price as 'TranPrice' , t1.LineTotal as 'Line Total' , t5.ListName as 'BPPrList' , t4.Price as 'BPPrice' , t6.ListName as 'RetailPrice' , t7.Price as 'RetPrice' , cast(((t7.Price - t4.Price) * t1.Quantity) as dec(19,5)) as 'PlannedComm' , cast(((t7.Price - t1.Price) * t1.Quantity) as dec(19,5)) as 'ActComm' from oinv as t0 inner join inv1 as t1 on t0.DocEntry = t1.DocEntry inner join ocrd as t2 on t0.CardCode = t2.CardCode inner join ocrg as t3 on t2.GroupCode = t3.GroupCode left outer join itm1 as t4 on t2.ListNum = t4.PriceList and t1.ItemCode = t4.ItemCode inner join opln as t5 on t2.ListNum = t5.ListNum inner join opln as t6 on t6.U_PLType = 'RET' left outer join itm1 as t7 on t6.ListNum = t7.PriceList and t1.ItemCode = t7.ItemCode left outer join oitm t8 on t1.ItemCode = t8.ItemCode left outer join oitb t9 on t8.ItmsGrpCod = t9.ItmsGrpCod --left outer join itm1 as t5 --on --and t1.ItemCode = t4.ItemCode where t3.GroupName in ('Prepaid Dealers', 'Distributors', 'Exclusive Dealers') and t0.DocDate >= @FromDate and t0.DocDate <= @ToDate )
Thanks a lot!
u can use cast or convert function of sql as given as above .