I'm optimizing a long-running ABAP report that uses some SELECT-OPTIONS/RANGES for SELECTS that are currently implemented as OpenSQL queries in the ABAP report itself.
s_partn FOR but000-partner.
SELECT * FROM but000 INTO TABLE lt_but000 WHERE PARTNER IN s_parn.
As I'm pushing down large parts of the ABAP logic into SQLscript Stored Procedures in HANA, I wonder what to do with the SELECT-OPTIONs. As HANA doesn't seem to support using RANGES (SIGN,OPTION, LOW, HIGH) in WHERE conditions, I'll have to
- convert the RANGES to a HANA-compliant WHERE clause string (either in HANA or in ABAP)
- hand it down from ABAP to HANA (either the RANGES tables or the string resulting from the conversion, depending on where I convert it)
- execute either a fully dynamic SQL statement via EXEC or EXECUTE IMMEDIATE,
- execute a regular SELECT (which I could ideally bind to a table variable, e.g. an output parameter table) with a dynamic WHERE clause,
- use really nasty hacks such as dynamically creating Stored Procedure with a static SELECT in (either from ABAP via ADBC or within HANA) - I'd hate to have to do that.
A number of questions:
- Is anyone aware of an existing function module or service class in ABAP or stored procedure in HANA that does the conversion from RANGES to HANA-compliant WHERE clauses? I could write one but it seems like such a classic reuse case that I hope something exists already.
- If I use fully dynamic SQL, the problem with EXEC and EXECUTE IMMEDIATE seems to be to get hold of the result set, which I need to bind to a table variable in either the same procedure or in the calling procedure for further processing. Since result sets generated with either statement cannot be bound to table variables directly and do not even show up when calling the procedure WITH OVERVIEW, this option seems to be ruled out.
- There doesn't seem to be the option to do a partly dynamic SELECT, where only the WHERE clause comes from a string.
- How is this kind of requirement usually handled? I can't be the first person doing this. 😊
Thanks a lot,