In the previous part of this learning series we uncovered the basics behind self-referential relationships. We discussed how to avoid getting false results and how using an alias allows the rule designer (and the engine) to understand more clearly the kind of comparison that is common in self-referential relationships. After all, if you want to refer to two instances of the same entity, it is probably because they are subject to some sort of comparison.

So now we can use the knowledge from the previous chapter in our supermarket scenario. Recall from previous articles in this series, that we are interested in a series of promotional messages that will accompany our customer receipt. The project looks like this so far.

In the previous articles we have inferred various messages using a Word inference table for example. Now we will implement a new self-referential relationship to look at the messages we have generated, and then concatenate them all into a single global string. This is a very common requirement – perhaps you have seen already a project with such a requirement (“take all the rows in entity X and iterate over them and extract information to be placed in a Global attribute). Unlike InstanceSum() and so on, here we want to actually extract something (text, numbers, whatever) and transform the data into a single attribute.

We want to take those promotional messages and create something like a signoff. We would like the end of the receipt to say something this, and we will use Self-Referential Relationships to do it. Imagine if we just wanted to have a string at the end of the receipt “Thank you for shopping with us today : here is your coupon for next time : 20 % off Cat Food on Thursdays”.

Our scenario is going to require two reference relationships to be added to the project. Both will be self-referential. The first will look like this . As the text suggests, this will be used identifying the next messages in the series (so if there are three messages to read, and we were looking at message one, then this would help us identify messages two and three).

And the second one:

You can probably guess that this one will look at the closest one in the set, so if you are looking at message one, this will help us identify message two. You might be surprised that the relationship is many to many. In this case it is Oracle Intelligent Advisor which forces us to do this, otherwise we will get an error message.

Now we are going to modify our table of messages to introduce an identifier (notice we have added another message just for fun):

This counter is essential as we are going to use it to step through the instances. In real life this counter might have to created from some other information (perhaps from a coded string, or a Row ID, or a date and time) but we need something of this nature to be able to loop. Of course in other examples you might do all of this in an Excel Worksheet.

So we might leverage the first relationship like this, drawing on the kind of rules we’ve seen in earlier episodes:

Notice the simple idea – if a message has a higher id than the current one, then it gets assigned to the next promotional messages. As seen last time out, an alias makes sure we don’t keep comparing ourselves to ourselves.

Here is the project in the debugger, with the three messages triggered by our shopping:

Notice that we have selected the message id 1 in the debugger, and in the inferable relationships we can see that the next promotional messages shows the next two messages.

The second of our relationships can use something similar to identify only the closest message in the list. Get ready for this as it can be a bit of a mouthful. Please note to the purists out there – we have deliberately constructed this to be a demonstration of something you might come across, so it is perhaps over-written.

What in the name of????? Those are some serious crazy Self-Referential Relationships there. Let’s see what this actually does

The initial conclusion is just the same sort as the previous example (a promotional message will be a member of a Self-Referential Relationship if it meets the criteria, and our criteria will need to use an alias). We are once again looking at the promotional messages with a higher Id. So if ID2 is greater than ID1, which it is, it passes the first condition.

For the second part of the rule, the ForAllScope() tells us that everything that follows is relating to the next promotional messages and that the conditions must be true for all of the members to pass the criteria.

Is the other promotional message eg ID2 <= members of “the next promotional messages”

But here there is a problem. We cannot just say the other promotional message id <= the promotional message id

Because if we do, we are falling into the same trap as earlier – you cannot compare two instances of the same entity without using an alias. We want to compare with only the instances in the next promotional messages. So we need to adopt a second alias (the further promotional message) for this part. You have to use another word (not “other”) so that it distinguishes the two aliases.

We are asking to find the lowest ID in the relationship. So for ID1, which has ID2, ID3 and ID4 in the relationship, ID2 will pass. So ID 2 passes both criteria. It is greater than ID1, but less than the other members (ID3 and ID4). If you look at ID2 now, ID3 and ID4 pass the first criteria, but ID3 is lower than ID4. And so on. So in the debugger session above you can see that ID2 is selected, ID3 is the closest.

So now that we have done all that what next? We are going to transform the relationships we have created and use them to build a string. Specifically, we will use the closest promotion message to concatenate each message with the next one, and keep doing that for all of them:

The above rules just calculate the number of instances in each relationship for each of the messages. We will use them in the next table.

This table will create a loop warning, because that is exactly what it is supposed to do. It will loop over each promotional message, checking if it has any “next promotional messages” or “closest promotional message”. Based on that it will decide or not to include a comma in the final string. For each promotional message it says something like this (in simplified form).

The promotional messages’ final text will be a concatenation of the promotional message with the value of the closest promotional messages’ final text. So we are deliberately creating a loop over all the messages, and concatenating the other messages.

So for ID1, the final text would be “20% off Cat Food on Thursdays, Buy 1 Get 1 Free on Beans on Mondays, Freezin’ Pleasin’ 50 cents off Peas, Tots N Teens N In-betweens 2 for 1.”

For ID2 the final text would be “Buy 1 Get 1 Free on Beans on Mondays, Freezin’ Pleasin’ 50 cents off Peas, Tots N Teens N In-betweens 2 for 1.”

For ID3 the final text would be ” Freezin’ Pleasin’ 50 cents off Peas, Tots N Teens N In-betweens 2 for 1.”

For ID4 the final text would be “Tots N Teens N In-betweens 2 for 1.”

Lastly we ask which was the longest one of those concatenations in the loop:

The longest of those strings after the loop must represent the complete set of concatenated messages. So we take that one and add some text around it to look nice.

So in our receipt below, only 4 lines had personalized offers attached, and the output is shown below the receipt.

There certainly have been a fair few Self-Referential Relationships in this article. It combines cross-entity reasoning, aliases, and a loop -plus a bit more – mostly just to illustrate a point it must be said. But it is an interesting set of circumstances and rules. There are lots of great examples in the Complex Entity Logic section of the online help.

Didn’t catch the others in this series? They can be found starting here. Then they go here. Then here. And then here. And there. If you want a copy of the Zip just drop a comment.