Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 420
  • Last Modified:

SQl : Divide by Zero Encountered ..!!!


SQL 2005 / 2008


SQL 2005 / 2008

I am facing Exception Error on below Syntax :-

select x.MemberName,x.Directions,x.Name,x.QtyOrdered,x.DaysSupply,x.DateFilled,
CASE      WHEN x.test = 0  THEN 'N/A'  WHEN compliance > 100.0   THEN '100.0'      
               ELSE CONVERT(VARCHAR(5),

-- CAST(FLOOR(compliance *10)/10.0 AS DECIMAL(3,1)))      END as [Compliance]
CAST(FLOOR(compliance *10) / 10.0 AS DECIMAL(6,1)))  END as [Compliance]

***

1) Error occurs only for a certain inputs, not for all.
2) Compliance column has to accept nvarchar and float. If there is no compliance, we set the value to be 'N/A'. if there is compliance it could be .. 98.9% or etc .. by making sure that they dont exceed 100%


0
chokka
Asked:
chokka
  • 7
  • 6
1 Solution
 
Kevin CrossChief Technology OfficerCommented:
Hmm. I don't actually see a division there, can you post the full query.
0
 
dwe761Software EngineerCommented:
what are the inputs that cause the error?
0
 
chokkaAuthor Commented:

-- CAST(FLOOR(compliance *10)/10.0 AS DECIMAL(3,1)))      END as [Compliance]

CAST(FLOOR(compliance *10) / 10.0 AS DECIMAL(6,1)))  END as [Compliance]


I tried casting the compliance column ..
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
chokkaAuthor Commented:



Input fields are firstname,lastname and DOB ...

So for some people it works great .. it breaks down only on certain people ..!!

I have no idea, i believe the error throws mainly due to casting , ..

Technically, we can't divide a Number by Zero.
Try 5/0 or 6/0 ... or Any Number / 0 is not possible ..!!

So, on this Casting i need to write .. N/A for any number is going to be divided by Zero ..!!
0
 
Kevin CrossChief Technology OfficerCommented:
You are dividing by a literal 10.0 there, so I am not sure how you can get that error. Now, you could end up with a numerator of 0 if (compliance * 10) is still less than 1 as FLOOR() will force to 0, but that should just result in 0 which should not be an issue unless you are then dividing by this later.
0
 
Kevin CrossChief Technology OfficerCommented:
I understand you can't divide by 0, but my point is that you are not dividing by variables here unless you showed us a query that doesn't reflect the real-life scenario. Hence my request to post the whole thing. :)

Note you use x.test = 0 and not compliance = 0, so to get an understanding it helps if we see everything. Maybe post samples of the data that fail and the complete code and we can take a look.
0
 
chokkaAuthor Commented:
Please find the complete query attached ..!!
select x.MemberName,x.DOB,x.FilePath,x.Medication,x.NDC,x.Directions,x.Name,x.Strength,x.GenericName,x.QtyOrdered,x.DaysSupply,x.DateFilled, 
CASE      WHEN x.test = 0  THEN 'N/A'  WHEN compliance > 100.0   THEN '100.0'      ELSE CONVERT(VARCHAR(7), 
-- CAST(FLOOR(compliance *10)/10.0 AS DECIMAL(3,1)))      END as [Compliance]
CAST(FLOOR(compliance *10) / 10.0 AS DECIMAL(6,1)))  END as [Compliance]


