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: 312
Joined: Mon May 14, 2012 11:31 pm

Return to Programming with Servoy

Who is online

Users browsing this forum: No registered users and 6 guests

cron