CODEWITHSUNDEEP
sql-server
behi behi
behi behi

Reputation: 137

How can prevention of Divide by zero error encountered in sql server?

I'm new in sql server and write this query:

SELECT y.Telno,
         t.Cycle+'-'+y.Cycle Cycle,
        (((y.CurBill - t.CurBill)/t.CurBill)*100) Price FROM [ClubEatc].[dbo].[GetOnlineBills] y INNER JOIN
 [ClubEatc].[dbo].[GetOnlineBills] t
     ON y.Telno = t.TelNo AND CAST(y.Cycle as int)-1 = CAST(t.Cycle as int)


in table [GetOnlineBills] in field CurBill some place save zero,and the i get zero divid error,want when CurBill equals zero then just show 100% in result,how can i implant that?thanks all.

Upvotes: 1

Views: 5609

Answers (3)

Jens
Jens

Reputation: 3299

-- Returns 0 when divided by 0
SELECT ISNULL(<Numerator> / NULLIF(<Denominator>, 0), 0);

-- Returns the numerator of your division when divided by 0
SELECT ISNULL(<Numerator> / NULLIF(<Denominator>, 0), <Numerator>);

Both options work fine, you just gotta pick what suits your situation best.

Upvotes: 1

Sandip - Frontend Developer
Sandip - Frontend Developer

Reputation: 14669

Check with 0 value and if found replace it with y.CurBill

SELECT 
    y.Telno,
    t.Cycle+'-'+y.Cycle Cycle,
    (((y.CurBill - t.CurBill)/(CASE ISNULL(t.CurBill,0) WHEN 0 THEN y.CurBill ELSE t.CurBill END))*100) Price 
FROM [ClubEatc].[dbo].[GetOnlineBills] y 
INNER JOIN [ClubEatc].[dbo].[GetOnlineBills] t ON y.Telno = t.TelNo AND CAST(y.Cycle as int)-1 = CAST(t.Cycle as int)

Upvotes: 0

Pரதீப்
Pரதீப்

Reputation: 93724

Use NULLIF to handle Divide by zero error.

Then use ISNULL to make the value as 100%

SELECT y.Telno,
       t.Cycle + '-' + y.Cycle                                                   Cycle,
       ( Isnull(( ( y.CurBill - t.CurBill ) / NULLIF(t.CurBill, 0) ), 1) * 100 ) Price
FROM   [ClubEatc].[dbo].[GetOnlineBills] y
       INNER JOIN [ClubEatc].[dbo].[GetOnlineBills] t
               ON y.Telno = t.TelNo
                  AND Cast(y.Cycle AS INT) - 1 = Cast(t.Cycle AS INT)

Upvotes: 1

Related Questions