IIF,ISNULL in transact sql

I have this query in ACCESS VBA:
SELECT DISTINCT qryRCP.RCP_VendorID, Sum(IIf(nz([RCP_ReceiptQty],0)=0,0,1))
AS fldNbrRcpts,
Sum(IIf(nz([SumOfRCQ_RejectQty]+[SumOfRC
Q_ScrapQty],0)=0,0,1)) AS
fldNbrRejScrap
FROM qryRCP
WHERE qryRCP.RCP_ReceiptQty >=0
GROUP BY qryRCP.RCP_VendorID, qryRCP.POM_PayName, qryRCP.VEN_PerfRating,
qryRCP.VEN_StatusCode
Can anyone help to convert it in sql?
I tried to change nz to isnull, but still hitting syntaz error.
I need to run in sql query analyzer first to find out the problem.
Thanks lotCheck out the ISNULL() and COALESCE functions in BooksOnLine.
Andrew J. Kelly SQL MVP
"Sql Fren" <SqlFren@.discussions.microsoft.com> wrote in message
news:16728166-FF7F-4BEB-86E1-D045B4801F0F@.microsoft.com...
>I have this query in ACCESS VBA:
> SELECT DISTINCT qryRCP.RCP_VendorID,
> Sum(IIf(nz([RCP_ReceiptQty],0)=0,0,1))
> AS fldNbrRcpts,
> Sum(IIf(nz([SumOfRCQ_RejectQty]+[SumOfRC
Q_ScrapQty],0)=0,0,1)) AS
> fldNbrRejScrap
> FROM qryRCP
> WHERE qryRCP.RCP_ReceiptQty >=0
> GROUP BY qryRCP.RCP_VendorID, qryRCP.POM_PayName, qryRCP.VEN_PerfRating,
> qryRCP.VEN_StatusCode
> Can anyone help to convert it in sql?
> I tried to change nz to isnull, but still hitting syntaz error.
> I need to run in sql query analyzer first to find out the problem.
> Thanks lot|||
SELECT
RCP_VendorID,
SUM(RCP_ReceiptQty), -- because of WHERE clause,
-- this can't possibly be NULL
SUM(COALESCE(SumOfRCQ_RejectQty,0) + COALESCE(SumOfRCQ_ScrapQty,0))
FROM qryRCP
WHERE RCP_ReceiptQty >= 0
GROUP BY RCP_VendorID
-- your other GROUP BY columns are illegal here, since they're
-- not part of the query at all!
On 3/17/05 10:49 PM, in article
16728166-FF7F-4BEB-86E1-D045B4801F0F@.microsoft.com, "Sql Fren"
<SqlFren@.discussions.microsoft.com> wrote:

