Hi LLBLGen,

I'm trying to create the following query:

SELECT COUNT(*) AS NumberOfLinks
FROM Links
WHERE SourceIncidentId = 4 OR TargetIncidentId = 4
HAVING COUNT(*) > 2

But can't seem to produce a having clause without a group by field. It turns the having clause in a where clause but that is not the same

I currently have this:

EntityField2 countField = LinkFields.LinkId.SetAggregateFunction(AggregateFunction.CountRow);

EntityFields2 fields = new EntityFields2(1) { countField };

RelationPredicateBucket bucket = new RelationPredicateBucket(LinkFields.SourceIncidentId == 4 | LinkFields.TargetIncidentId == 4);

GroupByCollection groupBy = new GroupByCollection();
groupBy.HavingClause = new PredicateExpression(countField > 2);

DataTable table = new DataTable();
this.AdapterToUse.FetchTypedList(fields, table, bucket, 0, null, true, groupBy);

But this generates the following sql:

SELECT COUNT(*) AS LinkId 
FROM Links 
WHERE SourceIncidentId = 200 OR TargetIncidentId = 200

So i'm missing my having clause with the count

This query will return 1 row. So are you saying that if the count)*) > 2 you want to get that 1 row and if it is <= 2 you want no rows back?

Seems you could just look at the result row and ignore it if the count(*) is greater than 2

The following SQL will yield the same result, could you please try implementing it.

SELECT *
FROM
{
SELECT COUNT(*) AS NumberOfLinks
FROM Links
WHERE SourceIncidentId = 4 OR TargetIncidentId = 4
} q
WHERE q.NumberOfLinks > 2

arschr wrote:

This query will return 1 row. So are you saying that if the count)*) > 2 you want to get that 1 row and if it is <= 2 you want no rows back?

Seems you could just look at the result row and ignore it if the count(*) is greater than 2

The query is a part of an EXISTS statement, and the query can return a lot more rows that just 1. And the having is to test if the row meets the condition that the user has provided.

Walaa wrote:

The following SQL will yield the same result, could you please try implementing it.

SELECT *
FROM
{
SELECT COUNT(*) AS NumberOfLinks
FROM Links
WHERE SourceIncidentId = 4 OR TargetIncidentId = 4
} q
WHERE q.NumberOfLinks > 2

Its a bit difficult to put the query in a subquery because it is a generated query, and the space that i can play with is about 1 query But is it not possible to only put a having clausing in a query without the group by?

No, not supported.

You might also add the PK to the select list, so that you can use GroupBy on the PK, and hence you can use Having.

Walaa wrote:

No, not supported.

You might also add the PK to the select list, so that you can use GroupBy on the PK, and hence you can use Having.

That unfortunately does not work, because it will for example return 4 times a 1 instead of 1 time a 4

SELECT COUNT(*) AS NumberOfLinks
FROM Links
WHERE SourceIncidentId = 26252 OR TargetIncidentId = 26252
GROUP BY LinkId
--HAVING COUNT(*) > 2

results in

1 1 1 1

and this

SELECT COUNT(*) AS NumberOfLinks
FROM Links
WHERE  SourceIncidentId = 26252 OR TargetIncidentId = 26252
--GROUP BY LinkId
HAVING COUNT(*) > 2

results in

4

Kiltux wrote:

Walaa wrote:

The following SQL will yield the same result, could you please try implementing it.

SELECT *
FROM
{
SELECT COUNT(*) AS NumberOfLinks
FROM Links
WHERE SourceIncidentId = 4 OR TargetIncidentId = 4
} q
WHERE q.NumberOfLinks > 2

Its a bit difficult to put the query in a subquery because it is a generated query, and the space that i can play with is about 1 query But is it not possible to only put a having clausing in a query without the group by?

We only support having with group by so the user is forced to use aggregates as that's the main reason to use having in the first place (a having clause without aggregates makes no sense). A query without a having clause has an implicit group by() clause in most databases and some don't support having without group by, hence we only support it on a group by clause.

The workaround Walaa gave is how to get what you want, as a having clause is actually just a predicate on the set containing the having clause, and can be formulated just as the query you have.

Please show us the code you have and what version you're working with so we can suggest how to implement the workaround (e.g. through query spec which is the easiest).

I work with LLBLGen 3.5

I cannot show the exact code because it is a part of our dynamic query system, and the fields are generated by what the user selects in the interface and thereby the conditions.

This is the sample code i can translate back to our code

EntityField2 countField = LinkFields.LinkId.SetAggregateFunction(AggregateFunction.CountRow);
EntityFields2 fields = new EntityFields2(1) { countField };

RelationPredicateBucket bucket = new RelationPredicateBucket(LinkFields.SourceIncidentId == 4 | LinkFields.TargetIncidentId == 4);

GroupByCollection groupBy = new GroupByCollection();
groupBy.HavingClause = new PredicateExpression(countField > 2);

DataTable table = new DataTable();
this.AdapterToUse.FetchTypedList(fields, table, bucket, 0, null, true, groupBy);
table.Dump();

And i must use the LLBLGen Pro Framework, and cannot use the QuerySpec framework because of the nature of the dynamic query.

I have created a temporary solution for the problem.

public class HavingClause : GroupByCollection
{   
    public HavingClause() : base(new EntityField2("()", null)) { }

    public override string ToQueryText(bool ignoreExpressions)
    {
        string text = base.ToQueryText(ignoreExpressions);
        if(text.StartsWith(".[()]"))
            text = text.Replace(".[()]", "()");
        return text;
    }
}

I realize that this isn't the ideal solution, but in this case it is easier to implement on my side instead of adjusting our dynamic query builder to support the subquery construction.

You can create a DerivedTableDefinition from the elements you now use to build the query, and assign it to a DynamicRelation which you then add to a RelationCollection, so it's wrapped into the main query.

Anyway, your workaround works too. You can make it easier though:

public class HavingClauseWithEmptyGroupBy : GroupByCollection
{   
    public HavingClauseWithEmptyGroupBy () : base() { }

    public override string ToQueryText(bool ignoreExpressions)
    {
        this.HavingClause.DatabaseSpecificCreator = this.DatabaseSpecificCreator;
        var toReturn = string.Format(" HAVING {0}", this.HavingClause.ToQueryText(true ) );
        this.Parameters.AddRange( this.HavingClause.Parameters );
        return toReturn;
    }
}

No need to hack around it with a dummy field, this simply emits the having clause without the group by elements. The code in the ToQueryText is copied from the GroupByCollection.

Otis wrote:

You can create a DerivedTableDefinition from the elements you now use to build the query, and assign it to a DynamicRelation which you then add to a RelationCollection, so it's wrapped into the main query.

Anyway, your workaround works too. You can make it easier though:

public class HavingClauseWithEmptyGroupBy : GroupByCollection
{   
    public HavingClauseWithEmptyGroupBy () : base() { }

    public override string ToQueryText(bool ignoreExpressions)
    {
        this.HavingClause.DatabaseSpecificCreator = this.DatabaseSpecificCreator;
        var toReturn = string.Format(" HAVING {0}", this.HavingClause.ToQueryText(true ) );
        this.Parameters.AddRange( this.HavingClause.Parameters );
        return toReturn;
    }
}

No need to hack around it with a dummy field, this simply emits the having clause without the group by elements. The code in the ToQueryText is copied from the GroupByCollection.

Ah thank you it is indeed better, i didn't knew what i would break if i didn't leave the construction as it is