Siebel Uses join like subqueries for 1:M relations in a buscomp that have the “Use Primary Join” set, combining this with the check no match (set True), allows Siebel to set the special value “No Match Row Id” value in place of a valid row_id if the child does not contain any valid data.
I needed to fix something in a Siebel application (on oracle database), where we search for the records that do not have a mvl record defined, and needed more background on this subject (I need it as a shortcut to find records that do not have a mvl child, this is a lot cheaper than going through the very expensive NOT EXISTS(IS NOT NULL) approach of PDQ.
So I found this quite exellent article by Prakash Akuthota, that only has two flaws:
“The NoMatchRowId generating and testing behavior is activated by setting Check No Match to FALSE for the MVL.”, it is in fact inverse. The value generated is also actually “No Match Row Id” with spaces.
In the GUI you can actually search for the “No Match Row Id” Value, this makes my shortcut work as intended. Using this approach is all good an fine, you just have to know that inserts/deletes not through the mvl relation you are looking at eg. Contact > Address as opposed to Account > Address wont set the No Match Row Id in the other MVL Relations.. But there are workarounds for that too.
Regards,
Henrik Ohm