> SELECT DISTINCT qryRCP.RCP_VendorID, Sum(IIf(nz([RCP_ReceiptQty],0)=0,0,1
))
> AS fldNbrRcpts,
> Sum(IIf(nz([SumOfRCQ_RejectQty]+[SumOfRC
Q_ScrapQty],0)=0,0,1)) AS
> fldNbrRejScrap
> FROM qryRCP
> WHERE qryRCP.RCP_ReceiptQty >=0
> GROUP BY qryRCP.RCP_VendorID, qryRCP.POM_PayName, qryRCP.VEN_PerfRating,
> qryRCP.VEN_StatusCode|||On Thu, 17 Mar 2005 23:06:08 -0500, Aaron [SQL Server MVP] wrote:
(snip)
>-- your other GROUP BY columns are illegal here, since they're
>-- not part of the query at all!
Hi Aaron,
That doesn't make them illegal. There's no requirement to include all
group by columns in the select list (though omitting then might make the
output of the query useless - I have a hard time imagining a scenario
where it'd be useful).
use pubs
go
select max(zip)
from authors
group by state
go
95688
46403
66044
20853
48105
97330
37215
84152
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||On Thu, 17 Mar 2005 23:06:08 -0500, Aaron [SQL Server MVP] wrote:

> -- your other GROUP BY columns are illegal here, since they're
> -- not part of the query at all!
Say what?
From SQL2K Books Online:
|| GROUP BY Clause
|| Specifies the groups into which output rows are to be placed and, if
|| aggregate functions are included in the SELECT clause <select list>,
|| calculates a summary value for each group. When GROUP BY is specified,
|| either each column in any non-aggregate expression in the select list
|| should be included in the GROUP BY list, or the GROUP BY expression
|| must match exactly the select list expression.
I read this as saying that you can't have a non-aggregate expression in the
SELECT list that isn't in the GROUP BY clause - but it says nothing about
having an expression in the GROUP BY clause that isn't in the SELECT list!
Of course, the result isn't very meaningful - how can you tell which rows
correspond to which group by value if the group by value isn't returned -
but I just ran this in query analyzer
select job, count(map) maps, sum(qty) qtys
from ttOrdClubItem
group by job,club
and got the same result as this:
select job, maps, qtys
from (
select job, club, count(map) maps, sum(qty) qtys
from ttOrdClubItem
group by job,club
)
so the construction is definitely "legal", at least|||Oh gosh, semantics. Sorry, I should have said useless, stupid, meaningless,
bizarre, weird, unexpected... any others I'm missing?
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:7jql31po7vvbvsu0q5eo2bgdmmus870567@.
4ax.com...
> On Thu, 17 Mar 2005 23:06:08 -0500, Aaron [SQL Server MVP] wrote:
> (snip)
> Hi Aaron,
> That doesn't make them illegal. There's no requirement to include all
> group by columns in the select list (though omitting then might make the
> output of the query useless - I have a hard time imagining a scenario
> where it'd be useful).
> use pubs
> go
> select max(zip)
> from authors
> group by state
> go
>
> --
> 95688
> 46403
> 66044
> 20853
> 48105
> 97330
> 37215
> 84152
>
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)

IIF statement issue

Here is the iif statement I have in the report:
=iif ((Sum(Fields!EXTENDED_MARGIN.Value) = 0) or
(SUM(Fields!TY_QTY.Value)=0), 0,
SUM(Fields!EXTENDED_MARGIN.Value)/SUM(Fields!TY_QTY.Value))
If I put in just the basic iif statment evaluation returning a 1 for true
and a 2 for false, it works fine. If I put in the expression I have for
false it will work fine unless one of the fields is a 0 and the other has a
number, then I get an error (which is understandable). I put them together
and I get the same thing as if I just put the false expression in, an error
if one number is a 0 and the other is an actual number.
Can anyone shed some light on this please?Not sure if there is another work around, however, I have run into this
problem many times and have solved it in several different ways. The probelm
that I see is that the IIF statement is calculating both the true and false
parts of the statement. Thus, you have a divide bt zero whenever
Fields!TY_Qty.Value = 0 even though you are trying to trap this condition in
the first part of the expression. Note that testing for
Fields!Extended_margin.value = 0 does not need to be done.
The first way to applies if you are using a stored proc to return the
dataset. If this is the case then you can in many cases return a field that
is already populated with
SUM(Fields!EXTENDED_MARGIN.Value)/SUM(Fields!TY_QTY.Value);.taking care to
handle the division by zero potential in your TSQL code.
The other way that I have handled the problem is by writing a procedure that
takes the two numbers and returns zero if SUM(Fields!TY_QTY.Value) = 0 or
else SUM(Fields!EXTENDED_MARGIN.Value)/SUM(Fields!TY_QTY.Value). Then instead
of using the IIF statement in an expression you call you procedure like this
=Code.MyProcedure( SUM(Fields!EXTENDED_MARGIN.Value),
SUM(Fields!TY_QTY.Value)).
Like I said, there may be other solutions but perhaps this will help.
"TBraun" wrote:
> Here is the iif statement I have in the report:
> =iif ((Sum(Fields!EXTENDED_MARGIN.Value) = 0) or
> (SUM(Fields!TY_QTY.Value)=0), 0,
> SUM(Fields!EXTENDED_MARGIN.Value)/SUM(Fields!TY_QTY.Value))
> If I put in just the basic iif statment evaluation returning a 1 for true
> and a 2 for false, it works fine. If I put in the expression I have for
> false it will work fine unless one of the fields is a 0 and the other has a
> number, then I get an error (which is understandable). I put them together
> and I get the same thing as if I just put the false expression in, an error
> if one number is a 0 and the other is an actual number.
> Can anyone shed some light on this please?|||Mark,
Thanks. I used your second suggestion and wrote a custom function and that
worked just fine. Appreciate the help!
"B. Mark McKinney" wrote:
> Not sure if there is another work around, however, I have run into this
> problem many times and have solved it in several different ways. The probelm
> that I see is that the IIF statement is calculating both the true and false
> parts of the statement. Thus, you have a divide bt zero whenever
> Fields!TY_Qty.Value = 0 even though you are trying to trap this condition in
> the first part of the expression. Note that testing for
> Fields!Extended_margin.value = 0 does not need to be done.
> The first way to applies if you are using a stored proc to return the
> dataset. If this is the case then you can in many cases return a field that
> is already populated with
> SUM(Fields!EXTENDED_MARGIN.Value)/SUM(Fields!TY_QTY.Value);.taking care to
> handle the division by zero potential in your TSQL code.
> The other way that I have handled the problem is by writing a procedure that
> takes the two numbers and returns zero if SUM(Fields!TY_QTY.Value) = 0 or
> else SUM(Fields!EXTENDED_MARGIN.Value)/SUM(Fields!TY_QTY.Value). Then instead
> of using the IIF statement in an expression you call you procedure like this
> =Code.MyProcedure( SUM(Fields!EXTENDED_MARGIN.Value),
> SUM(Fields!TY_QTY.Value)).
> Like I said, there may be other solutions but perhaps this will help.
> "TBraun" wrote:
> > Here is the iif statement I have in the report:
> >
> > =iif ((Sum(Fields!EXTENDED_MARGIN.Value) = 0) or
> > (SUM(Fields!TY_QTY.Value)=0), 0,
> > SUM(Fields!EXTENDED_MARGIN.Value)/SUM(Fields!TY_QTY.Value))
> >
> > If I put in just the basic iif statment evaluation returning a 1 for true
> > and a 2 for false, it works fine. If I put in the expression I have for
> > false it will work fine unless one of the fields is a 0 and the other has a
> > number, then I get an error (which is understandable). I put them together
> > and I get the same thing as if I just put the false expression in, an error
> > if one number is a 0 and the other is an actual number.
> >
> > Can anyone shed some light on this please?

IIF Statement

Below I'm trying to return the larger column. If the SUM of UnitsInStock is
greater than the SUM of UnitsOnOrder, then return SUM(UnitsInStock) AS
largerUnits and vice versa.
This is in northwind, can someone help me correct my syntax? I wasn't sure
how to do it with CASE.
CODE
SELECT IIf(SUM(UnitsInStock)>SUM(UnitsOnOrder), SUM(UnitsInStock) AS
largerUnits, SUM(UnitsOnOrder) AS largerUnits), ProductName
FROM Products
GROUP BY ProductNameTry this (untested):
SELECT case when SUM(UnitsInStock) > SUM(UnitsOnOrder)
then SUM(UnitsInStock)
else SUM(UnitsOnOrder)
end AS largerUnits
,ProductName
FROM Products
GROUP BY ProductName
ML
http://milambda.blogspot.com/|||On Fri, 23 Dec 2005 18:22:17 -0600, Scott wrote:

> Below I'm trying to return the larger column. If the SUM of UnitsInStock i
s
>greater than the SUM of UnitsOnOrder, then return SUM(UnitsInStock) AS
>largerUnits and vice versa.
>This is in northwind, can someone help me correct my syntax? I wasn't sure
>how to do it with CASE.
>CODE
>SELECT IIf(SUM(UnitsInStock)>SUM(UnitsOnOrder), SUM(UnitsInStock) AS
>largerUnits, SUM(UnitsOnOrder) AS largerUnits), ProductName
>FROM Products
>GROUP BY ProductName
>
SELECT CASE WHEN SUM(UnitsInStock) > SUM(UnitsOnOrder)
THEN SUM(UnitsInStock)
ELSE SUM(UnitsOnOrder) ) AS largerUnits),
ProductName
FROM Products
GROUP BY ProductName
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Forgive me for dipping my fly into your ointment, but your CASE expression i
s
missing its END. ;)
ML
http://milambda.blogspot.com/|||thanks.
"ML" <ML@.discussions.microsoft.com> wrote in message
news:63517235-3C00-4A10-9FD3-1963051A3411@.microsoft.com...
> Try this (untested):
> SELECT case when SUM(UnitsInStock) > SUM(UnitsOnOrder)
> then SUM(UnitsInStock)
> else SUM(UnitsOnOrder)
> end AS largerUnits
> ,ProductName
> FROM Products
> GROUP BY ProductName
>
> ML
> --
> http://milambda.blogspot.com/|||Just one thought - how will you ditinct between the two values in the client
application? After all, those are just numbers, but this query returns them
in a single column, although they originate in two different sources...?
Another CASE maybe?
SELECT case when SUM(UnitsInStock) > SUM(UnitsOnOrder)
then SUM(UnitsInStock)
else SUM(UnitsOnOrder)
end AS largerUnits
,case when SUM(UnitsInStock) > SUM(UnitsOnOrder)
then 'InStock'
else 'OnOrder'
end AS largerSource
,ProductName
FROM Products
GROUP BY ProductName
ML
http://milambda.blogspot.com/|||thanks, in my case, i just needed the larger of the 2.
"ML" <ML@.discussions.microsoft.com> wrote in message
news:3442E4F6-D752-4708-BB0A-A65416DE9755@.microsoft.com...
> Just one thought - how will you ditinct between the two values in the
> client
> application? After all, those are just numbers, but this query returns
> them
> in a single column, although they originate in two different sources...?
> Another CASE maybe?
> SELECT case when SUM(UnitsInStock) > SUM(UnitsOnOrder)
> then SUM(UnitsInStock)
> else SUM(UnitsOnOrder)
> end AS largerUnits
> ,case when SUM(UnitsInStock) > SUM(UnitsOnOrder)
> then 'InStock'
> else 'OnOrder'
> end AS largerSource
> ,ProductName
> FROM Products
> GROUP BY ProductName
>
> ML
> --
> http://milambda.blogspot.com/|||On Fri, 23 Dec 2005 17:05:02 -0800, ML wrote:

>Forgive me for dipping my fly into your ointment, but your CASE expression
is
>missing its END. ;)
Hi ML,
So it is. Thanks for the correction.
I really shouldn't write any more replies after 1 AM....
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||But then again - the level of inspiration is usually at its highest point at
1 AM.
ML
http://milambda.blogspot.com/|||For fun, I thought I would try a sql clr function to simulate IIF. Not
quite as elegant as the real IIF, but maybe more concise then a Case.
-- Usage example
declare @.n1 int
declare @.n2 int
set @.n1 = 1
set @.n2 = 2
select dbo.IIF(sum(@.n1), '>', sum(@.n2), 100, 200) -- Just to show using
sum(), does not make sense in this usage.
select dbo.IIF(@.n1, '>', @.n2, 'n1 is > n2', 'n1 is not > n2')
select dbo.IIF(@.n1, '<', @.n2, 'n1 is < n2', 'n1 is not < n2')
select dbo.IIF(@.n1, '>=', @.n2, 'n1 is >= n2', 'n1 is not >= n2');
select dbo.IIF(@.n1, '<=', @.n2, 'n1 is <= n2', 'n1 is not <= n2')
select dbo.IIF(@.n1, '==', @.n2, 'n1 is == n2', 'n1 is not == n2')
select dbo.IIF(@.n1, '!=', @.n2, 'n1 is != n2', 'n1 is not != n2')
select dbo.IIF(@.n1, '<>', @.n2, 'n1 is <> n2', 'n1 is not <> n2')
//
// The SQL Clr UDF IIF code.
//
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
public partial class UserDefinedFunctions
{
/// <summary>
/// Returns one of two objects, depending on the evaluation of an
expression.
/// </summary>
/// <param name="lside">The left side comparand.</param>
/// <param name="op">The conditional operator to use for
testing.</param>
/// <param name="rside">The right side comparand.</param>
/// <param name="truePart">Returned if Expression evaluates to
True.</param>
/// <param name="falsePart">Returned if Expression evaluates to
False.</param>
/// <returns>Returns one of two objects, depending on the evaluation of
an expression. </returns>
[Microsoft.SqlServer.Server.SqlFunction]
public static object IIF(object lside, string op, object rside, object
truePart, object falsePart)
{
if (lside == null || rside == null)
return falsePart;
if (lside is DBNull || rside is DBNull)
return falsePart;
if (op == null)
throw new ArgumentNullException("op");
IComparable cLeft = (IComparable)lside;
IComparable cRight = (IComparable)rside;
/*
CompareTo results:
Less than zero - This instance is less than obj.
Zero - This instance is equal to obj.
Greater than zero - This instance is greater than obj.
*/
switch (op)
{
case ">":
if (cLeft.CompareTo(cRight) > 0)
return truePart;
return falsePart;
case ">=":
if (cLeft.CompareTo(cRight) >=0)
return truePart;
return falsePart;
case "<":
if (cLeft.CompareTo(cRight) < 0)
return truePart;
return falsePart;
case "<=":
if (cLeft.CompareTo(cRight) <= 0)
return truePart;
return falsePart;
case "==":
if (cLeft.CompareTo(cRight) == 0)
return truePart;
return falsePart;
case "!=":
case "<>":
if (cLeft.CompareTo(cRight) == 0)
return falsePart;
return truePart;
default:
throw new ArgumentException("op");
}
}
};
William Stacey [MVP]
"Scott" <sbailey@.mileslumber.com> wrote in message
news:ua%230dECCGHA.216@.TK2MSFTNGP15.phx.gbl...
> Below I'm trying to return the larger column. If the SUM of UnitsInStock
> is greater than the SUM of UnitsOnOrder, then return SUM(UnitsInStock) AS
> largerUnits and vice versa.
> This is in northwind, can someone help me correct my syntax? I wasn't sure
> how to do it with CASE.
> CODE
> SELECT IIf(SUM(UnitsInStock)>SUM(UnitsOnOrder), SUM(UnitsInStock) AS
> largerUnits, SUM(UnitsOnOrder) AS largerUnits), ProductName
> FROM Products
> GROUP BY ProductName
>