COALESCE SQL function

1. Usage of COALESCE to show freight cost.

SQL Server Query 1

            
 SELECT  

      OrderID,  

      CustomerID,  

      OrderDate,  

      COALESCE(Freight, 0) AS FreightCost,  

      (SELECT SUM(UnitPrice * Quantity) FROM "Order Details" WHERE OrderID = Orders.OrderID) + COALESCE(Freight, 0) AS TotalOrderCost  

  FROM Orders;  

            
        

Create SQL query with SqlQueryBuilder 1

            
 var (sql1, parameters1) = new SqlQueryBuilder()  

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

     .Column(new COALESCE(new Column("Freight"), 0), "FreightCost")  

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

             .From("[Order Details]")  

             .Where(new Where(new Column("OrderID"), SQLComparisonOperators.EQUALE_TO, new Column("Orders.OrderID")))  

         ).ADD(new COALESCE(new Column("Freight"), "0"))  

     , "TotalOrderCost")  

     .From("Orders")  

     .Build();  

            
        

Query build by SqlQueryBuilder 1

            
SELECT OrderID,
       CustomerID,
       OrderDate,
       COALESCE (Freight, @pMAIN_2507192039015378770) AS FreightCost,
       (SELECT SUM(UnitPrice * Quantity) AS TotalOrderCost
        FROM [Order Details]
        WHERE OrderID = Orders.OrderID) + COALESCE (Freight, @pMAIN_2507192039015378771) AS TotalOrderCost
FROM Orders;


            
        

Parameters (If used)

Query Results 1:

2. Usage of COALESCE to show missing order quantities.

SQL Server Query 2

            
 SELECT  

     OrderID,  

     ProductID,  

     UnitPrice,  

     COALESCE(Quantity, 0) AS Quantity,  

     UnitPrice * COALESCE(Quantity, 0) AS TotalPrice  

 FROM [Order Details];  

            
        

Create SQL query with SqlQueryBuilder 2

            
 var (sql2, parameters2) = new SqlQueryBuilder()  

     .Select().Columns("OrderID", "ProductID", "UnitPrice")  

     .Column(new COALESCE(new Column("Quantity"), 0), "Quantity")  

     .Column(new ColumnArithmatic("UnitPrice").MULTIPLY(new COALESCE(new Column("Quantity"), 0)), "TotalPrice")  

     .From("[Order Details]")  

     .Build();  

            
        

Query build by SqlQueryBuilder 2

            
SELECT OrderID,
       ProductID,
       UnitPrice,
       COALESCE (Quantity, @pMAIN_2507192039015477500) AS Quantity,
       UnitPrice * COALESCE (Quantity, @pMAIN_2507192039015477501) AS TotalPrice
FROM [Order Details];


            
        

Parameters (If used)

Query Results 2: