public void TestSelect_FunctionWithStartFraming()
        {
            SelectBuilder builder = new SelectBuilder();
            AliasedSource sale = builder.AddTable(new Table("sale"));
            AliasedProjection productId = builder.AddProjection(sale.Column("prod_id"));
            AliasedProjection monthNumber = builder.AddProjection(sale.Column("month_num"));
            AliasedProjection sales = builder.AddProjection(sale.Column("sales"));

            Function function = new Function("SUM", sales.ProjectionItem);
            FunctionWindow window = new FunctionWindow();
            window.AddPartition(productId);
            window.AddOrderBy(new OrderBy(monthNumber));
            window.Frame = new PrecedingOnlyWindowFrame(new PrecedingBoundFrame(12));
            function.FunctionWindow = window;
            builder.AddProjection(function);

            Formatter formatter = new Formatter();
            string actual = formatter.GetCommandText(builder);
            string expected = "SELECT sale.prod_id, sale.month_num, sale.sales, SUM(sale.sales) OVER (PARTITION BY sale.prod_id ORDER BY sale.month_num ROWS 12 PRECEDING) FROM sale";
            Assert.AreEqual(expected, actual, "The wrong SQL was generated.");
        }
 private Function buildFunctionCall(MatchResult result)
 {
     MatchResult functionNameResult = result.Matches[SqlGrammar.FunctionCall.FunctionName];
     List<string> parts = new List<string>();
     buildMultipartIdentifier(functionNameResult, parts);
     Namespace qualifier = getNamespace(parts.Take(parts.Count - 1));
     string functionName = parts[parts.Count - 1];
     Function function = new Function(qualifier, functionName);
     MatchResult argumentsResult = result.Matches[SqlGrammar.FunctionCall.Arguments];
     if (argumentsResult.IsMatch)
     {
         ValueList arguments = new ValueList();
         buildValueList(argumentsResult, arguments);
         foreach (IProjectionItem value in arguments.Values)
         {
             function.AddArgument(value);
         }
     }
     MatchResult windowResult = result.Matches[SqlGrammar.FunctionCall.Window.Name];
     if (windowResult.IsMatch)
     {
         FunctionWindow window = new FunctionWindow();
         MatchResult partitioning = windowResult.Matches[SqlGrammar.FunctionCall.Window.Partitioning.Name];
         if (partitioning.IsMatch)
         {
             MatchResult valueListResult = partitioning.Matches[SqlGrammar.FunctionCall.Window.Partitioning.ValueList];
             ValueList valueList = new ValueList();
             buildValueList(valueListResult, valueList);
             foreach (IProjectionItem value in valueList.Values)
             {
                 window.AddPartition(value);
             }
         }
         MatchResult ordering = windowResult.Matches[SqlGrammar.FunctionCall.Window.Ordering.Name];
         if (ordering.IsMatch)
         {
             MatchResult orderByListResult = ordering.Matches[SqlGrammar.FunctionCall.Window.Ordering.OrderByList];
             buildOrderByList(orderByListResult, window.OrderByList);
         }
         MatchResult framing = windowResult.Matches[SqlGrammar.FunctionCall.Window.Framing.Name];
         if (framing.IsMatch)
         {
             MatchResult precedingOnlyFrameResult = framing.Matches[SqlGrammar.FunctionCall.Window.Framing.PrecedingFrame];
             if (precedingOnlyFrameResult.IsMatch)
             {
                 IPrecedingFrame precedingFrame = buildPrecedingFrame(precedingOnlyFrameResult);
                 window.Frame = new PrecedingOnlyWindowFrame(precedingFrame);
             }
             MatchResult betweenFrameResult = framing.Matches[SqlGrammar.FunctionCall.Window.Framing.BetweenFrame.Name];
             if (betweenFrameResult.IsMatch)
             {
                 MatchResult precedingFrameResult = betweenFrameResult.Matches[SqlGrammar.FunctionCall.Window.Framing.BetweenFrame.PrecedingFrame];
                 IPrecedingFrame precedingFrame = buildPrecedingFrame(precedingFrameResult);
                 MatchResult followingFrameResult = betweenFrameResult.Matches[SqlGrammar.FunctionCall.Window.Framing.BetweenFrame.FollowingFrame];
                 IFollowingFrame followingFrame = buildFollowingFrame(followingFrameResult);
                 window.Frame = new BetweenWindowFrame(precedingFrame, followingFrame);
             }
             MatchResult frameTypeResult = framing.Matches[SqlGrammar.FunctionCall.Window.Framing.FrameType];
             window.Frame.FrameType = buildFrameType(frameTypeResult);
         }
         function.FunctionWindow = window;
     }
     return function;
 }
        public void TestSelect_FunctionWithPartitioning()
        {
            SelectBuilder builder = new SelectBuilder();
            AliasedSource table = builder.AddTable(new Table("Employee"));
            AliasedProjection employeeType = builder.AddProjection(table.Column("Type"), "Type");

            Function function = new Function("COUNT", new NumericLiteral(1));
            FunctionWindow window = new FunctionWindow();
            window.AddPartition(employeeType);
            function.FunctionWindow = window;
            builder.AddProjection(function, "Count");

            Formatter formatter = new Formatter();
            string actual = formatter.GetCommandText(builder);
            string expected = "SELECT Employee.Type AS Type, COUNT(1) OVER (PARTITION BY Employee.Type) AS Count FROM Employee";
            Assert.AreEqual(expected, actual, "The wrong SQL was generated.");
        }