from (
SELECT cm.*, ( ( (SELECT 1.0 * Coalesce(SUM(dayssupply), 0) AS sumdayssupply 
            FROM   (SELECT	FirstName,LastName,DateOfBirth,dayssupply,ndc,datefilled 
                    FROM   voeorderhistorywide 
                    UNION ALL 
                    SELECT FirstName,LastName,DateOfBirth,dayssupply,ndc,datefilled  
                    FROM   voeorderwide) AS x 
            WHERE  FirstName = 'Robert'
				   AND LastName = 'Dale'
				   AND DateOfBirth = '02/22/1980'
				   AND ndc = cm.ndc 
                   AND datefilled >= '03/15/2010') - 
                  (SELECT TOP 1 dayssupply 
                   FROM   (SELECT FirstName, LastName,DateOfBirth,dayssupply,datefilled,ndc 
                           FROM   voeorderhistorywide 
                           UNION ALL 
                           SELECT FirstName, LastName,DateOfBirth,dayssupply,datefilled,ndc 
                           FROM   voeorderwide)AS x 
                   WHERE  FirstName = 'Robert'
							AND LastName = 'Dale'
						 AND DateOfBirth = '02/22/1980'
                          AND ndc = cm.ndc 
                          AND datefilled >= 
                              '03/15/2010' 
                   ORDER  BY datefilled DESC) ) / 
                  Datediff(d, (SELECT TOP 1 datefilled 
                               FROM   (SELECT FirstName, LastName,DateOfBirth,dayssupply,datefilled,ndc 
                                       FROM   voeorderhistorywide 
                                       UNION ALL 
                                       SELECT FirstName, LastName,DateOfBirth,dayssupply,datefilled,ndc 
                                       FROM   voeorderwide)AS x 
                               WHERE  FirstName = 'Robert'
				   AND LastName = 'Dale'
				   AND DateOfBirth = '02/22/1980'
                                      AND ndc = cm.ndc 
                                      AND datefilled >= '03/15/2010' 
                               ORDER  BY datefilled ASC), 
                  (SELECT TOP 1 datefilled 
                   FROM   (SELECT FirstName, LastName,DateOfBirth,dayssupply,datefilled,ndc 
                           FROM   voeorderhistorywide 
                           UNION ALL 
                           SELECT FirstName, LastName,DateOfBirth,dayssupply,datefilled,ndc 
                           FROM   voeorderwide)AS x 
                   WHERE  FirstName = 'Robert'
				   AND LastName = 'Dale'
				   AND DateOfBirth = '02/22/1980'
                          AND ndc = cm.ndc 
                          AND datefilled >= '03/15/2010' 
                   ORDER  BY datefilled DESC)) ) * 100.0 AS Compliance , 
         ( (SELECT 1.0 * Coalesce(SUM(dayssupply), 0) AS sumdayssupply 
            FROM   (SELECT      FirstName,LastName,DateOfBirth,dayssupply,ndc,datefilled 
                    FROM   voeorderhistorywide 
                    UNION ALL 
                    SELECT FirstName,LastName,DateOfBirth,dayssupply,ndc,datefilled  
                    FROM   voeorderwide) AS x 
            WHERE  FirstName = 'Robert'
                           AND LastName = 'Dale'
                           AND DateOfBirth = '02/22/1980'
                           AND ndc = cm.ndc 
                   AND datefilled >= '03/15/2010') - 
                  (SELECT TOP 1 dayssupply 
                   FROM   (SELECT FirstName, LastName,DateOfBirth,dayssupply,datefilled,ndc 
                           FROM   voeorderhistorywide 
                           UNION ALL 
                           SELECT FirstName, LastName,DateOfBirth,dayssupply,datefilled,ndc 
                           FROM   voeorderwide)AS x 
                   WHERE  FirstName = 'Robert'
                                          AND LastName ='Dale'
                                     AND DateOfBirth = '02/22/1980'
                          AND ndc = cm.ndc 
                          AND datefilled >= 
                              '03/15/2010' 
                   ORDER  BY datefilled DESC) ) as test
FROM   [Udf_compliancemembers] ('Robert','Dale','02/22/1980' ) cm

) x

Open in new window

0
 
Kevin CrossChief Technology OfficerCommented:
Look in your calculation for compliance you have a division with a DATEDIFF - if you get back date times that are on the same day then your denominator will evaluate to zero; hence error message!
0
 
chokkaAuthor Commented:
Yes, if i am getting this error and i need to display it as "N/A" on the Compliance field ..!!

i.e) if my denominator is zero, then before dividing i need to assign the value to the Compliance field as "N/A"
0
 
chokkaAuthor Commented:
Actually i face an error on this part of the Source Code.

