Join vs For all entries - Error handling / records not found

[JaySchwendemann]

Dear all,

being a good citizen and the like I know that one should go towards using joins or even code pushdown instead of FAE.

However, the latter seems way more suitable to do a more user friendly error handling.

Suppose I have the following join (simplified for sake of, well simplifying)

SELECT q~qmnum
	FROM qmel AS q
	INNER JOIN ihpa AS i
		ON q~objnr = i~objnr
	WHERE q~qmnum IN @it_qmnum_range
	AND   i~parvw =  'Y4'
	INTO TABLE @DATA(lt_notification).

So I essentially want to get all the service notifications, where a specific partner type (technician = Y4) has been assigned a task.

When doing this with above join and something fails, I don't have a clue if it failed because the notification number was not in the range "it_qmnum_range" or if there was no entry found in "ihpa" for "Y4".

If I would go for FAE, I could deal with, identify exactly where things went wrong and give the user back a meaningful error.

How to overcome such issues while still opting for joins / code pushdown.

Cheers

Jens

[DoanManhQuynh]

you may try SELECT...ENDSELECT and process the check inside select loop.

[Sandra_Rossi]

The code should remain like that productively (with a join), there's usually no interest to explain what is the exact reason of a failure. I think it's more useful for developers (or testers having a technical knowledge) to analyze why a SQL failed. Maybe a tool. Isn't there one provided with database systems?

[lbreddemann · Accepted solution]

An empty result set is not a failed join.
The join operation worked correctly (and hopefully fast) to determine that the no combination of records matched the provided conditions.

Now, you want to get more information about why this was the case.

You could check for every single filter condition in your query whether - taken separately - would yield a non-empty result. Does that help, though? No, since the reason for filtering out a specific record might very well be in the combination of all filters (or just some of the filters).

At that point, to provide concise feedback on why a certain record hasn't been included in the result set you would have to work through all combinations of filters which can become a complex exercise for your "error handling" code.

So much on the "general case".

For your example, what you would do (that's what I assume) is that once you found that the result set is empty, your code would check if IPHA has any entries for which PARVW = 'Y4' and if QMEL returns any records for the QMNUM_RANGE. You can do the exact same thing with SQL, by running two additional statements.

Beyond this, I have to admit, that I've not seen a requirement for a "why-not-found" before and the example of fetching service notification doesn't occur to me as one, where the end user would want/need an explanation of why there are no notifications.

Besides the "it could be nice"-point: in what scenario do you provide users with that kind of feedback?

[JaySchwendemann · Accepted solution]

Lars,

thanks a ton for the elaborated answer.

"[...] you would have to work through all combinations of filters which can become a complex exercise [...]"

That is exactly the point I struggled / made me start thinking.

"in what scenario do you provide users with that kind of feedback"

This is actually a program for transmitting certain service notifications from our ERP system via PI to an external partner. Only some service notification should be transmitted. The program selects the eligible service notifications with that join (obviously the real join is considerably deeper nested with about 5 nested joins).

Now there might be "false positives", where the LOB people working with the service notification did not fill in every field necessary to make the service notification eligible for transfer. I give you that this ideally should then be handled at that very point in time, via input validation. The LOB currently however is reluctant to accept a very strict lock down of the process of creating / editing service notifications because they want as few interference with validation so they "could get their job done".

On the other hand, the external partner, at some time does indeed know about the service notification by other means of communication (fax, email, phone) even if he hasn't received a data transfer yet. Or the end customer, for which the service notification got opened, will call and complain why there still was no technician.

So at that point (or precautionary) some (power) user will want to look into this and ideally will search for the notification number and will get detailed error information (which I am currently unable to provide)

Again, I totally give you that this whole jumble should ideally be handled beforehand at save time of the service notification and I will do so for most of the possible issues. However, I still cannot help the feeling that with the FAE version, I would have things more firmly under control.

I think we would just need to see, how often such unexpected "non finds" will show up in real world and work from there.

Cheers

Jens

[lbreddemann · Accepted solution]

Thanks for the further details of the usage scenario.

I can see how it could make sense in that setup to be able to "dig into" the data and look at records with "relaxed" join/filter conditions.

Personally, I'd try and tackle that in two steps.
One would be the standard inner join with all filters etc. - the good case scenario. With the assumption that this makes the majority of all cases this would be a straight forward and easy to understand implementation that also takes benefit from "code push-down" to HANA.

The second step could be an outer join as hinted by Matthew and Rafael. Since the "relaxation" should not only occur on the join condition but possibly also on the filters, it makes sense to keep this separate and e.g. have filters with patterns or ranges.
Having this second piece of implementation would make it clear that there is a separate use case ("looking for the incompletely documented notifications") and would keep the "good path" a lot (c)leaner.

As written, that's my thinking around this.

[JaySchwendemann · Accepted solution]

Shame that one cannot make a comment an answer and accept that. Overall I think your approach would be the most sensible. Of course thanks matthew.billingham and rcfigueiredo for providing input for that general direction I'll be heading to.

Thanks again, will implement this and see how things turn out once testing is done / we are productive. Will try remembering updating this thread

Cheers

Jens

[matt]

Use a RIGHT OUTER JOIN?

[former_member562009]

Hi Jens,

If you select at least one record of each table, you may use a LEFT JOIN to return all existing records of QMEL and if there are no corresponding records in IHPA, the fields of this table will return null (initial) values. Then you can check later if there are no records at all or if there are some from QMEL with no matching records in IHPA.

SELECT q~qmnum i~parvw
  INTO TABLE @DATA(lt_notification).
  FROM qmel AS q
  LEFT JOIN ihpa AS i ON q~objnr = i~objnr
 WHERE q~qmnum IN @it_qmnum_range
   AND i~parvw ='Y4'.