QB Select with QBAggregate .sum adds (unwanted) cast

Classic Servoy
1

Hi

I have a SQL statement like the following:

	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.

	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_ …))).

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,

2

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?

3

Hi Rob

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

Best regards, Robert

rgansevles:
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?