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: 376
  • Last Modified:

SQL - Case When Statement

SQL 2005 / 2008


In the below SQL Query, I am dividing a set of query to the another.

In some situations, i am getting the values as follows ..

1/0

or 1.0/0

Any number divide by zero is an Error.

So, if the denominator is Zero, then i need to set the value for those rows as 'N/A'

all these values goes to the column : test

I am not able to Cast / Implement the Case When statement on this issue ..!!
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
chokka
Asked:
chokka
  • 13
  • 7
1 Solution
 
knightEknightCommented:
You can get NULL instead of error by surrounding the entire denominator in a NULLIF function, for example

select 5 / nullif(0,0)

so in your case:


/ 

          NULLIF(
                  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) )
          ,0 )
     as test

Open in new window

0
 
knightEknightCommented:
... see lines 3 & 52 above
0
 
knightEknightCommented:
actually, i may have line 52 in the wrong place, but hopefully you get the idea.  Here is a fuller example:

declare @denom int

select @denom = 2
select 6 / nullif(@denom,0) as result

select @denom = 0
select 6 / nullif(@denom,0) as result
0
Independent Software Vendors: 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:
Throws Syntax Error


1) Well, instead of NULL - Is there any way for us to bring 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) ) / 
			 / 

          NULLIF(
                  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) )
          ,0 )
     as test 

FROM   [Udf_compliancemembers] ('Rose','Post','10/02/1926' ) cm

Open in new window

0
 
knightEknightCommented:
The syntax error is because I put the code at line 86 in the wrong place ... it should go immediately after the end of the denominator query.

We can get it to say N/A like this, but I recommend getting it to work with NULL first, then adding this in to get N/A:

declare @denom int

select @denom = 2
select 6 / isnull( nullif(@denom,0), 'N/A' ) as result

select @denom = 0
select 6 / isnull( nullif(@denom,0), 'N/A' ) as result
0
 
knightEknightCommented:
scratch my last, I didn't take into consideration the conversion from INT to CHAR, so we'll have to get the 'N/A' value a different way ....

But first things first, let's at least get it to NULL first by moving the code at line 86 to the appropriate place...
0
 
chokkaAuthor Commented:


Just executing ..

your  syntax ...

I am facing  -  this error

declare @denom int

select @denom = 2
select 6 / isnull( nullif(@denom,0), 'N/A' ) as result

select @denom = 0
select 6 / isnull( nullif(@denom,0), 'N/A' ) as result


(1 row(s) affected)
Msg 245, Level 16, State 1, Line 7
Conversion failed when converting the varchar value 'N/A' to data type int.


This is a Complicated situation ..


1) We need to cast the output.

2) N/A appears only when Denominator is N/A. So, i guess we need Case Statement !!

0
 
knightEknightCommented:
yes, as I just noted above.  :)

>> But first things first, let's at least get it to NULL first by moving the code at line 86 to the appropriate place...
0
 
knightEknightCommented:
let's revert to this example first, then we'll get to the 'N/A' ...

declare @denom int

select @denom = 2
select 6 / nullif(@denom,0) as result

select @denom = 0
select 6 / nullif(@denom,0) as result
0
 
chokkaAuthor Commented:
declare @denom int

select @denom = 2
select 6 / nullif(@denom,0) as result

select @denom = 0
select 6 / nullif(@denom,0) as result


So, this piece returns NULL ..!! Now, we need to cast this NULL Value to N/A
0
 
knightEknightCommented:
Do you have this pattern working in your query?
0
 
knightEknightCommented:


declare @denom float

select @denom = 2
select isnull( convert( varchar, 44.4 / nullif(@denom,0) ), 'N/A') as result

select @denom = 0
select isnull( convert( varchar, 44.4 / nullif(@denom,0) ), 'N/A') as result

0
 
knightEknightCommented:
That is a working example, but I recommend going one step at a time ... first get it to work with just NULL, the later add in the stuff to convert it to N/A.

0
 
chokkaAuthor Commented:
I face that error - Syntax Part fails ..!!

I understand the intial logic of  select 6 / nullif(@denom,0) as result

But i fail,

NULLIF ( SQL QUERY   ,0 )
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) ) / 
			 / 

          NULLIF(
                  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) )
          ,0 )
     as test 

FROM   [Udf_compliancemembers] ('Rose','Post','10/02/1926' ) cm

Open in new window

0
 
knightEknightCommented:
add parens around the query:

  NULLIF ( (SQL QUERY)   ,0 )
0
 
chokkaAuthor Commented:
Yeah, i did that ..

If you can copy that code and parse it / complie it ..

You can see the issues .. not exactly executing ..!!!
0
 
knightEknightCommented:
I can't compile it because I don't have the tables, but in principle, this should work:



 SELECT (select 6) / nullif( (select 2), 0 ) as result
 SELECT (select 6) / nullif( (select 0), 0 ) as result
 
 note that both the numerator and denominator queries are in parens ... I'm not sure the former is necessary, but I know the latter is because of the nullif.
0
 
chokkaAuthor Commented:
For Parsing the Syntax, we dont need tables ..!!

We just need to run the compiler ..

There is TICK Mark right next to Execute in the Upper Panel of SQL Query Window .. That helps us to Compile any sql syntax ..!!!
0
 
knightEknightCommented:
As I said several times, you just need to move the end of the null-if to its proper place:


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) )
                         /

          NULLIF(
                  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)),0 ) * 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
0
 
chokkaAuthor Commented:
Thanks
0

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

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