STR SQL function

1. Usage of STR to show formatted total amount.

SQL Server Query 1

            
SELECT TOP 10  

    o.OrderID,  

    c.CompanyName,  

    'Total: $' +  

    CASE  

        WHEN SUM(od.UnitPrice * od.Quantity * (1 - od.Discount)) > 1000  

            THEN '**' + STR(SUM(od.UnitPrice * od.Quantity * (1 - od.Discount)), 10, 2)  

        ELSE STR(SUM(od.UnitPrice * od.Quantity * (1 - od.Discount)), 10, 2)  

    END AS FormattedTotalAmount  

FROM  

    Orders o  

JOIN  

    Customers c ON o.CustomerID = c.CustomerID  

JOIN  

    [Order Details] od ON o.OrderID = od.OrderID  

GROUP BY  

    o.OrderID,  

    c.CompanyName  

ORDER BY  

    o.OrderID;  

            
        

Create SQL query with SqlQueryBuilder 1

            
var (sql1, parameters1) = new SqlQueryBuilder()              

    .Select().Top(10)  

    .Columns("o.OrderID", "c.CompanyName")  

    .Column(new CONCAT(  

        "Total: $",  

        new CASE()  

            .When(new SUM(new ColumnArithmatic("od.UnitPrice").MULTIPLY("od.Quantity").MULTIPLY()  

                .StartBracket(1).SUBTRACT("od.Discount").EndBracket()).GreaterThan(100))  

            .Then(new CONCAT("**", new STR(new SUM(  

                new ColumnArithmatic().Column("od.UnitPrice").MULTIPLY().Column("od.Quantity").MULTIPLY().StartBracket()  

                    .Value(1).SUBTRACT().Column("od.Discount").EndBracket()  

            ), 10, 2)))  

            .Else(new STR(  

                    new SUM(new ColumnArithmatic().Column("od.UnitPrice").MULTIPLY().Column("od.Quantity").MULTIPLY()  

                        .StartBracket().Value(1).SUBTRACT().Column("od.Discount").EndBracket()), 10, 2))  

    ), "FormattedTotalAmount")  

    .From("Orders", "o")  

    .Join(new List<IJoin>()  

    {  

        new INNERJOIN().TableName(new Table("Customers","c"))  

            .On(new Column("o.CustomerID"), SQLComparisonOperators.EQUALE_TO, new Column("c.CustomerID")),  

        new INNERJOIN().TableName(new Table("[Order Details]", "od"))  

            .On(new Column("o.OrderID"), SQLComparisonOperators.EQUALE_TO, new Column("od.OrderID"))  

    })  

    .GroupBy(new GroupBy(new Column("o.OrderID"), new Column("c.CompanyName")))  

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

    .Build();  

            
        

Query build by SqlQueryBuilder 1

            
SELECT TOP 10 o.OrderID,
              c.CompanyName,
              CONCAT(@pMAIN_2507200213505016790, CASE WHEN SUM(od.UnitPrice * od.Quantity * (@pMAIN_2507200213505016791 - od.Discount)) > @pMAIN_2507200213505016792 THEN CONCAT(@pMAIN_2507200213505016793, STR(SUM(od.UnitPrice * od.Quantity * (@pMAIN_2507200213505016794 - od.Discount)), @pMAIN_2507200213505016795, @pMAIN_2507200213505016796)) ELSE STR(SUM(od.UnitPrice * od.Quantity * (@pMAIN_2507200213505016797 - od.Discount)), @pMAIN_2507200213505016798, @pMAIN_2507200213505016799) END) AS FormattedTotalAmount
FROM Orders AS o
     INNER JOIN
     Customers AS c
     ON o.CustomerID = c.CustomerID
     INNER JOIN
     [Order Details] AS od
     ON o.OrderID = od.OrderID
GROUP BY o.OrderID, c.CompanyName
ORDER BY o.OrderID ASC;


            
        

Parameters (If used)

Query Results 1: