Guest User

Guest User

  • Tech Writer
  • 2.1k
  • 480.5k

How to fix this sql error Error converting data type nvarchar?

Jun 13 2024 1:04 PM

Hi Team

I want to write this query and getting this sql exception for using Error converting data type nvarchar to numeric. Where can improve this query?

WITH PremixCalculation AS (
    SELECT 
        Adr_Batch_Log.MatDesc,
        Adr_Batch_Log.MatCode,
        Adr_Batch_Log.Shift,
        Adr_Batch_Log.Batched,
        CASE 
            WHEN Adr_Batch_Log.MatCode LIKE 'MR%' THEN 1 * COALESCE(CAST(NULLIF(Adr_Batch_Log.MatUnit, '') AS DECIMAL(18, 3)), 0) * Adr_Batch_Log.Shift * Adr_Batch_Log.Batched
            WHEN Adr_Batch_Log.MatCode LIKE 'RB%' THEN 25 * COALESCE(CAST(NULLIF(Adr_Batch_Log.MatUnit, '') AS DECIMAL(18, 3)), 0) * Adr_Batch_Log.Shift * Adr_Batch_Log.Batched
            ELSE 0
        END AS Premix
    FROM 
        Adroit_Batch_Suite.dbo.Adr_Batch_Log Adr_Batch_Log
    WHERE 
        Adr_Batch_Log.dt >= '2024-06-01' AND Adr_Batch_Log.dt < '2024-06-02'
        AND (Adr_Batch_Log.MatCode LIKE 'MR%' OR Adr_Batch_Log.MatCode LIKE 'RB%')
)
SELECT 
    MatDesc,
    SUM(Premix) AS TotalPremix
FROM 
    PremixCalculation
GROUP BY 
    MatDesc;

 


Answers (1)