NULLIF SQL function

1. Usage of NULLIF to show Stock to Order Ration.

SQL Server Query 1

            
 SELECT  

     ProductID,  

     ProductName,  

     UnitsInStock,  

     UnitsOnOrder,  

     --  Avoid division by zero if UnitsOnOrder is 0  

     CASE  

         WHEN UnitsOnOrder = 0 THEN 0  -- Or some other appropriate value  

         ELSE UnitsInStock / NULLIF(UnitsOnOrder, 0)  

     END AS StockToOrderRatio  

 FROM Products  

 ORDER BY ProductID;  

            
        

Create SQL query with SqlQueryBuilder 1

            
 var (sql1, parameters1) = new SqlQueryBuilder()  

     .Select()  

     .Columns("ProductID", "ProductName", "UnitsInStock", "UnitsOnOrder")  

     .Column(new CASE()  

         .When(new Column("UnitsOnOrder").Equale(0))  

         .Then(0)  

         .Else(new ColumnArithmatic("UnitsInStock").DIVIDE(new NULLIF(new Column("UnitsOnOrder"), 0)))  

         , "StockToOrderRatio")  

     .From("Products")  

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

     .Build();  

            
        

Query build by SqlQueryBuilder 1

            
SELECT ProductID,
       ProductName,
       UnitsInStock,
       UnitsOnOrder,
       CASE WHEN UnitsOnOrder = @pMAIN_2507192025015353300 THEN @pMAIN_2507192025015353301 ELSE UnitsInStock / NULLIF (UnitsOnOrder, @pMAIN_2507192025015353302) END AS StockToOrderRatio
FROM Products
ORDER BY ProductID ASC;


            
        

Parameters (If used)

Query Results 1:

2. Usage of NULLIF to show compare Phone and Fax.

SQL Server Query 2

            
 SELECT  

     CustomerID,  

     ContactName,  

     ContactTitle,  

     Fax,  

     Phone,  

     --  Compare Phone and Fax.  If they are the same, return NULL  

     NULLIF(Phone, Fax) AS PhoneFaxDifference  

 FROM Customers  

 WHERE NULLIF(Phone, Fax) IS NOT NULL  

 ORDER BY CustomerID;  

            
        

Create SQL query with SqlQueryBuilder 2

            
 var (sql2, parameters2) = new SqlQueryBuilder()  

     .Select()  

     .Columns("CustomerID", "ContactName", "ContactTitle", "Fax", "Phone")  

     .Column(new NULLIF(new Column("Phone"), new Column("Fax")), "PhoneFaxDifference")  

     .From("Customers")  

     .Where(new Where(new IS_NOT_NULL(new NULLIF(new Column("Phone"), new Column("Fax")))))  

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

     .Build();  

            
        

Query build by SqlQueryBuilder 2

            
SELECT CustomerID,
       ContactName,
       ContactTitle,
       Fax,
       Phone,
       NULLIF (Phone, Fax) AS PhoneFaxDifference
FROM Customers
WHERE NULLIF (Phone, Fax) IS NOT NULL
ORDER BY CustomerID ASC;


            
        

Parameters (If used)

Query Results 2: