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

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/

thanks.

Show quoteHide quote

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.

Show quoteHide quote

On Fri, 23 Dec 2005 18:22:17 -0600, Scott wrote:

Show quoteHide quoteSELECT   CASE WHEN SUM(UnitsInStock) > SUM(UnitsOnOrder)
              THEN SUM(UnitsInStock)
              ELSE SUM(UnitsOnOrder) ) AS largerUnits),
         ProductName
FROM     Products
GROUP BY ProductName


Best, Hugo

Forgive me for dipping my fly into your ointment, but your CASE expression is
missing its END. ;)


ML

---
http://milambda.blogspot.com/

On Fri, 23 Dec 2005 17:05:02 -0800, ML wrote:

Hi ML,

So it is. Thanks for the correction.

I really shouldn't write any more replies after 1 AM.....

Best, Hugo

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");
        }
    }
};

Show quoteHide quote

DECLARE CURSOR intermittently failing
How do I pass a second parameter to user-defined aggregate (SQL 20
Custom number format
WHERE IN - Return NULL values
GetDate() in UDF