Dev Tip: New Ways to Select Documents with Status Objects

In case you’ve missed our dev tips in past weeks, we will be posting them in the blog moving forward. To sign up and get your dev tips delivered to your inbox each week, click here!

Tip of the Week

New Ways to Select Documents with Status Objects

When SAP introduced Open SQL into ABAP back in 2014, I knew it would be a game-changer.  I stumbled around using some of the features such as inline declarations and SQL expressions, but I recently came across a new use – the IS NULL comparison operator.

Here’s the use case – something all ABAPers who have ever written a report has come across.  We need to grab all open documents of a type that’s status is stored in table JEST.  It is easy enough to get data from both tables, but the issue is if a document status has never been set, it does not show up in table JEST.

For this example, I will use quality notifications.  My goal is to grab a list of all open quality notifications.  The status “NOCO – Notification completed” means the notification is closed.  The status I0072 is the corresponding value for NOCO.

We can see that QN 200000343 is closed.  Easy enough.
The corresponding entry is JEST –
Now, take the case of QN 200000644.  It is still open.
However, there is no entry for I0072 (NOCO) in JEST.
How is this possible?  SAP only creates entries in JEST once a status is set.  Therefore, since we have never set NOCO on QN 200000644, the entry will not be there.Why is this important?  It complicates how we get the information for our original goal, a list of all open quality notifications.

In the past, there were a couple ways to get this data.  We could SELECT all quality notifications, then SELECT the entries in JEST for those QNs, and finally loop through the results to build our output.  It would look a little something like this.
The second way is similar, but uses a LEFT JOIN along with a DELETE.
The second way is cleaner, but still requires a DELETE statement.  These DELETE statements can be performance hogs when the internal table is large.Enter the new way!  With ABAP Open SQL, we can now use the IS NULL operator.  By using IS NULL, we exclude records from our results that have status I0072 set.  No further processing is necessary!
Just to prove that the results are the same, here are the results from the above code snippets.
As for performance concerns, the first option is by far the worst.  With my small dataset of ~2000 overall records, there was a minor performance boost, around 5%, with option 3 over 2.  The difference would be much greater with a larger data set.Have you found a new way to solve an old problem using the features of ABAP 740?  I’d love to hear about it!

 

If you have an interest in viewing similar content, visit our blog, here. 

View our LinkedIn, here. 

Mike Berg is a Senior SAP Developer at Mindset. He leverages his 18+ years of experience across various SAP technologies and functional areas to bring optimal solutions for customers to light. Mike focuses on bringing the best user experience possible to users, which in turn maximizes ROI for the organization. He does this by emphasizing the correct technology, be it Fiori, Personas, or other, and by optimizing application performance and ease-of-use. Mike is a regular contributor to Mindset’s blog and development tips newsletter, as well as a speaker at SAP Sapphire and ASUG events.

TwitterLinkedIn
Back To Top