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

Postby huber » Wed Jun 27, 2018 11:21 am

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_period
inner 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_name
inner join fractions fractions
   on actual_work_quota_per_period.teaching_allotment_period_fraction_name=fractions.name
where
   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
User avatar
huber
 
Posts: 516
Joined: Mon May 14, 2012 11:31 pm

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

Postby rgansevles » Fri Jul 20, 2018 12:31 pm

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
User avatar
rgansevles
 
Posts: 1927
Joined: Wed Nov 15, 2006 6:17 pm
Location: Amersfoort, NL

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

Postby huber » Tue Jul 31, 2018 10:53 am

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
User avatar
huber
 
Posts: 516
Joined: Mon May 14, 2012 11:31 pm


Return to Programming with Servoy

Who is online

Users browsing this forum: No registered users and 4 guests