IIF SQL function

1. Usage of IIF to find size of the order.

SQL Server Query 1

            
SELECT  

    OrderID,  

    CustomerID,  

    OrderDate,  

    Freight,  

    OrderTotal = (SELECT SUM(UnitPrice * Quantity) FROM [Order Details] WHERE OrderID = Orders.OrderID),  

    Category = IIF(  

		            (SELECT SUM(UnitPrice * Quantity) FROM [Order Details] WHERE OrderID = Orders.OrderID) < 100,  

		            'Small',  

		            IIF(  

			            (SELECT SUM(UnitPrice * Quantity) FROM [Order Details] WHERE OrderID = Orders.OrderID) < 500,  

			            'Medium',  

			            IIF(  

				            (SELECT SUM(UnitPrice * Quantity) FROM [Order Details] WHERE OrderID = Orders.OrderID) < 1000,  

				            'Large',  

				             'Very Large'  

            )  

        )  

    )  

FROM Orders  

ORDER BY OrderID;  

            
        

Create SQL query with SqlQueryBuilder 1

            
var (sql1, parameters1) = new SqlQueryBuilder()  

    .Select()  

    .Columns("OrderID", "CustomerID", "OrderDate", "Freight")  

    .Column(new SqlQueryBuilder().Select().Column(new SUM(new ColumnArithmatic("UnitPrice").MULTIPLY("Quantity")), "OrderTotal")  

        .From("[Order Details]").Where(new Where(new Column("OrderID"), SQLComparisonOperators.EQUALE_TO, new Column("Orders.OrderID")))  

        , "OrderTotal")  

    .Column(new IIF(new SqlQueryBuilder()  

            .Select()  

            .Column(new SUM(new ColumnArithmatic("UnitPrice").MULTIPLY("Quantity")), "Category")  

            .From("[Order Details]")  

            .Where(new Where(new Column("OrderID").Equale(new Column("Orders.OrderID")))).LessThan(100)  

        ).True("Small")  

        .False(new IIF(  

                new SqlQueryBuilder().Select().Column(new SUM(new ColumnArithmatic("UnitPrice").MULTIPLY("Quantity")), "Category")  

                    .From("[Order Details]")  

                    .Where(new Where(new Column("OrderID").Equale(new Column("Orders.OrderID")))).LessThan(500)  

            ).True("Medium")  

            .False(new IIF(  

                    new SqlQueryBuilder().Select().Column(new SUM(new ColumnArithmatic("UnitPrice").MULTIPLY("Quantity")), "Category")  

                        .From("[Order Details]")  

                        .Where(new Where(new Column("OrderID").Equale(new Column("Orders.OrderID")))).LessThan(1000)  

                ).True("Large").False("Very Large")  

            )  

        )  

    , "Category")  

    .From("Orders")  

    .OrderBy(new OrderBy().Set(new Column("OrderID")))  

    .Build();  

            
        

Query build by SqlQueryBuilder 1

            
SELECT OrderID,
       CustomerID,
       OrderDate,
       Freight,
       (SELECT SUM(UnitPrice * Quantity) AS OrderTotal
        FROM [Order Details]
        WHERE OrderID = Orders.OrderID) AS OrderTotal,
       IIF ((SELECT SUM(UnitPrice * Quantity) AS Category
             FROM [Order Details]
             WHERE OrderID = Orders.OrderID) < @pMAIN_2507200140484217320, @pMAIN_2507200140484217321, IIF ((SELECT SUM(UnitPrice * Quantity) AS Category
                                                                                                             FROM [Order Details]
                                                                                                             WHERE OrderID = Orders.OrderID) < @pMAIN_2507200140484217322, @pMAIN_2507200140484217323, IIF ((SELECT SUM(UnitPrice * Quantity) AS Category
                                                                                                                                                                                                             FROM [Order Details]
                                                                                                                                                                                                             WHERE OrderID = Orders.OrderID) < @pMAIN_2507200140484217324, @pMAIN_2507200140484217325, @pMAIN_2507200140484217326))) AS Category
FROM Orders
ORDER BY OrderID ASC;


            
        

Parameters (If used)

Query Results 1:

2. Usage of IIF to compare shipping status.

SQL Server Query 2

            
SELECT  

    OrderID,  

    OrderDate,  

    ShippedDate,  

    ShipStatus = IIF(  

        ShippedDate IS NULL,  

        'Not Shipped',  

        IIF(  

            ShippedDate <= OrderDate + 7,  -- Shipped within 7 days  

            'Shipped On Time',  

            'Shipped Late'  

        )  

    )  

FROM Orders  

ORDER BY OrderID;  

            
        

Create SQL query with SqlQueryBuilder 2

            
var (sql2, parameters2) = new SqlQueryBuilder()  

    .Select()  

    .Columns("OrderID", "OrderDate", "ShippedDate")  

    .Column(new IIF(new IS_NULL(new Column("ShippedDate")))  

        .True("Not Shipped")  

        .False(new IIF(new Column("ShippedDate").LessThanOrEqualeTo(new ColumnArithmatic("OrderDate").ADD(7)))  

            .True("Shipped On Time")  

            .False("Shipped Late")  

        )  

    , "ShipStatus")  

    .From("Orders")  

    .OrderBy(new OrderBy().SetColumnAscending("OrderID"))  

    .Build();  

            
        

Query build by SqlQueryBuilder 2

            
SELECT OrderID,
       OrderDate,
       ShippedDate,
       IIF (ShippedDate IS NULL, @pMAIN_2507200140484480720, IIF (ShippedDate <= OrderDate + @pMAIN_2507200140484480721, @pMAIN_2507200140484480722, @pMAIN_2507200140484480723)) AS ShipStatus
FROM Orders
ORDER BY OrderID ASC;


            
        

Parameters (If used)

Query Results 2: