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

The error "Error converting data type nvarchar to numeric" typically arises when there is an attempt to convert non-numeric strings to a numeric type. In this case, the issue is likely due to the CAST(NULLIF(Adr_Batch_Log.MatUnit, '') AS DECIMAL(18, 3)) part of the query. If Adr_Batch_Log.MatUnit contains non-numeric values or empty strings, this conversion will fail.

To handle this, you can use a CASE statement to ensure that only numeric values are converted. Here’s how you can adjust your query:

1. Use TRY_CAST to safely attempt the conversion to a numeric type. If the conversion fails, TRY_CAST will return NULL instead of throwing an error.
2. Ensure MatUnit is numeric before attempting the conversion.
   Here is the revised 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(TRY_CAST(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(TRY_CAST(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;
   

   In this query: TRY_CAST attempts to convert Adr_Batch_Log.MatUnit to DECIMAL(18, 3). If the conversion fails, it returns NULL.COALESCE then handles these NULL values by converting them to 0.This approach ensures that any non-numeric values in MatUnit do not cause the query to fail.