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 ..!!
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
... see lines 3 & 52 above
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
declare @denom int
select @denom = 2
select 6 / nullif(@denom,0) as result
select @denom = 0
select 6 / nullif(@denom,0) as result
ASKER
Throws Syntax Error
1) Well, instead of NULL - Is there any way for us to bring N/A ..!!
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
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
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
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...
But first things first, let's at least get it to NULL first by moving the code at line 86 to the appropriate place...
ASKER
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 !!
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...
>> But first things first, let's at least get it to NULL first by moving the code at line 86 to the appropriate place...
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
declare @denom int
select @denom = 2
select 6 / nullif(@denom,0) as result
select @denom = 0
select 6 / nullif(@denom,0) as result
ASKER
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
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
Do you have this pattern working in your query?
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
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.
ASKER
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 )
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
add parens around the query:
NULLIF ( (SQL QUERY) ,0 )
NULLIF ( (SQL QUERY) ,0 )
ASKER
Yeah, i did that ..
If you can copy that code and parse it / complie it ..
You can see the issues .. not exactly executing ..!!!
If you can copy that code and parse it / complie it ..
You can see the issues .. not exactly executing ..!!!
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.
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.
ASKER
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 ..!!!
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 ..!!!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks
select 5 / nullif(0,0)
so in your case:
Open in new window