You can see the Test Column - Which is generated by Dvision
SELECT cm.*, ( ( (SELECT 1.0 * Coalesce(SUM(dayssupply), 0) AS sumdayssupply 
            FROM   (SELECT	FirstName,LastName,DateOfBirth,dayssupply,ndc,datefilled 
                    FROM   voeorderhistorywide 
                    UNION ALL 
                    SELECT FirstName,LastName,DateOfBirth,dayssupply,ndc,datefilled  
                    FROM   voeorderwide) AS x 
            WHERE  FirstName = 'Robert'
				   AND LastName = 'Dale'
				   AND DateOfBirth = '02/22/1980'
				   AND ndc = cm.ndc 
                   AND datefilled >= '03/15/2010') - 
                  (SELECT TOP 1 dayssupply 
                   FROM   (SELECT FirstName, LastName,DateOfBirth,dayssupply,datefilled,ndc 
                           FROM   voeorderhistorywide 
                           UNION ALL 
                           SELECT FirstName, LastName,DateOfBirth,dayssupply,datefilled,ndc 
                           FROM   voeorderwide)AS x 
                   WHERE  FirstName = 'Robert'
							AND LastName = 'Dale'
						 AND DateOfBirth = '02/22/1980'
                          AND ndc = cm.ndc 
                          AND datefilled >= 
                              '03/15/2010' 
                   ORDER  BY datefilled DESC) ) / 
                  Datediff(d, (SELECT TOP 1 datefilled 
                               FROM   (SELECT FirstName, LastName,DateOfBirth,dayssupply,datefilled,ndc 
                                       FROM   voeorderhistorywide 
                                       UNION ALL 
                                       SELECT FirstName, LastName,DateOfBirth,dayssupply,datefilled,ndc 
                                       FROM   voeorderwide)AS x 
                               WHERE  FirstName = 'Robert'
				   AND LastName = 'Dale'
				   AND DateOfBirth = '02/22/1980'
                                      AND ndc = cm.ndc 
                                      AND datefilled >= '03/15/2010' 
                               ORDER  BY datefilled ASC), 
                  (SELECT TOP 1 datefilled 
                   FROM   (SELECT FirstName, LastName,DateOfBirth,dayssupply,datefilled,ndc 
                           FROM   voeorderhistorywide 
                           UNION ALL 
                           SELECT FirstName, LastName,DateOfBirth,dayssupply,datefilled,ndc 
                           FROM   voeorderwide)AS x 
                   WHERE  FirstName = 'Robert'
				   AND LastName = 'Dale'
				   AND DateOfBirth = '02/22/1980'
                          AND ndc = cm.ndc 
                          AND datefilled >= '03/15/2010' 
                   ORDER  BY datefilled DESC)) ) * 100.0 AS Compliance , 
         ( (SELECT 1.0 * Coalesce(SUM(dayssupply), 0) AS sumdayssupply 
            FROM   (SELECT      FirstName,LastName,DateOfBirth,dayssupply,ndc,datefilled 
                    FROM   voeorderhistorywide 
                    UNION ALL 
                    SELECT FirstName,LastName,DateOfBirth,dayssupply,ndc,datefilled  
                    FROM   voeorderwide) AS x 
            WHERE  FirstName = 'Robert'
                           AND LastName = 'Dale'
                           AND DateOfBirth = '02/22/1980'
                           AND ndc = cm.ndc 
                   AND datefilled >= '03/15/2010') - 
                  (SELECT TOP 1 dayssupply 
                   FROM   (SELECT FirstName, LastName,DateOfBirth,dayssupply,datefilled,ndc 
                           FROM   voeorderhistorywide 
                           UNION ALL 
                           SELECT FirstName, LastName,DateOfBirth,dayssupply,datefilled,ndc 
                           FROM   voeorderwide)AS x 
                   WHERE  FirstName = 'Robert'
                                          AND LastName ='Dale'
                                     AND DateOfBirth = '02/22/1980'
                          AND ndc = cm.ndc 
                          AND datefilled >= 
                              '03/15/2010' 
                   ORDER  BY datefilled DESC) ) as test
FROM   [Udf_compliancemembers] ('Robert','Dale','02/22/1980' ) cm

Open in new window

0
 
Kevin CrossChief Technology OfficerCommented:
That looks like you are pointing to the DATEDIFF piece of the code that I already directed you to above. Is that correct? It is very unclear what it is you are saying ... the error is before you are assigning the 'N/A' in the case statement. Try splitting up that extremely large calculation filled with subqueries. i.e., bring back each subquery to a different column then do the calculation in the outer query where you can use CASE to inspect whether or not the DATEDIFF results in 0.
0
 
chokkaAuthor Commented:
@mwvisa1, Yes you are perfectly right ..!!!

But now, in that DateDiff Part of syntax - I am trying to implement Case When Statement

CASE   WHEN    (SQL QUERY) = 0  THEN 'N/A'  
               
