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,