Solved

Calulated Column Returns error apon dividing by Zero

Posted on 2007-03-28
9
176 Views
Last Modified: 2010-03-19
SELECT
max(dbo.getmonthdate(t.TransDate)),
max(dbo.getmonth(t.TransDate)),
Sum(CASE t.TransType WHEN 1 THEN 1 ELSE 0 END) AS PassportsIssued,
Sum(CASE t.TransType WHEN 7 THEN 1 ELSE 0 END) AS PassportVerifications,
Sum(CASE t.TransType WHEN 8 THEN 1 ELSE 0 END) AS SkillVerifications,(Sum(CASE t.TransType WHEN 7 THEN 1 ELSE 0 END) + Sum(CASE t.TransType WHEN 8 THEN 1 ELSE 0 END))/Sum(CASE t.TransType WHEN 1 THEN 1 ELSE 0 END) AS Calculated
FROM GetCardtypes(@schemeID) ct
INNER JOIN tblTransactions t
ON ct.cardtypeID=t.cardTypeID
INNER JOIN tblUsers u on
u.PHID = t.IssuedToID

The problem is that  (CASE t.TransType WHEN 1 THEN 1 ELSE 0 END)  may sometimes be zero in which case I need to return "No Ratio Available"

Can anyone help me edit my query please.

0
Comment
Question by:MayoorPatel
  • 3
  • 3
  • 3
9 Comments
 
LVL 28

Expert Comment

by:imran_fast
Comment Utility
change this column to be

case when  (CASE t.TransType WHEN 1 THEN 1 ELSE 0 END) = 0 then 0
else
(Sum(CASE t.TransType WHEN 7 THEN 1 ELSE 0 END) + Sum(CASE t.TransType WHEN 8 THEN 1 ELSE 0 END))/Sum(CASE t.TransType WHEN 1 THEN 1 ELSE 0 END)
end
 AS Calculated
0
 
LVL 17

Expert Comment

by:HuyBD
Comment Utility
SELECT
max(dbo.getmonthdate(t.TransDate)),
max(dbo.getmonth(t.TransDate)),
Sum(CASE t.TransType WHEN 1 THEN 1 ELSE 0 END) AS PassportsIssued,
Sum(CASE t.TransType WHEN 7 THEN 1 ELSE 0 END) AS PassportVerifications,
Sum(CASE t.TransType WHEN 8 THEN 1 ELSE 0 END) AS SkillVerifications, (Sum(CASE t.TransType WHEN 7 THEN 1 ELSE 0 END) + CASE t.TransTypeSum WHEN 1 THEN (CASE t.TransType WHEN 8 THEN 1 ELSE 0 END))/Sum(1) ELSE 0) AS Calculated
FROM GetCardtypes(@schemeID) ct
INNER JOIN tblTransactions t
ON ct.cardtypeID=t.cardTypeID
INNER JOIN tblUsers u on
u.PHID = t.IssuedToID

HuyBD;
0
 
LVL 1

Author Comment

by:MayoorPatel
Comment Utility
Huy Bud im getting

Msg 102, Level 15, State 1, Line 6
Incorrect syntax near ')'.

Which is an error with this line?

Sum(CASE t.TransType WHEN 8 THEN 1 ELSE 0 END) AS SkillVerifications, (Sum(CASE t.TransType WHEN 7 THEN 1 ELSE 0 END) + CASE t.TransTypeSum WHEN 1 THEN (CASE t.TransType WHEN 8 THEN 1 ELSE 0 END))/Sum(1) ELSE 0) AS Calculated
0
 
LVL 28

Expert Comment

by:imran_fast
Comment Utility
SELECT
max(dbo.getmonthdate(t.TransDate)),
max(dbo.getmonth(t.TransDate)),
Sum(CASE t.TransType WHEN 1 THEN 1 ELSE 0 END) AS PassportsIssued,
Sum(CASE t.TransType WHEN 7 THEN 1 ELSE 0 END) AS PassportVerifications,
case when  (CASE t.TransType WHEN 1 THEN 1 ELSE 0 END) = 0 then 0
else
(Sum(CASE t.TransType WHEN 7 THEN 1 ELSE 0 END) + Sum(CASE t.TransType WHEN 8 THEN 1 ELSE 0 END))/Sum(CASE t.TransType WHEN 1 THEN 1 ELSE 0 END)
end
 AS Calculated


