"new built-in operator SEARCH(t TUPLE{*}, regex CHAR) RETURNS BOOLEAN, which returns

true if any CAST_AS_CHAR(a), where a is an attribute of t, matches regex."

Your first inclination may be to assume that a is referring to scalar attribute(s).

But what if relation t has tuples that include not only scalar value(s) but rva(s)

(relational valued attributes). What will SEARCH do when it encounters an rva?

To see what Rel does in this case execute the follow statement to create relvar TESTSEARCH

which has several rva's as well as a nested relation. It contains 4 tuples. The key is

attribute KEY# integer. KEY# ranges from 1 to 4. Please note that type Decimal is

required. If your database doesn't have this type you can load it from:

RelScripts\SampleJavaBasedTypes\TypeDecimal.rel.

Code: Select all

```
VAR TESTSEARCH BASE INIT(
RELATION {KEY# INTEGER, ID CHARACTER, R1 RELATION {M CHARACTER}, R2 RELATION {C CHARACTER, R3 RELATION {N INTEGER, D CHARACTER, G Decimal}}} {
TUPLE {KEY# 1, ID "10", R1 RELATION {M CHARACTER} {
TUPLE {M "H068"},
TUPLE {M "H0273"},
TUPLE {M "H0201"},
TUPLE {M "H0118"}
}, R2 RELATION {C CHARACTER, R3 RELATION {N INTEGER, D CHARACTER, G Decimal}} {
TUPLE {C "5Q72", R3 RELATION {N INTEGER, D CHARACTER, G Decimal} {
TUPLE {N 10, D "KP0105.622", G Decimal(41.688, 3)},
TUPLE {N 11, D "KP040.772", G Decimal(37.898, 3)},
TUPLE {N 12, D "KP0312.842", G Decimal(34.74, 3)},
TUPLE {N 13, D "KP045.54", G Decimal(32.067, 3)},
TUPLE {N 14, D "KP084.479", G Decimal(29.777, 3)},
TUPLE {N 15, D "KP042.699", G Decimal(27.792, 3)}
}}
}},
TUPLE {KEY# 2, ID "14", R1 RELATION {M CHARACTER} {
TUPLE {M "H0119"},
TUPLE {M "H0152"},
TUPLE {M "H0499"}
}, R2 RELATION {C CHARACTER, R3 RELATION {N INTEGER, D CHARACTER, G Decimal}} {
TUPLE {C "5Q51", R3 RELATION {N INTEGER, D CHARACTER, G Decimal} {
TUPLE {N 14, D "KP025.674", G Decimal(21.152, 3)},
TUPLE {N 15, D "KP048.133", G Decimal(19.742, 3)},
TUPLE {N 16, D "KP038.743", G Decimal(18.508, 3)},
TUPLE {N 17, D "KP019.173", G Decimal(17.419, 3)},
TUPLE {N 18, D "KP094.024", G Decimal(16.451, 3)},
TUPLE {N 19, D "KP045.342", G Decimal(15.586, 3)}
}}
}},
TUPLE {KEY# 3, ID "16", R1 RELATION {M CHARACTER} {
TUPLE {M "H035"},
TUPLE {M "H0418"},
TUPLE {M "H0554"}
}, R2 RELATION {C CHARACTER, R3 RELATION {N INTEGER, D CHARACTER, G Decimal}} {
TUPLE {C "4Q70", R3 RELATION {N INTEGER, D CHARACTER, G Decimal} {
TUPLE {N 16, D "KP0194.876", G Decimal(20.93, 3)},
TUPLE {N 17, D "KP046.131", G Decimal(19.699, 3)},
TUPLE {N 18, D "KP032.775", G Decimal(18.604, 3)},
TUPLE {N 19, D "KP062.651", G Decimal(17.625, 3)},
TUPLE {N 20, D "KP0215.05", G Decimal(16.744, 3)}
}}
}},
TUPLE {KEY# 4, ID "18", R1 RELATION {M CHARACTER} {
TUPLE {M "H057"},
TUPLE {M "H0351"},
TUPLE {M "H0681"},
TUPLE {M "H01033"},
TUPLE {M "H01075"}
}, R2 RELATION {C CHARACTER, R3 RELATION {N INTEGER, D CHARACTER, G Decimal}} {
TUPLE {C "9Q75", R3 RELATION {N INTEGER, D CHARACTER, G Decimal} {
TUPLE {N 18, D "KP0894.048", G Decimal(40.896, 3)},
TUPLE {N 19, D "KP055.853", G Decimal(38.743, 3)},
TUPLE {N 20, D "KP053.574", G Decimal(36.806, 3)},
TUPLE {N 21, D "KP095.333", G Decimal(35.054, 3)},
TUPLE {N 22, D "KP0206.823", G Decimal(33.46, 3)},
TUPLE {N 23, D "KP057.037", G Decimal(32.005, 3)},
TUPLE {N 24, D "KP0120.177", G Decimal(30.672, 3)},
TUPLE {N 25, D "KP030.5", G Decimal(29.445, 3)},
TUPLE {N 26, D "KP069.576", G Decimal(28.313, 3)},
TUPLE {N 27, D "KP036.981", G Decimal(27.264, 3)}
}}
}}
}
)KEY{KEY#} ;
```

