Query Builder group by cannot find column alias

Questions and answers on designing your Servoy solutions, database modelling and other 'how do I do this' that don't fit in any of the other categories

Query Builder group by cannot find column alias

Postby pitc » Sun May 30, 2021 9:25 pm

The group by clause fails to find the column alias defined in a complex result part.
In the result part I define:
Code: Select all
   q.result
                 ...
      .add(q.getColumn(edis_alias,'daily_sales').divide(q.getColumn(edis_alias,'daily_units').nullif(0)),'avg_unit_pr') //NOTE need check for div by 0)


In the group by I define:
Code: Select all
   q.groupBy
      .add(...)
                 ...
      .add(q.getColumn(edis_alias,'avg_unit_pr'))

Before I added these lines to the result and group by part the query runs fine.
The error message is
Cannot find column 'avg_unit_pr' in data source ....

Where the datasource is the table: edis_alias.
I am thinking that the alias 'avg_unit_pr' has become lost or assigned to some unknown datasource or my code is incorrect....the equivalent sql runs fine.
Any ideas are welcomed before I submit as a Jira issue.
Tom
Tom
prospect-saas.com
pitc
 
Posts: 85
Joined: Thu Nov 14, 2019 2:22 pm
Location: Ottawa, Ontario, Canada

Re: Query Builder group by cannot find column alias

Postby rgansevles » Fri Jun 04, 2021 10:16 am

Tom,

In a simple case it seems to work fine:

Code: Select all
var q = datasources.db.example_data.book_nodes.createSelect('mytablealias');
q.result.add(q.getColumn('mytablealias', 'label_text')).add(q.columns.node_id.count)
q.groupBy.add(q.getColumn('mytablealias', 'label_text'));


this results in sql
Code: Select all
select mytablealias.label_text, count(mytablealias.node_id) from book_nodes mytablealias group by  mytablealias.label_text


Can you show a complete example where it goes wrong?

Rob
Rob Gansevles
Servoy
User avatar
rgansevles
 
Posts: 1907
Joined: Wed Nov 15, 2006 6:17 pm
Location: Amersfoort, NL


Return to Programming with Servoy

Who is online

Users browsing this forum: No registered users and 6 guests