FROM GetCardtypes(@schemeID) ct
INNER JOIN tblTransactions t
ON ct.cardtypeID=t.cardTypeID
INNER JOIN tblUsers u on
u.PHID = t.IssuedToID
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 1

Author Comment

by:MayoorPatel
Comment Utility
Imran I now get this

Msg 8120, Level 16, State 1, Line 1
Column 'tblTransactions.TransType' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

0
 
LVL 17

Expert Comment

by:HuyBD
Comment Utility
sorry, I have missing
SELECT
max(dbo.getmonthdate(t.TransDate)),
max(dbo.getmonth(t.TransDate)),
Sum(CASE t.TransType WHEN 1 THEN 1 ELSE 0 END) AS PassportsIssued,
Sum(CASE t.TransType WHEN 7 THEN 1 ELSE 0 END) AS PassportVerifications,
Sum(CASE t.TransType WHEN 8 THEN 1 ELSE 0 END) AS SkillVerifications, (Sum(CASE t.TransType WHEN 7 THEN 1 ELSE 0 END) + CASE t.TransTypeSum WHEN 1 THEN (CASE t.TransType WHEN 8 THEN 1 ELSE 0 END))/Sum(1) ELSE 0 END) AS Calculated
FROM GetCardtypes(@schemeID) ct
INNER JOIN tblTransactions t
ON ct.cardtypeID=t.cardTypeID
INNER JOIN tblUsers u on
u.PHID = t.IssuedToID
0
 
LVL 1

Author Comment

by:MayoorPatel
Comment Utility
Huy Bud still getting

Msg 102, Level 15, State 1, Line 6
Incorrect syntax near ')'.
with this line

Sum(CASE t.TransType WHEN 8 THEN 1 ELSE 0 END) AS SkillVerifications, (Sum(CASE t.TransType WHEN 7 THEN 1 ELSE 0 END) + CASE t.TransTypeSum WHEN 1 THEN (CASE t.TransType WHEN 8 THEN 1 ELSE 0 END))/Sum(1) ELSE 0 END) AS Calculated
0
 
LVL 28

Accepted Solution

by:
imran_fast earned 250 total points
Comment Utility
Try this

SELECT
max(dbo.getmonthdate(t.TransDate)),
max(dbo.getmonth(t.TransDate)),
Sum(CASE t.TransType WHEN 1 THEN 1 ELSE 0 END) AS PassportsIssued,
Sum(CASE t.TransType WHEN 7 THEN 1 ELSE 0 END) AS PassportVerifications,
case when  sum(CASE t.TransType WHEN 1 THEN 1 ELSE 0 END) = 0 then 0
else
(Sum(CASE t.TransType WHEN 7 THEN 1 ELSE 0 END) + Sum(CASE t.TransType WHEN 8 THEN 1 ELSE 0 END))/Sum(CASE t.TransType WHEN 1 THEN 1 ELSE 0 END)
end
 AS Calculated


FROM GetCardtypes(@schemeID) ct
INNER JOIN tblTransactions t
ON ct.cardtypeID=t.cardTypeID
INNER JOIN tblUsers u on
u.PHID = t.IssuedToID
0
 
LVL 17

Assisted Solution

by:HuyBD
HuyBD earned 250 total points
Comment Utility
SELECT
max(dbo.getmonthdate(t.TransDate)),
max(dbo.getmonth(t.TransDate)),
Sum(CASE t.TransType WHEN 1 THEN 1 ELSE 0 END) AS PassportsIssued,
Sum(CASE t.TransType WHEN 7 THEN 1 ELSE 0 END) AS PassportVerifications,
Sum(CASE t.TransType WHEN 8 THEN 1 ELSE 0 END) AS SkillVerifications, (Sum(CASE t.TransType WHEN 7 THEN 1 ELSE 0 END) + CASE t.TransTypeSum WHEN 1 THEN (CASE t.TransType WHEN 8 THEN 1 ELSE 0 END))/Sum(1)) ELSE 0 END) AS Calculated
FROM GetCardtypes(@schemeID) ct
INNER JOIN tblTransactions t
ON ct.cardtypeID=t.cardTypeID
INNER JOIN tblUsers u on
u.PHID = t.IssuedToID
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now