/// <summary>
/// Creates a WhereTerm which encapsulates SQL NOT EXISTS clause
/// </summary>
/// <param name="subQuery">Sub query for the NOT EXISTS clause</param>
/// <returns></returns>
public static Cond NotExists(Select subQuery)
{
Cond oper = new Cond();
oper.Term = WhereTerm.CreateNotExists(subQuery.Query);
return(oper);
}
public virtual void ComplicatedQuery()
{
FromTerm tCustomers = FromTerm.Table("customers", "c");
FromTerm tProducts = FromTerm.Table("products", "p");
FromTerm tOrders = FromTerm.Table("orders", "o");
SelectQuery query = new SelectQuery();
query.Columns.Add(new SelectColumn("name", tCustomers));
query.Columns.Add(new SelectColumn(SqlExpression.IfNull(SqlExpression.Field("name", tCustomers), SqlExpression.Constant(SqlConstant.String("name"))), "notNull"));
query.Columns.Add(new SelectColumn(SqlExpression.Null(), "nullValue"));
query.Columns.Add(new SelectColumn("name", tProducts, "productName", SqlAggregationFunction.None));
query.Columns.Add(new SelectColumn("price", tProducts));
query.FromClause.BaseTable = tCustomers;
query.FromClause.Join(JoinType.Left, tCustomers, tOrders, "customerId", "customerId");
query.FromClause.Join(JoinType.Inner, tOrders, tProducts, "productId", "productId");
query.WherePhrase.Terms.Add(WhereTerm.CreateCompare(SqlExpression.Field("name", tCustomers), SqlExpression.String("John"), CompareOperator.Equal));
query.WherePhrase.Terms.Add(WhereTerm.CreateCompare(SqlExpression.String("Dohe"), SqlExpression.Field("name", tCustomers), CompareOperator.NotEqual));
query.WherePhrase.Terms.Add(WhereTerm.CreateCompare(SqlExpression.Field("name", tCustomers), SqlExpression.String("J%"), CompareOperator.Like));
query.WherePhrase.Terms.Add(WhereTerm.CreateCompare(SqlExpression.Date(DateTime.Now), SqlExpression.Field("date", tOrders), CompareOperator.Greater));
query.WherePhrase.Terms.Add(WhereTerm.CreateCompare(SqlExpression.Number(10), SqlExpression.Number(9), CompareOperator.Greater));
query.WherePhrase.Terms.Add(WhereTerm.CreateCompare(SqlExpression.Number(10), SqlExpression.Number(9), CompareOperator.GreaterOrEqual));
query.WherePhrase.Terms.Add(WhereTerm.CreateCompare(SqlExpression.Number(10), SqlExpression.Number(11.5), CompareOperator.LessOrEqual));
query.WherePhrase.Terms.Add(WhereTerm.CreateCompare(SqlExpression.Number(1), SqlExpression.Number(1), CompareOperator.BitwiseAnd));
WhereClause group = new WhereClause(WhereClauseRelationship.Or);
group.Terms.Add(WhereTerm.CreateBetween(SqlExpression.Field("price", tProducts), SqlExpression.Number(1), SqlExpression.Number(10)));
group.Terms.Add(WhereTerm.CreateIn(SqlExpression.Field("name", tProducts), SqlConstantCollection.FromList(new string[] { "Nail", "Hamer", "Skrewdriver" })));
group.Terms.Add(WhereTerm.CreateIn(SqlExpression.Field("name", tProducts), "select name from products"));
group.Terms.Add(WhereTerm.CreateNotIn(SqlExpression.Field("name", tProducts), SqlConstantCollection.FromList(new string[] { "Unkown" })));
group.Terms.Add(WhereTerm.CreateNotIn(SqlExpression.Field("name", tProducts), "select name from products"));
group.Terms.Add(WhereTerm.CreateIsNull(SqlExpression.Field("name", tProducts)));
group.Terms.Add(WhereTerm.CreateIsNotNull(SqlExpression.Field("name", tProducts)));
group.Terms.Add(WhereTerm.CreateExists("select productId from products"));
group.Terms.Add(WhereTerm.CreateNotExists("select productId from products"));
query.WherePhrase.SubClauses.Add(group);
query.OrderByTerms.Add(new OrderByTerm("name", tCustomers, OrderByDirection.Descending));
query.OrderByTerms.Add(new OrderByTerm("price", OrderByDirection.Ascending));
query.Distinct = true;
query.Top = 10;
RenderSelect(query);
}
/// <summary>
/// Creates a WHERE clause for a multivalued attribute
/// </summary>
/// <param name="builder">The builder for this query</param>
/// <param name="parameterNames">The parameters to evaluate against</param>
/// <returns>The WhereClause object for this query</returns>
private WhereClause CreateWhereClauseforAVPTarget(DBQueryBuilder builder, IList <string> parameterNames)
{
FromTerm fromTable;
fromTable = builder.GetNextAVPTableReference(this.SearchAttribute.TableName);
WhereClause clause = new WhereClause(WhereClauseRelationship.And);
if (this.Operator != ValueOperator.NotPresent)
{
string paramName = builder.AddParameter(this.SearchAttribute.Name);
WhereTerm attributeNameTerm = WhereTerm.CreateCompare(SqlExpression.Field("attributeName", fromTable), SqlExpression.Parameter(paramName), CompareOperator.Equal);
clause.Terms.Add(attributeNameTerm);
}
if (this.Operator == ValueOperator.IsPresent)
{
clause.Terms.Add(WhereTerm.CreateIsNotNull(SqlExpression.Field(this.SearchAttribute.ColumnName, fromTable)));
}
else if (this.Operator == ValueOperator.NotPresent)
{
string sql = this.CreateSubSelectStatementForMVNotExists(builder);
clause.Terms.Add(WhereTerm.CreateNotExists(sql));
}
else
{
WhereClause subClause = new WhereClause(WhereClauseRelationship.Or);
switch (this.Operator)
{
case ValueOperator.And:
case ValueOperator.Contains:
case ValueOperator.GreaterThan:
case ValueOperator.GreaterThanOrEq:
case ValueOperator.LessThan:
case ValueOperator.StartsWith:
case ValueOperator.EndsWith:
case ValueOperator.LessThanOrEq:
foreach (string parameterName in parameterNames)
{
subClause.Terms.Add(WhereTerm.CreateCompare(SqlExpression.Field(this.SearchAttribute.ColumnName, fromTable), SqlExpression.Parameter(parameterName), this.CompareOperator));
}
break;
case ValueOperator.Equals:
subClause.Terms.Add(WhereTerm.CreateIn(SqlExpression.Field(this.SearchAttribute.ColumnName, fromTable), parameterNames.ToCommaSeparatedString()));
break;
case ValueOperator.NotEquals:
string sql = this.CreateSubSelectStatementForMVNotEquals(builder, parameterNames);
clause.Terms.Add(WhereTerm.CreateNotExists(sql));
break;
default:
throw new InvalidOperationException();
}
clause.SubClauses.Add(subClause);
}
return(clause);
}
