## QB Select with QBAggregate .sum adds (unwanted) cast

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

### QB Select with QBAggregate .sum adds (unwanted) cast

Hi

I have a SQL statement like the following:

Code: Select all
`   var query = "\      SELECT\         sum((awq.teaching_allotment_quantity_percent - COALESCE(twq.sum_school_unit_assignment_paid_out_percent, twq.sum_school_unit_assignment_percent)) / 2) AS delta\      FROM\         actual_work_quota_per_period awq\      INNER JOIN target_work_quota_per_period_in_percent twq\         ON twq.person_id = awq.natural_person_id\         AND twq.school_unit_assignment_period_school_year = awq.teaching_allotment_period_school_year\         AND twq.school_unit_assignment_period_fraction_name = awq.teaching_allotment_period_fraction_name\      INNER JOIN fractions f\         ON f.name = awq.teaching_allotment_period_fraction_name\      WHERE\         awq.natural_person_id = " + natural_person_id + "\         AND (awq.teaching_allotment_period_school_year < " + teaching_allotment_period_school_year + "\            OR (awq.teaching_allotment_period_school_year = " + teaching_allotment_period_school_year + "\               AND f.sequence <= " + _actual_work_quota_per_period_defined_for_fractions.sequence + "))";`

with the corresponding QB Select Statement like the following. The interesting part is the query.result.add(...) line having the .sum QBAggregate.

Code: Select all
`   var query = datasources.db.hades.actual_work_quota_per_period.createSelect();   var awq = query.columns;   /** @type {QBJoin<db:/hades/target_work_quota_per_period_in_percent>} */   var join1 = query.joins.add('db:/hades/target_work_quota_per_period_in_percent', JSRelation.INNER_JOIN);   var twq = join1.columns;   join1.on      .add(twq.person_id.eq(awq.natural_person_id))      .add(twq.school_unit_assignment_period_school_year.eq(awq.teaching_allotment_period_school_year))      .add(twq.school_unit_assignment_period_fraction_name.eq(awq.teaching_allotment_period_fraction_name));   /** @type {QBJoin<db:/hades/fractions>} */   var join2 = query.joins.add('db:/hades/fractions', JSRelation.INNER_JOIN);   var f = join2.columns;   join2.on      .add(f.name.eq(awq.teaching_allotment_period_fraction_name));   query.result      .add(awq.teaching_allotment_quantity_percent.minus(query.functions.coalesce(twq.sum_school_unit_assignment_paid_out_percent, twq.sum_school_unit_assignment_percent)).divide(2).sum);   query.where         .add(awq.natural_person_id.eq(natural_person_id))      .add(query.and         .add(query.or            .add(awq.teaching_allotment_period_school_year.lt(teaching_allotment_period_school_year))            .add(query.and               .add(awq.teaching_allotment_period_school_year.eq(teaching_allotment_period_school_year))               .add(f.sequence.le(_actual_work_quota_per_period_defined_for_fractions.sequence)))));`

For a reason I don't understand, the SQL generated by the Query Builder adds because of the .sum a cast as numeric(19,0) around the column (sum(cast(((actual_work_quota_ ...))).

Code: Select all
`select   sum(cast(((actual_work_quota_per_period.teaching_allotment_quantity_percent-coalesce(targeota_per_period_in_percent.sum_school_unit_assignment_paid_out_percent, targeota_per_period_in_percent.sum_school_unit_assignment_percent))/?) as numeric(19,0)))from   actual_work_quota_per_period actual_work_quota_per_periodinner join target_work_quota_per_period_in_percent targeota_per_period_in_percent   on actual_work_quota_per_period.natural_person_id=targeota_per_period_in_percent.person_id   and actual_work_quota_per_period.teaching_allotment_period_school_year=targeota_per_period_in_percent.school_unit_assignment_period_school_year   and actual_work_quota_per_period.teaching_allotment_period_fraction_name=targeota_per_period_in_percent.school_unit_assignment_period_fraction_nameinner join fractions fractions   on actual_work_quota_per_period.teaching_allotment_period_fraction_name=fractions.namewhere   actual_work_quota_per_period.natural_person_id = ?    and (actual_work_quota_per_period.teaching_allotment_period_school_year < ?      or (actual_work_quota_per_period.teaching_allotment_period_school_year = ? and fractions.sequence <= ?))`

This makes for a wrong result, as the decimal places are always 0 instead of the correct value (if there is no cast with as as numeric(19,0))

How can I avoid or suppress this (automatic ?) cast creation by the Query Builder?

Regards,
Robert Huber
7r AG, Switzerland
SAN Developer
http://www.seven-r.ch huber

Posts: 326
Joined: Mon May 14, 2012 11:31 pm

### Re: QB Select with QBAggregate .sum adds (unwanted) cast

Servoy adds the cast to be able to deal with large numbers in the sum.
Unfortunately this is not configurable, the type numerc(19,0) is defined in the Hibernate dialect library we use.

Maybe you can multiply with 100 before summing and divide the sum by 100 to get 2 digits precision?
Rob Gansevles
Servoy rgansevles

Posts: 1880
Joined: Wed Nov 15, 2006 6:17 pm
Location: Amersfoort, NL

### Re: QB Select with QBAggregate .sum adds (unwanted) cast

Hi Rob

Thanks for the background and tip. It work as expected with multiply/divide by 1000.

Best regards, Robert

rgansevles wrote:Servoy adds the cast to be able to deal with large numbers in the sum.
Unfortunately this is not configurable, the type numerc(19,0) is defined in the Hibernate dialect library we use.

Maybe you can multiply with 100 before summing and divide the sum by 100 to get 2 digits precision?
Robert Huber
7r AG, Switzerland
SAN Developer
http://www.seven-r.ch huber

Posts: 326
Joined: Mon May 14, 2012 11:31 pm