Divide by zero error encountered.

Question

Error is generating ,when dividing by 0

Create table #Containerno (CID int,ConWEight varchar(50),Container_Rate decimal(10,4),Trucking_Charges  decimal(10,4),)  
  
  
insert into #Containerno values(444,' ', 22.01,5555)   
  
 SELECT  convert(numeric(10,2),(isnull(nullif(c.ConWeight,''),0)*c.Container_Rate)+c.Trucking_Charges)/isnull(nullif(c.ConWeight,''),0) as Invoice_Amount  
 FROM  #Containerno c  

Answer 1

Hi @Analyst_SQL
As we all know: the divisor cannot be zero.
So when ConWeight is ' ', the divisor will be zero. Therefore, you could do this modification:

SELECT  convert(numeric(10,2),(isnull(nullif(c.ConWeight,''),0)*c.Container_Rate)+c.Trucking_Charges)/isnull(nullif(c.ConWeight,''),1) as Invoice_Amount  
FROM  #Containerno c  

Best regards,
LiHong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Error is generating ,when dividing by 0

Of course, a "division by zero" error. Check the divider first on 0:

SELECT  CASE WHEN isnull(nullif(c.ConWeight,''),0) 0 0 THEN NULL  
        ELSE convert(numeric(10,2),(isnull(nullif(c.ConWeight,''),0)*c.Container_Rate)+c.Trucking_Charges)/ isnull(nullif(c.ConWeight,''),0)   
        END as Invoice_Amount  
  FROM  #Containerno c