private void addJoinItem(SelectBuilder builder, Join join) { JoinStart start = join as JoinStart; if (start == null) { builder.AddJoin(join); return; } AliasedSource source = start.Source; Table table = source.Source as Table; if (table != null) { builder.AddTable(table, source.Alias); return; } ISelectBuilder select = source.Source as SelectBuilder; if (select != null) { builder.AddSelect(select, source.Alias); return; } Function functionCall = source.Source as Function; if (functionCall != null) { builder.AddFunction(functionCall, source.Alias); return; } throw new InvalidOperationException(); }
public void TestSelect_FunctionWithOrderingWindow() { SelectBuilder innerBuilder = new SelectBuilder(); AliasedSource table = innerBuilder.AddTable(new Table("Table")); Column column1 = table.Column("Column1"); Column column2 = table.Column("Column2"); Column column3 = table.Column("Column3"); innerBuilder.AddProjection(column1, "c1"); Function function = new Function("ROW_NUMBER"); FunctionWindow window = new FunctionWindow(); window.AddOrderBy(new OrderBy(column2)); window.AddOrderBy(new OrderBy(column3)); function.FunctionWindow = window; innerBuilder.AddProjection(function, "rn"); SelectBuilder builder = new SelectBuilder(); AliasedSource inner = builder.AddSelect(innerBuilder, "inner"); builder.AddProjection(inner.Column("c1")); builder.AddWhere(new BetweenFilter(inner.Column("rn"), new NumericLiteral(11), new NumericLiteral(20))); Formatter formatter = new Formatter(); string actual = formatter.GetCommandText(builder); string expected = "SELECT inner.c1 FROM (SELECT Table.Column1 AS c1, ROW_NUMBER() OVER (ORDER BY Table.Column2, Table.Column3) AS rn FROM Table) inner WHERE inner.rn BETWEEN 11 AND 20"; Assert.AreEqual(expected, actual, "The wrong SQL was generated."); }
public void TestSelect_FromSelect() { SelectBuilder builder = new SelectBuilder(); SelectBuilder inner = new SelectBuilder(); inner.AddProjection(new NumericLiteral(1), "Column"); builder.AddSelect(inner); builder.AddProjection(new Column("Column")); Formatter formatter = new Formatter(); string commandText = formatter.GetCommandText(builder); string expected = "SELECT Column FROM (SELECT 1 AS Column)"; Assert.AreEqual(expected, commandText, "The wrong SQL was generated."); }