Dynamic WHERE clause (to push down SELECT-OPTIONS from ABAP)

[former_member182046]

(Disclaimer: This post overlaps with a similar but not identical question I asked in the ABAP on HANA forum (http://scn.sap.com/message/13812969). This version here focuses on the HANA aspects and leaves the ABAP aspects out. Trying not to crosspost.)

Hi all,

I have some logic that dynamically builds a WHERE clause I want to use in a SELECT (inside a stored procedure). The result structure is fixed, and so are the fields used in the WHERE clause; but the number and content of the conditions is only known at runtime, depending on filter values given by a user (for those with ABAP skills: I'm pushing down some SELECT-OPTIONS from the selection screen of an ABAP report).

Suppose my Stored Procedure has a scalar input parameter through which I pass the WHERE clause, e.g.

    BUT000.PARTNER  BETWEEN 0000000048 AND 0000000060

AND BUT0ID.IDNUMBER = '...'

AND ADRCP.STREET    = '...'

What can I do with it?

These are the options I currently see:

1. execute either a fully dynamic SQL statement via EXEC or EXECUTE IMMEDIATE,
   or
2. execute a regular SELECT (which I could ideally bind to a table variable, e.g. an output parameter table) with a dynamic WHERE clause,
   or
3. 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.

My questions:

1. 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.
2. There doesn't seem to be the option to do a partly dynamic SELECT, where only the WHERE clause comes from a string. Or did I just miss it?
3. How is this kind of requirement usually handled? I can't be the first person doing this.
4. And by the way, is anyone aware of an existing 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.

Thanks a lot,

Thorsten

[lbreddemann · Accepted solution]

Hi Thorsten,

you're right, the request for dynamic SQL clauses is not new.

In fact it's pretty old - at least as old as dynamic applications with flexible UIs are around that allow again flexible data access.

The way this is usually handled (also with other DBMS) is: you create the SQL statement on the application layer and send this to the database.

That's what O/R-mapper frame works do, that's what middleware application servers do and of course that's what NetWeaver does (being such a middleware).

If you ask me, this is the place where it belongs to be, since SQL doesn't allow for this flexibility in expressions.

As often, the difficulty of a proper solution, that provides the desired flexibility to the application developer on the one hand and the performance in db processing on the other is the generality.

E.g. if the requirement is simply to create a IN-condition with a variable number of entries, you could create temporary tables and fill them with the condition variables you want in your result set.

An inner join on these temp. tables will work nicely as a filter.

That's what SAP BW does in some cases.

Straight forward and simple,

Another approach that is especially useful if you want to have the statement itself reused is to implement a code similar to the For-All-Entries mapping of the DBSL.

For your long internal table you create statements with placeholders (? or $x in HANA) and run this statement as long as enough values are present to bind to the placeholders.

A last statement can be run for the remaining values and all results of all executed statements are then unified in the client.

This of course is quite laborious to implement - but still easier than the fully flexible WHERE clause (you ABAPers are so spoiled ).

Ok, all this is just my opinion on that, and surely enough there will be others.

Concerning SAP owns business content and/or Suite on HANA afaik what they did is to keep such super-flexible stuff in NetWeaver (should DBSL and DB Optimizer deal with it) and just model the base data structures to work on (analytic/calc. views).

I find this to be a good approach so far.

Cheers, Lars

[former_member182046 · Accepted solution]

Lars,

Thank you. I can create the SQL up at the ABAP application layer and pass it down to the database - but let's talk about what happens to the query results.

Suppose the query in question is only part of a multi-step business logic implemented in HANA. The next processing step is also modeled in HANA - perhaps as another Stored Procedure. How does it get hold of the results?

Would I have to do it like this:

1. ABAP.Program_1 drops and creates HANA.Procedure_2 (with an outbound table parameter and the dynamic SQL)
2. ABAP.Program_1 calls HANA.Procedure_1
3. HANA.Procedure_1 calls HANA.Procedure_2

No, this can't be the solution: In a multi-user/multi-session environment, this would be terrible if the name of HANA.Procedure_2 was fixed.

Or: HANA.Procedure_2 could have a GUID as a name, which would be passed as a parameter from ABAP.Program_1 to HANA.Procedure_1 and called dynamically. But I don't think dynamic procedure calls are supported. Not a solution either.

However:

1. ABAP.Program_1 creates a local temporary table with a fixed name
2. ABAP.Program_1 generates and calls a SQL query that fills the temporary table
3. ABAP.Program_1 calls HANA.Procedure_1
4. HANA.Procedure_1 calls HANA.Procedure_2

What do you think - problem solved? Could this be the best general solution for any query or Stored Procedure wishing to consume the result of any dynamic SQL - shove the result set into a local temporary table?

Best,

Thorsten

[lbreddemann · Accepted solution]

Personally, I actually wouldn't try to generate the dynamic part down to HANA.

I'd rather try to build models that later on get consumed by the generated SQL statement.

If you employ the temporary table approach, then unique naming is obviously required, but dropping and recreating is not.

It's sufficient to create the tables and keep an eye on who's using what table at the moment and truncate the table afterwards.

That's what BW does.

But currently I (again, this is my personal opinion) I wouldn't strive for a general best solution but rather for something that works in specific cases.

- Lars

[former_member182046 · Accepted solution]

Looks like I found a solution that is comfortable enough. See the following sample code:

-- DDL (executed once during deployment)
CREATE GLOBAL TEMPORARY TABLE TFR_DYNAMIC_RESULT
  AS ( SELECT * FROM V_CLAIMS_V2_RELEVANT_ITEMS ) WITH NO DATA;
CREATE PROCEDURE TFR_DYNAMIC_SELECT ( IN in_clause VARCHAR )
  LANGUAGE SQLSCRIPT
  AS
BEGIN
  TRUNCATE TABLE TFR_DYNAMIC_RESULT;
  EXEC 'INSERT INTO TFR_DYNAMIC_RESULT ( SELECT * FROM V_CLAIMS_V2_RELEVANT_ITEMS ' || :in_clause || ')';      
  t_result = SELECT * FROM TFR_DYNAMIC_RESULT;
-- further processing of :t_result
  SELECT * FROM :t_result;
END;

-- Executed for each call by ABAP - ABAP handing down the dynamic WHERE clause 
CALL TFR_DYNAMIC_SELECT('WHERE CLAIM = ''09561000000001437''');
-- Example: Empty WHERE clause works, too
CALL TFR_DYNAMIC_SELECT('');

-- Clean-up - executed once during undeployment
DROP PROCEDURE TFR_DYNAMIC_SELECT;
DROP TABLE TFR_DYNAMIC_RESULT;

Just too bad we can't fulfill the READS SQL DATA restriction here, so no reuse in HANA modeler views possible.

Cheers,

Thorsten

[lbreddemann · Accepted solution]

Hi Thorsten,

I thought about this a bit more again.

Still I think that since we already have a perfect mapper for OpenSQL code and HANA native SQL, the dbsl of the workprocesses, we should use them.

So what's wrong with this approach

1. create a temporary table to contain all primary keys of the rows to work on. (this one doesn't need to be a global temporary table)
2. fill this temporary table via a regular
   INSERT FROM (SELECT <primkey-cols> FROM table WHERE <your where clause here>)
3. call your procedure and either implicitly take the temp. table as given or use a table type parameter.
4. in your procedure do your work and return the result set(s).

That way, you can reuse the WHERE condition translation, don't need dynamic SQL and can eventually use READS SQL DATA (and thereby providing the option for better parallel use of the procedure).

For your multi step processing example: why wouldn't you just write a wrapper SQLScript procedure that takes the selection input and than calls all required subsequent procedures?

In that case, you can use table variables and HANA will take care of the value passing between the procedures,

Hope that makes sense... Lars