Skip to Content

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

Decimal Places

Hi,

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!

Former Member
Former Member replied

Hi ,

u can use cast or convert function of sql as given as above .

Rgds,

Premraj

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