With HANA all around (speaking about the HANA database, not HANA XS or Hana Cloud) I as ABAP developer expect myself needing to leverage more database features. This consequently means more to code more SQL – and this implies more headache to me. My brain more used to more imperative commands instead of declarative expressions.
But technology does not care about my brain, so I have to get it used to this type of programming. Luckily, I don’t need a HANA-DB in order to train myself, so I started to write a short series about how to use SQL – oh wait, it’s now called “code pushdown”. This makes it easier to sell and might trick my brain that this is not veeeery old stuff which I should have been knowing for a long time but funky new stuff which is worth investing time into.
This first post is about the very basic of joins – particularly LEFT OUTER JOINs in combination with restrictions.
Business background
In our application, the database scheme is quite well normalized (don’t ask me which normal form it is, but it should be the third one or higher, I was never eager to differentiate further). However; to the user, the data is presented in a denormalized form. While transactionally, the dependent data is retrieved via an association, we also need to enable finding instances where dependent data matches some criteria. However, the non-existence of this dependent data must not prevent finding instances when not searching for attributes of the dependent data.
Implementation alternatives
Reading the requirement, the first thing that comes to every ABAPer’s mind is a database view. Go to se11, model your relations, use OpenSQL – that’s it. Unluckily not.
An DDIC DB-view always joins tables with an INNER JOIN. This means that the right hand side table (RHST) needs to provide matching entries for each instance of the Left HSTas prerequisite for the joined data to exist. This makes DDIC DB views unusable for all those selections, where data shall be searchable which has a minimum cardinality of 0. Thus, specifying the join as LEFT OUTER at runtime seems to be the only alternative. However, as we are being told by the OPenSQL-exception, placing a restriction on the RHST via OpenSQL is also not possible. Both limitations – as far as I know – shall be obsolete when using Core data services (CDS). But as this is only available from 7.40 upwards, it can only be subject of another post.
Until then, in our project we were forced to used native SQL based on LEFT OUTER JOINs in order to fulfill those requirements.
The INITIAL issue
After we had implemented a generic query class executing the native SQL, we felt quite comfortable having found a nice solution. Until the first tickets appeared in out bug tracking tool. As we’re using a floorplan manager based UI, our users were enjoying the “new” search options – particularly the “is initial” and the “is not initial” option were attracting a lot of affection: “oh, this makes it so easy for me to find a … where no purchasing requisition has been created yet”. Of course! “But it does not work!”. The reason for that was the custom table containing the purchasing requisition number was a depend one to the table which contained the instances for which shall be searched. With dependent_table.purch_req = ‘’, the desired instances for which the dependent data does not exist were not returned. When explaining the issue and solution in my team, I felt that not every ABAPer is aware of that behavior.
Thus, I wrote a short report executing multiple selects in order to explain the different result sets.
Have look at the following screenshot picturing the content of the joined tables and the different SELECT-statements issued. Do you know all the results?
You can find a screenshot of the report including result and explanations separately at below. I did not want to spoil your curiosity presenting the results directly :wink:
Did you expect all the results? There was one, which surprised me. And to my even bigger surprise, it behaves differently on NW 7.31 and NW 7.40 – maybe I should open a message for that one…
You can find the report attached including the report source and alternatively SAPLINK-nugget containing the source tables if you’re too lazy to create the two tables yourself.
Outlook
There is one massive limitation with respect to performance on this approach of using LEFT OUTER JOINs. Therefore, in a subsequent post, I may introduce the advantage of subqueries (once I understood it myself to such an extent that I feel comfortable writing about it… )
Got feedback?
I’d love to read your comments on this topic and how you prepare yourself for the obviously upcoming re-incarnation of SQL – sorry: of the code pushdown.
- SAP Managed Tags:
2 Comments
