Skip to Content
author's profile photo
Former Member

Convert alphanum UDF to number in query

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'

Add comment
10|10000 characters needed characters exceeded

  • Follow
  • Get RSS Feed

1 Answer

  • Posted on May 04, 2011 at 08:32 PM

    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

    Add comment
    10|10000 characters needed characters exceeded