SELECT cm.*, ( ( (	SELECT 1.0 * Coalesce(SUM(dayssupply), 0) AS sumdayssupply 
FROM			(
					SELECT		FirstName,LastName,	DateOfBirth,dayssupply,ndc,datefilled 
                    FROM		voeorderhistorywide 
                    UNION ALL 
                    SELECT		FirstName,LastName,DateOfBirth,dayssupply,
								ndc,datefilled  
                    FROM		voeorderwide
				)	AS x 
					WHERE		FirstName = 'Rose' AND	LastName = 'Post' AND DateOfBirth = '10/02/1926'
					AND			ndc = cm.ndc AND datefilled >= '03/15/2010') - 
                  (	SELECT TOP 1 dayssupply 
					FROM   (	SELECT	FirstName,LastName,DateOfBirth,dayssupply,datefilled,
										ndc FROM   voeorderhistorywide 
								UNION ALL SELECT FirstName, LastName,DateOfBirth,dayssupply,datefilled,ndc 
									FROM   voeorderwide)AS x 
                   WHERE		FirstName = 'Rose' AND LastName = 'Post'  AND DateOfBirth = '10/02/1926'
                          AND ndc = cm.ndc AND datefilled >=  '03/15/2010' ORDER  BY datefilled DESC) ) 

					/ 

                  Datediff(d, (SELECT TOP 1 datefilled 
                               FROM   (SELECT FirstName, LastName,DateOfBirth,dayssupply,datefilled,ndc 
                                       FROM   voeorderhistorywide 
                                       UNION ALL 
                                       SELECT FirstName, LastName,DateOfBirth,dayssupply,datefilled,ndc 
                                       FROM   voeorderwide)AS x 
                               WHERE  FirstName = 'Rose'
				   AND LastName = 'Post'
				   AND DateOfBirth = '10/02/1926'
                                      AND ndc = cm.ndc 
                                      AND datefilled >= '03/15/2010' 
                               ORDER  BY datefilled ASC), 
                  (SELECT TOP 1 datefilled 
                   FROM   (SELECT FirstName, LastName,DateOfBirth,dayssupply,datefilled,ndc 
                           FROM   voeorderhistorywide 
                           UNION ALL 
                           SELECT FirstName, LastName,DateOfBirth,dayssupply,datefilled,ndc 
                           FROM   voeorderwide)AS x 
                   WHERE  FirstName = 'Rose'
				   AND LastName = 'Post'
				   AND DateOfBirth = '10/02/1926'
                          AND ndc = cm.ndc 
                          AND datefilled >= '03/15/2010' 
                   ORDER  BY datefilled DESC)) ) * 100.0 AS Compliance , 
         ( (SELECT 1.0 * Coalesce(SUM(dayssupply), 0) AS sumdayssupply 
            FROM   (SELECT      FirstName,LastName,DateOfBirth,dayssupply,ndc,datefilled 
                    FROM   voeorderhistorywide 
                    UNION ALL 
                    SELECT FirstName,LastName,DateOfBirth,dayssupply,ndc,datefilled  
                    FROM   voeorderwide) AS x 
            WHERE  FirstName = 'Rose'
                           AND LastName = 'Post'
                           AND DateOfBirth = '10/02/1926'
                           AND ndc = cm.ndc 
                   AND datefilled >= '03/15/2010') - 
                  (SELECT TOP 1 dayssupply 
                   FROM   (SELECT FirstName, LastName,DateOfBirth,dayssupply,datefilled,ndc 
                           FROM   voeorderhistorywide 
                           UNION ALL 
                           SELECT FirstName, LastName,DateOfBirth,dayssupply,datefilled,ndc 
                           FROM   voeorderwide)AS x 
                   WHERE  FirstName = 'Rose'
                                          AND LastName ='Post'
                                     AND DateOfBirth = '10/02/1926'
                          AND ndc = cm.ndc 
                          AND datefilled >= 
                              '03/15/2010' 
                   ORDER  BY datefilled DESC) ) as test
FROM   [Udf_compliancemembers] ('Rose','Post','10/02/1926' ) cm

Open in new window

0
 
Kevin CrossChief Technology OfficerCommented:
That is going to be very confusing to maintain as you will have:

CASE
   WHEN DateDiff(d, ({some query}), ({another query}))  = 0
      THEN 'N/A'
   ELSE ({yet another query}) / DateDiff(d, ({some query}), ({another query}))
END

I would recommend highly, that you do this:

SELECT CASE DateDiff(d, x, y) WHEN 0 THEN 'N/A' ELSE z / DateDiff(d, x, y) END
FROM (
   SELECT ({some query}) as x, ({another query}) as y, ({yet another query}) as z
) derived

Make sense?
0
 
chokkaAuthor Commented:
Thanks
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 7
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now