I'm using SAP HANA and would like to join the output results of a procedure call inside a loop, is there anyway to do this?
Something similar to this: But the problem is the duplicate attribute name
FOR i IN 1..:nYEARS DO
CALL FUTUREREVENUES (:i,resulttemp);
result = SELECT * FROM :result t1
INNER JOIN :resulttemp t2
ON t1.ID = t2.ID
END FOR;
Nope, the main problem is not the duplicate attribute name; SAP HANA actually allows that in a projection, as long as the attribute is uniquely identifiable.
What you are trying to do here is not a good idea in any statically typed language, such as SQL. Basically, the structure of your return type result depends on the input, i.e. how often the loop gets executed.
What you would need here is some way of dynamic SQL that adjusts the projected column names with each iteration. While this appears to be a straightforward approach, it's actually the opposite.
Every consumer of the result data is forced to accept whatever the table comes out of this loop, without even knowing how the projected columns would be named.
That's hard to handle and makes the solution very little reusable.
An alternative approach could be to have a fixed output table structure, say 5 years forecast (if you can even predict anything that far with any certainty), and no dynamic column names.
Instead, you could e.g. name the columns FC+Year1, FC+Year2, ...
That way, the output structure stays the same and all the client application has to do, is to match the output labels according to the baseline year (the input into your prediction).
