CODEWITHSUNDEEP
sqlsql-server
Bunion
Bunion

Reputation: 461

Divide by zero error encountered in SQL Server 2014

In this specific part of a query I'm trying to create, I'm getting a "divide by zero" error. I believe this is because I'm trying to divide ARTran.Qty by a null. I'm unsure how I can work around this... (I'm using Cast as in the next part of the query I'm trying to using this number as a decimal

(CASE WHEN ([ARTran].[UOM] LIKE 'EACH') 
         THEN CAST([ARTran].[Qty] AS VARCHAR(30)) 
      ELSE CAST((ARTran.Qty / InventoryItem.UsrLength) AS VARCHAR(30))
 END)

Can someone point me in the right direction? I've looked at a few other threads on this, but I'm still not sure how to solve this.

Upvotes: 1

Views: 4518

Answers (1)

Matt
Matt

Reputation: 15071

Use NULLIF to get around this.

(CASE WHEN ([ARTran].[UOM] LIKE 'EACH') 
      THEN CAST([ARTran].[Qty] AS VARCHAR(30)) 
      ELSE CAST((ARTran.Qty / NULLIF(InventoryItem.UsrLength, 0) AS VARCHAR(30))
END)

Upvotes: 3

Related Questions