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.");
}
