Using an alias in a group by

Questions
1

Is it possible to add a result with an alias and then group by that alias?

query.result.add(
			query.case.when(
				query.and
				.add(query.columns.type.eq(1))
				.add(joinQuery.columns.uuid.not.isnull)
				).then(query.columns.creation_date.year)
				.else(query.columns.cancelled_date.year)
		
		, 'compare_date_year')

queryPolicyCanLap.groupBy....

I’ve tried the addColumn but that doesn’t work as I think that’s looking for a field in the table

2

Hi

I have the same question regarding ORDER BY. The SQL result includes:

query.result.add(query.case
	.when(a.a1.eq(1)).then(1)
	.when(a.a2.eq(4)).then(1)
	.when(b.b2.eq(1)).then(1)
	.else(0).min, 'has_passed')

How can I sort by this column? Any help will be appreciated a lot.

Regards
Birgit

3

How about (out of my head)

query.sort.add(query.case
	.when(a.a1.eq(1)).then(1)
	.when(a.a2.eq(4)).then(1)
	.when(b.b2.eq(1)).then(1)
	.else(0).min.asc)
4

Thank you Patrick

The queries SQL (getSQL) can be executed in our database. But the execution of the QBSelect fails with the following error message:

Unknown errorCode 100 SQL Anywhere Error -854: Function or column reference to 'max' in the ORDER BY clause is invalid

Yet, this morning I succeeded by rewriting the query. I now use a subquery to retrieve the aggregate. Then I join the main query with the subquery and use the aggregate column with getColumn() for sorting:

query.sort
    .add(joinSub.getColumn('has_passed'))

Thank you again for the idea. Best regards
Birgit