Error converting data type varchar to numeric in sql-server 2005 - urgent

I am working on a stored proc to convert the comma delimiter to pipe delimiter. I noticed that any delimiter was not given at all the the code. So now I am adding another SELECT on the top of existing query to add the pipe delimiter.

1. Let me know if this works... The final data goes into .csv file
2. I am getting the error Error converting data type varchar to numeric, when I give pipe delimiter, How to solve it?
3. I had to remove the ORDER BY clause in the query as it is giving an error that ORDER BY can not be there in in-line queries. How can I add ORDER BY in this code?

The procedure code is as follows :

CREATE  PROCEDURE [dbo].[csp_Select_SKU_APOS_Data_test]

AS

SELECT SKU_No + ' | ' + SKU_Desc + ' | ' + List_Price + ' | ' + MOD_True_Cost + ' | ' + MOD_Brick_Cost  + ' | ' +
       MOD_SKU_Cost + ' | ' +  SKU_EOL_Date + ' | ' + Business_Unit_Desc + ' | ' + Business_Unit_Id + ' | ' + Parent_Channel_Desc + ' | ' + Parent_Channel_ID
FROM
(
SELECT     dbo.T_SKU_Fixed_Data.SKU_No,
         dbo.TLk_SKU_Desc.SKU_Desc,
           CAST(ROUND(dbo.T_SKU_BU_Parent_Channel.SKU_List_Price , 2) AS decimal(18, 2)) AS List_Price,
           SUM(CAST(ROUND(dbo.T_SKU_Mod.MOD_True_Cost, 2) AS decimal(18, 2))) AS MOD_True_Cost,
           SUM(CAST(ROUND(dbo.T_SKU_Mod.MOD_Brick_Cost , 2) AS decimal(18, 2))) AS MOD_Brick_Cost,
           SUM(CAST(ROUND(dbo.T_SKU_Mod.MOD_SKU_Cost , 2) AS decimal(18, 2))) AS MOD_SKU_Cost,
         CONVERT(Char(12), dbo.T_SKU_Fixed_Data.SKU_EOL_Date, 9) AS SKU_EOL_Date,
           dbo.T_Business_Unit.Business_Unit_Desc,
         dbo.T_Business_Unit.Business_Unit_Id,
         dbo.TLk_Parent_Channel.Parent_Channel_Desc,
         Parent_Channel_ID = CASE Parent_Channel_Desc
                              WHEN 'ENT' THEN ''
                              WHEN 'PAD' THEN '2MA_E'
                              WHEN 'PUB' THEN ''

                              WHEN 'HSB' THEN '2DD_E'  
                              ELSE Parent_Channel_Desc  END


FROM         dbo.T_SKU_Fixed_Data INNER JOIN
                      dbo.TLk_SKU_Desc ON dbo.T_SKU_Fixed_Data.SKU_Desc_RecId = dbo.TLk_SKU_Desc.SKU_Desc_RecId INNER JOIN
                      dbo.TLk_Option_Class ON dbo.T_SKU_Fixed_Data.Option_Class_RecId = dbo.TLk_Option_Class.Option_Class_RecId INNER JOIN
                      dbo.T_SKU_BU_Parent_Channel ON dbo.T_SKU_BU_Parent_Channel.Pre_SKU_No_RecId = dbo.T_SKU_Fixed_Data.Pre_SKU_No_RecId INNER JOIN
                      dbo.T_BU_Parent_Channel ON
                      dbo.T_SKU_BU_Parent_Channel.BU_Parent_Channel_RecId = dbo.T_BU_Parent_Channel.BU_Parent_Channel_RecId INNER JOIN
                      dbo.T_Business_Unit ON dbo.T_Business_Unit.BU_RecId = dbo.T_BU_Parent_Channel.BU_RecId INNER JOIN
                      dbo.TLk_Parent_Channel ON dbo.TLk_Parent_Channel.Parent_Channel_RecId = dbo.T_BU_Parent_Channel.Parent_Channel_RecId INNER JOIN
                      dbo.TLk_Currency ON dbo.TLk_Currency.Ccy_RecId = dbo.T_Business_Unit.Ccy_RecId INNER JOIN
                          (SELECT     Dollar_Hedge_Rate, Ccy_RecId
                            FROM          T_Currrency_Rate INNER JOIN
                                                   T_Fiscal_Year_Qtr ON T_Currrency_Rate.Fiscal_Yr_Qtr_RecId = T_Fiscal_Year_Qtr.Fiscal_Yr_Qtr_RecId
                            WHERE      GETDATE() BETWEEN T_Fiscal_Year_Qtr.Fiscal_Yr_Qtr_Start_Date AND Fiscal_Yr_Qtr_End_Date) A ON
                      A.Ccy_RecId = dbo.TLk_Currency.Ccy_RecId INNER JOIN
                      dbo.T_SKU_Mod ON dbo.T_SKU_Fixed_Data.Pre_SKU_No_RecId = dbo.T_SKU_Mod.Pre_SKU_No_RecId AND
                      dbo.T_SKU_BU_Parent_Channel.SKU_BU_Parent_Channel_RecId = dbo.T_SKU_Mod.SKU_BU_Parent_Channel_RecId

WHERE     (dbo.TLk_Option_Class.Option_Class = N'XYZ')

GROUP BY dbo.T_SKU_Fixed_Data.SKU_No, dbo.TLk_SKU_Desc.SKU_Desc,
                      CAST(ROUND(dbo.T_SKU_BU_Parent_Channel.SKU_List_Price, 2) AS decimal(18, 2)), CONVERT(Char(12),
                      dbo.T_SKU_Fixed_Data.SKU_EOL_Date, 9), dbo.T_Business_Unit.Business_Unit_Desc, dbo.T_Business_Unit.Business_Unit_Id,
                      dbo.TLk_Parent_Channel.Parent_Channel_Desc

HAVING      (dbo.TLk_Parent_Channel.Parent_Channel_Desc <> N'HSB') AND (NOT (dbo.T_SKU_Fixed_Data.SKU_No IS NULL)) AND
                      (dbo.T_Business_Unit.Business_Unit_Desc = N'United Kingdom')

UNION

SELECT     dbo.T_SKU_Fixed_Data.SKU_No,
         dbo.TLk_SKU_Desc.SKU_Desc,
           CAST(ROUND(dbo.T_SKU_BU_Parent_Channel.SKU_List_Price, 2) AS decimal(18, 2)) AS List_Price,
         SUM(CAST(ROUND(dbo.T_SKU_Mod.MOD_True_Cost , 2) AS decimal(18, 2))) AS MOD_True_Cost,
           SUM(CAST(ROUND(dbo.T_SKU_Mod.MOD_Brick_Cost , 2) AS decimal(18, 2))) AS MOD_Brick_Cost,
           SUM(CAST(ROUND(dbo.T_SKU_Mod.MOD_SKU_Cost, 2) AS decimal(18, 2))) AS MOD_SKU_Cost,
         CONVERT(Char(12), dbo.T_SKU_Fixed_Data.SKU_EOL_Date, 9) AS SKU_EOL_Date,
           dbo.T_Business_Unit.Business_Unit_Desc,
         dbo.T_Business_Unit.Business_Unit_Id,
         dbo.TLk_Parent_Channel.Parent_Channel_Desc,
            Parent_Channel_Desc_Id =CASE Parent_Channel_Desc
                              WHEN 'REL' THEN '2MA_E'  
                              WHEN 'HSB' THEN '2DD_E'  
                        ELSE Parent_Channel_Desc  END


FROM         dbo.T_SKU_Fixed_Data INNER JOIN
                      dbo.TLk_SKU_Desc ON dbo.T_SKU_Fixed_Data.SKU_Desc_RecId = dbo.TLk_SKU_Desc.SKU_Desc_RecId INNER JOIN
                      dbo.TLk_Option_Class ON dbo.T_SKU_Fixed_Data.Option_Class_RecId = dbo.TLk_Option_Class.Option_Class_RecId INNER JOIN
                      dbo.T_SKU_BU_Parent_Channel ON dbo.T_SKU_BU_Parent_Channel.Pre_SKU_No_RecId = dbo.T_SKU_Fixed_Data.Pre_SKU_No_RecId INNER JOIN
                      dbo.T_BU_Parent_Channel ON
                      dbo.T_SKU_BU_Parent_Channel.BU_Parent_Channel_RecId = dbo.T_BU_Parent_Channel.BU_Parent_Channel_RecId INNER JOIN
                      dbo.T_Business_Unit ON dbo.T_Business_Unit.BU_RecId = dbo.T_BU_Parent_Channel.BU_RecId INNER JOIN
                      dbo.TLk_Parent_Channel ON dbo.TLk_Parent_Channel.Parent_Channel_RecId = dbo.T_BU_Parent_Channel.Parent_Channel_RecId INNER JOIN
                      dbo.TLk_Currency ON dbo.TLk_Currency.Ccy_RecId = dbo.T_Business_Unit.Ccy_RecId INNER JOIN
                          (SELECT     Dollar_Hedge_Rate, Ccy_RecId
                            FROM          T_Currrency_Rate INNER JOIN
                                                   T_Fiscal_Year_Qtr ON T_Currrency_Rate.Fiscal_Yr_Qtr_RecId = T_Fiscal_Year_Qtr.Fiscal_Yr_Qtr_RecId
                            WHERE      GETDATE() BETWEEN T_Fiscal_Year_Qtr.Fiscal_Yr_Qtr_Start_Date AND Fiscal_Yr_Qtr_End_Date) A ON
                      A.Ccy_RecId = dbo.TLk_Currency.Ccy_RecId INNER JOIN
                      dbo.T_SKU_Mod ON dbo.T_SKU_Fixed_Data.Pre_SKU_No_RecId = dbo.T_SKU_Mod.Pre_SKU_No_RecId AND
                      dbo.T_SKU_BU_Parent_Channel.SKU_BU_Parent_Channel_RecId = dbo.T_SKU_Mod.SKU_BU_Parent_Channel_RecId

WHERE     (dbo.TLk_Option_Class.Option_Class = N'XYZ')

GROUP BY dbo.T_SKU_Fixed_Data.SKU_No, dbo.TLk_SKU_Desc.SKU_Desc,
                      CAST(ROUND(dbo.T_SKU_BU_Parent_Channel.SKU_List_Price, 2) AS decimal(18, 2)), CONVERT(Char(12),
                      dbo.T_SKU_Fixed_Data.SKU_EOL_Date, 9), dbo.T_Business_Unit.Business_Unit_Desc, dbo.T_Business_Unit.Business_Unit_Id,
                      dbo.TLk_Parent_Channel.Parent_Channel_Desc

HAVING      (NOT (dbo.T_SKU_Fixed_Data.SKU_No IS NULL)) AND
                      (dbo.T_Business_Unit.Business_Unit_Desc + dbo.TLk_Parent_Channel.Parent_Channel_Desc <> N'United KingdomENT') AND
                      (dbo.T_Business_Unit.Business_Unit_Desc + dbo.TLk_Parent_Channel.Parent_Channel_Desc <> N'United KingdomPAD') AND
                      (dbo.T_Business_Unit.Business_Unit_Desc + dbo.TLk_Parent_Channel.Parent_Channel_Desc <> N'United KingdomPUB')
) a
--ORDER BY dbo.T_SKU_Fixed_Data.SKU_No, dbo.T_Business_Unit.Business_Unit_Desc, dbo.TLk_Parent_Channel.Parent_Channel_Desc






Start Free Trial