public void TestSelect_ConditionalCase_MultipleCases()
{
SelectBuilder builder = new SelectBuilder();
AliasedSource source = builder.AddTable(new Table("Table"));
Column column = source.Column("Column");
ConditionalCase options = new ConditionalCase();
options.AddBranch(new EqualToFilter(column, new NumericLiteral(0)), new StringLiteral("Sunday"));
options.AddBranch(new EqualToFilter(column, new NumericLiteral(1)), new StringLiteral("Monday"));
options.AddBranch(new EqualToFilter(column, new NumericLiteral(2)), new StringLiteral("Tuesday"));
options.AddBranch(new EqualToFilter(column, new NumericLiteral(3)), new StringLiteral("Wednesday"));
options.AddBranch(new EqualToFilter(column, new NumericLiteral(4)), new StringLiteral("Thursday"));
options.AddBranch(new EqualToFilter(column, new NumericLiteral(5)), new StringLiteral("Friday"));
options.AddBranch(new EqualToFilter(column, new NumericLiteral(6)), new StringLiteral("Saturday"));
builder.AddProjection(options);
Formatter formatter = new Formatter();
string actual = formatter.GetCommandText(builder);
string expected = "SELECT CASE WHEN Table.Column = 0 THEN 'Sunday' WHEN Table.Column = 1 THEN 'Monday' WHEN Table.Column = 2 THEN 'Tuesday' WHEN Table.Column = 3 THEN 'Wednesday' WHEN Table.Column = 4 THEN 'Thursday' WHEN Table.Column = 5 THEN 'Friday' WHEN Table.Column = 6 THEN 'Saturday' END FROM Table";
Assert.AreEqual(expected, actual, "The wrong SQL was generated.");
}
private void buildCondition(MatchResult result, ConditionalCase options)
{
MatchResult expressionResult = result.Matches[SqlGrammar.Condition.Filter];
IFilter innerFilter = buildOrFilter(expressionResult);
MatchResult valueResult = result.Matches[SqlGrammar.Condition.Value];
IProjectionItem value = (IProjectionItem)buildArithmeticItem(valueResult);
FilterGroup filterGroup = new FilterGroup(Conjunction.And, innerFilter);
filterGroup.Optimize();
options.AddBranch(filterGroup, value);
}
public void TestSelect_ConditionalCase_Else()
{
SelectBuilder builder = new SelectBuilder();
AliasedSource source = builder.AddTable(new Table("Table"));
Column column = source.Column("Column");
ConditionalCase options = new ConditionalCase();
options.AddBranch(new EqualToFilter(column, new StringLiteral("Admin")), new StringLiteral("Administrator"));
options.Default = new StringLiteral("User");
builder.AddProjection(options);
Formatter formatter = new Formatter();
string actual = formatter.GetCommandText(builder);
string expected = "SELECT CASE WHEN Table.Column = 'Admin' THEN 'Administrator' ELSE 'User' END FROM Table";
Assert.AreEqual(expected, actual, "The wrong SQL was generated.");
}
