I’m having trouble trying to explain my necessity, so I’ll describe the scenario.
Scenario:
- Product A has a maximum production of 125KG at a time.
- The operator received a production order of 1027,5KG of product A.
- The operator have to calculate how many rounds he’ll have to manufacture and adjust the components quantity for each round.
We want to create a report where this calculations are already done and what we believe would be the first step, based on the values of this scenario, is to return something like this:
ROUND QUANTITY(KG)
1 125
2 125
3 125
4 125
5 125
6 125
7 125
8 125
9 27,5
After that, the recalculation of the components could be done with simple operations.
The problem is that we couldn’t think of a way to get the desired return and we also couldn’t think of a different way of achieving the said report.
All we could do is get the integer part of the division
SELECT FLOOR(1027.5/125) AS "TEST" FROM DUMMY
and the remainder
SELECT MOD(1027.5,125) AS "TEST" FROM DUMMY
We are using:
- SAP HANA SQL
- Crystal Reports
- SAP B1
Any help would be appreciated
Thanks in advance!
Advertisement
Answer
There are several ways to achieve want you described.
One way is to translate the requirement into a function that takes the two input parameter values and returns the table of production rounds.
This can look like this:
create or replace function production_rounds( IN max_production_volume_per_round decimal (10, 2) , IN production_order_volume decimal (10, 2) ) returns table ( production_round integer , production_volume decimal (10, 2)) as begin declare rounds_to_produce integer; declare remainder_production_volume decimal (10, 2); rounds_to_produce := floor( :production_order_volume / :max_production_volume_per_round); remainder_production_volume := mod(:production_order_volume, :max_production_volume_per_round); return select /* generate rows for all "max" rounds */ s.element_number as production_round , :max_production_volume_per_round as production_volume from series_generate_integer (1, 1, :rounds_to_produce + 1) s UNION ALL select /* generate a row for the final row with the remainder */ :rounds_to_produce + 1 as production_round , :remainder_production_volume as production_volume from dummy where :remainder_production_volume > 0.0; end;
You can use this function just like any table – but with parameters:
select * from production_rounds (125 , 1027.5) ; PRODUCTION_ROUND PRODUCTION_VOLUME 1 125 2 125 3 125 4 125 5 125 6 125 7 125 8 125 9 27.5
The bit that probably needs explanation is SERIES_GENERATE_INTEGER
. This is a HANA-specific built-in function that returns a number of records from a “series”. Series here is a sequence of periods within a min and max limit and with a certain step-size between two adjacend periods.
More on how this works can be found in the HANA reference documentation, but for now just say, this is the fastest way to generate a result set with X rows.
This series-generator is used to create all “full” production rounds.
For the second part of the UNION ALL
then creates just a single row by selecting from the built-in table DUMMY
(DUAL
in Oracle) which is guaranteed to only have a single record.
Finally, this second part needs to be “disabled” if there actually is no remainder, which is done by the WHERE
clause.