Convert alphanum UDF to number in query

[Former Member]

Hi,

I'm including a SUM on a UDF in a query I've created. The UDF has to be set up as alphanumeric since it would not be a round number (ex. 495.50). I'm getting an error on the SUM portion and thinking I need to convert the UDF to number field in the query - how would I do this?

Thank you!

Heather

Here is the query:

SELECT T0.ContractID, T0.CstmrName, T1.U_Site, T2.ItmsGrpNam], T1.StartDate, T1.EndDate], T1.U_Rentype, T1.U_Freq, T1.U_RRate], T1.U_User], T1.U_RenStatus] FROM OCTR T0 INNER JOIN CTR1 T1 ON T0.ContractID = T1.ContractID INNER JOIN OITB T2 ON T1.ItemGroup = T2.ItmsGrpCod WHERE T0.Status ='A'

UNION ALL

SELECT '', '', '', '', NULL, NULL, 'Total', '', SUM(T1.U_RRate), '', ''

FROM OCTR T0 INNER JOIN CTR1 T1 ON T0.ContractID = T1.ContractID INNER JOIN OITB T2 ON T1.ItemGroup = T2.ItmsGrpCod

WHERE T0.Status ='A'

[kvbalakumar]

Hi Heather,

Try this

SELECT T0.ContractID, T0.CstmrName, T1.U_Site, T2.[ItmsGrpNam], T1.StartDate, T1.[EndDate], T1.U_Rentype, T1.U_Freq, CAST(T1.[U_RRate] as INT), T1.[U_User], T1.[U_RenStatus] FROM OCTR T0 INNER JOIN CTR1 T1 ON T0.ContractID = T1.ContractID INNER JOIN OITB T2 ON T1.ItemGroup = T2.ItmsGrpCod WHERE T0.Status ='A' 
UNION ALL
SELECT '', '', '', '', NULL, NULL, 'Total', '', SUM(CAST(T1.U_RRate as Int)), '', ''
FROM OCTR T0 INNER JOIN CTR1 T1 ON T0.ContractID = T1.ContractID INNER JOIN OITB T2 ON T1.ItemGroup = T2.ItmsGrpCod 
WHERE T0.Status ='A'

OR

SELECT T0.ContractID, T0.CstmrName, T1.U_Site, T2.[ItmsGrpNam], T1.StartDate, T1.[EndDate], T1.U_Rentype, T1.U_Freq, CONVERT(INT, T1.[U_RRate]), T1.[U_User], T1.[U_RenStatus] FROM OCTR T0 INNER JOIN CTR1 T1 ON T0.ContractID = T1.ContractID INNER JOIN OITB T2 ON T1.ItemGroup = T2.ItmsGrpCod WHERE T0.Status ='A' 
UNION ALL
SELECT '', '', '', '', NULL, NULL, 'Total', '', SUM(CONVERT(INT,T1.U_RRate)), '', ''
FROM OCTR T0 INNER JOIN CTR1 T1 ON T0.ContractID = T1.ContractID INNER JOIN OITB T2 ON T1.ItemGroup = T2.ItmsGrpCod 
WHERE T0.Status ='A'

Regards,

Bala