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