CHOOSE SQL function

1. Usage of CHOOSE to map shipper name with shipper id.

SQL Server Query 1

            
SELECT  

    o.OrderID,  

    o.ShipVia,  

    CHOOSE(o.ShipVia, 'Speedy Express', 'United Package', 'Federal Shipping') AS ShipperName,  

    ISNULL(CHOOSE(o.ShipVia, 'Speedy Express', 'United Package', 'Federal Shipping'), 'Unknown Shipper') AS ShipperNameWithDefault  

FROM Orders o;  

            
        

Create SQL query with SqlQueryBuilder 1

            
var (sql1, parameters1) = new SqlQueryBuilder()  

    .Select()  

    .Columns("o.OrderID", "o.ShipVia")  

    .Column(new CHOOSE(new Column("o.ShipVia"), "Speedy Express", "United Package", "Federal Shipping"),"ShipperName")  

    .Column(new ISNULL(new CHOOSE(new Column("o.ShipVia"), "Speedy Express", "United Package", "Federal Shipping"), "Unknown Shipper"),"ShipperNameWithDefault")  

    .From("Orders", "o")  

    .Build();  

            
        

Query build by SqlQueryBuilder 1

            
SELECT o.OrderID,
       o.ShipVia,
       CHOOSE(o.ShipVia, @pMAIN_2507200145499883200, @pMAIN_2507200145499883201, @pMAIN_2507200145499883202) AS ShipperName,
       ISNULL(CHOOSE(o.ShipVia, @pMAIN_2507200145499883203, @pMAIN_2507200145499883204, @pMAIN_2507200145499883205), @pMAIN_2507200145499883206) AS ShipperNameWithDefault
FROM Orders AS o;


            
        

Parameters (If used)

Query Results 1:

2. Usage of IIF to compare shipping status.

SQL Server Query 2

            
SELECT  

    e.EmployeeID,  

    CHOOSE(e.EmployeeID, 'Chief Executive Officer', 'Vice President, Sales', 'Sales Representative',  

           'Sales Representative', 'Sales Representative', 'Sales Manager',  

           'Sales Representative', 'Public Relations Representative', 'Accountant') AS EmployeeTitle  

FROM Employees e;  

            
        

Create SQL query with SqlQueryBuilder 2

            
var (sql2, parameters2) = new SqlQueryBuilder()  

    .Select()  

    .Columns("e.EmployeeID")  

    .Column(new CHOOSE(new Column("e.EmployeeID"), "Chief Executive Officer", "Vice President, Sales", "Sales Representative",  

           "Sales Representative", "Sales Representative", "Sales Manager",  

           "Sales Representative", "Public Relations Representative", "Accountant"), "EmployeeTitle")  

    .From("Employees", "e")  

    .Build();  

            
        

Query build by SqlQueryBuilder 2

            
SELECT e.EmployeeID,
       CHOOSE(e.EmployeeID, @pMAIN_2507200145499990740, @pMAIN_2507200145499990741, @pMAIN_2507200145499990742, @pMAIN_2507200145499990743, @pMAIN_2507200145499990744, @pMAIN_2507200145499990745, @pMAIN_2507200145499990746, @pMAIN_2507200145499990747, @pMAIN_2507200145499990748) AS EmployeeTitle
FROM Employees AS e;


            
        

Parameters (If used)

Query Results 2:

3. Usage of CHOOSE with expression and calculation.

SQL Server Query 3

            
SELECT  

    od.OrderID,  

    od.ProductID,  

    od.Quantity,  

    od.UnitPrice,  

    CHOOSE(  

        CASE  

            WHEN od.Quantity < 10 THEN 1  

            WHEN od.Quantity >= 10 AND od.Quantity < 50 THEN 2  

            ELSE 3  

        END,  

        od.UnitPrice * 1.05,       -- Small Quantity: 5% markup  

        od.UnitPrice * 1.03,       -- Medium Quantity: 3% markup  

        od.UnitPrice * 1.01        -- Large Quantity: 1% markup  

    ) AS AdjustedPrice  

FROM [Order Details] od;  

            
        

Create SQL query with SqlQueryBuilder 3

            
var (sql3, parameters3) = new SqlQueryBuilder()  

    .Select()  

    .Columns("od.OrderID","od.ProductID","od.Quantity","od.UnitPrice")  

    .Column(new CHOOSE(new CASE()  

            .When(new Column("od.Quantity").LessThan(1)).Then(1)  

            .When(new Column("od.Quantity").GreaterThanOrEqualeTo(10).AND(new Column("od.Quantity").LessThan(50))).Then(2)  

            .Else(3), new ColumnArithmatic("od.UnitPrice").MULTIPLY(1.05), new ColumnArithmatic("od.UnitPrice").MULTIPLY(1.03),  

                new ColumnArithmatic("od.UnitPrice").MULTIPLY(1.01)  

        )  

    , "AdjustedPrice")  

    .From("[Order Details]","od")  

    .Build();  

            
        

Query build by SqlQueryBuilder 3

            
SELECT od.OrderID,
       od.ProductID,
       od.Quantity,
       od.UnitPrice,
       CHOOSE(CASE WHEN od.Quantity < @pMAIN_2507200145500044530 THEN @pMAIN_2507200145500044531 WHEN od.Quantity >= @pMAIN_2507200145500044532
                                                                                                      AND od.Quantity < @pMAIN_2507200145500044533 THEN @pMAIN_2507200145500044534 ELSE @pMAIN_2507200145500044535 END, od.UnitPrice * @pMAIN_2507200145500044536, od.UnitPrice * @pMAIN_2507200145500044537, od.UnitPrice * @pMAIN_2507200145500044538) AS AdjustedPrice
FROM [Order Details] AS od;


            
        

Parameters (If used)

Query Results 3: