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
>