attribute types and values. Now execute the following expression which adds attribute FOUND.

FOUND is type boolean. If SEARCH is working correctly then if FOUND is true SEARCH found

the string ("699") somewhere in the tuple. If FOUND is false the string wasn't found.

Code: Select all

```
TESTSEARCH EXTEND {FOUND:=SEARCH(TUP{*},".*699.*")}
```

KEY# 3 (see nested rva R3 attr. G Decimal(19.699,3)).

Next try the expression:

Code: Select all

```
TESTSEARCH EXTEND {FOUND:=SEARCH(TUP{*},".*72.*")}
```

KEY# 4 (nested rva R3 ATTR. G Decimal(30.672,3)).

You can, of course, use WHERE to restrict the returned tuples to those where SEARCH is true:

Code: Select all

```
TESTSEARCH WHERE SEARCH(TUP{*}, ".*72.*")
```

EXTEND for restricting tuples instead of using WHERE (but that's another post and I'll use WHERE here when restricting tuples).

If you want further evidence use these update example expressions to insert your own values

in relvar TESTSEARCH and use SEARCH with them. Note if you want to make the update(s) permanent

change the update expression to an update statement by adding a semicolon(;) at the end.

Use this update to change attribute M in relation R1 to another character value.

Code: Select all

```
UPDATE TESTSEARCH WHERE KEY#=2:{UPDATE R1 WHERE M="H0152" :{M:="AB015"}}
```

Code: Select all

```
UPDATE TESTSEARCH WHERE KEY#=4:{UPDATE R2:{C:="999Q55"}}
```

Code: Select all

```
UPDATE TESTSEARCH WHERE KEY#=2:{UPDATE R2:{UPDATE R3 WHERE N=15:{G:=Decimal(15.999,3)}}}
```

system types and user-defined types, but relational valued attributes as well. It essentially

will search everything no matter what it is and no matter where or how it's organized.

Note that using TUP{*} brings every scalar and every rva into the SEARCH scope.

Using an individual rva with SEARCH.

You can use the form: relvar WHERE SEARCH(TUPLE{arbitrary attribute name, rva}, regex CHAR)

For example SEARCH using rva R2. TUPLE attribute Y uses relation R2 which has attributes C and

nested rva R3.

Code: Select all

```
TESTSEARCH WHERE SEARCH(TUP{Y R2}, ".*KP036.981.*")
```

Code: Select all

```
TESTSEARCH WHERE SEARCH(TUP{Y R2{R3}}, ".*KP036.981.*")
```

If you try to SEARCH just rva R3:

Code: Select all

```
TESTSEARCH WHERE SEARCH(TUP{Y R3}, ".*KP036.981.*")
```

This is because the only way to get rva R3 in scope is thru rva R2 because it's a nested (in rva r2) rva.

It seems to me the scoping of rva's in SEARCH follow the same logic as rva scoping using EXTEND.

To duplicate the elegance of: TESTSEARCH WHERE SEARCH(TUP{Y R2{R3}}, ".*KP036.981.*")

using just rva R3 by itself in SEARCH requires multiple EXTEND operations in order to create an rva R3

like relation where SEARCH is applied to it. Notice that in this case the SEARCH restriction is not

applied to relvar TESTSEARCH but only to rva NEW.R3 and it takes another WHERE statement to restrict

relvar TESTSEARCH.

Code: Select all

```
((TESTSEARCH EXTEND {NEW.R2:=(R2 EXTEND {NEW.R3:=R3 WHERE SEARCH(TUP{*}, ".*KP036.981.*") }){NEW.R3}} UNGROUP NEW.R2) WHERE NOT IS_EMPTY(NEW.R3)){ALL BUT NEW.R3}
```

you own value for the SEARCH regex. (Note you could use the WITH definitions as input to the Rev visual query editor).

Code: Select all

```
WITH(T1:=TESTSEARCH EXTEND {NEW.R2:=R2 EXTEND {NEW.R3:=R3 WHERE SEARCH(TUP{*}, ".*KP036.981.*")}},
T2:=T1 UNGROUP NEW.R2,
T3:=T2 {ALL BUT C, R3}, //Attributes C and rva R3 in rva NEW.R2 are in a different scope from C and R3 in rva R2 so there's no conflict.
T4:=T3 WHERE NOT IS_EMPTY(NEW.R3) //Restrict relvar TESTSEARCH based on SEARCH applied to rva R3 by itself (rva NEW.R3).
):T4{ATTRIBUTES_OF(TESTSEARCH)} /* Substitute expression T1, T2 OR T3 for T4{ATTRIBUTES_OF(TESTSEARCH)} to see the query at that particular point. */
```

Operators like SEARCH is why its worth the investment to learn a relational dbms.

Especially a relational dbms like Rel.

P.S. I didn't write a query or create a relational literal by inputting data for relvar TESTSEARCH.

It was created/populated using an operator I named RAD. R(el)A(utomated)D(ata). It can generate

pretty much any kind of data structured in any way you can imagine. I struggle with documenting stuff

but if I ever get through documenting it I will post operator RAD