Multi-Value and Range-Value Attributes

Traditionally, an attribute can specify only one value. If an app needed an attribute to have multiple values, stored as comma-separated values it would need to add logic to parse the comma-separated values and display them properly. However, the core system would not be aware of the workaround convention and it could lead to issues when searching or indexing the data. To deal with these issues, MQL provides the following support for multi-value attributes:

• Multi-value attributes can be applied to any attribute type, including date, integer, real, Boolean, and string.
• Multi-value attributes can define an order. An order number is used to display the attribute values in a specific order.
• Dimension can be applied to the numeric fields.
• Each value can have its own unit of measure when Unit Of Measure (UOM) is applicable.
• Searching is performed against each value separately to determine a match.
• A multi-value attribute can have more than one instance of the same value.

You define an attribute to be multi-value by specifying its type as multivalue at the time of creation. You can also provide an order number when setting a multi-value attribute so that its value is positioned correctly.

To define an attribute as multi-value, use this command:

add attribute NAME multivalue;

To define an attribute as single-value, use one of these commands:

add attribute NAME notmultivalue;
add attribute NAME !multivalue;
add attribute NAME;

By default, attributes are single-value. You cannot specify default values for multi-value attributes. Import and export can handle multi-value attribute definitions and instances.

The lxString table indexes support multi-value attributes as follows:

• The index on lxType, lxVal, lxOid is only LxType, lxVal. In addition, the index order is lxType, lxVal on all databases.
• The unique index on lxOid, lxType includes the non-key column lxVal on SQL Server.

While multi-value attributes allow you to enter distinct values, range-value attributes allow you to enter maximum and minimum values (for example, to model a range of 100% - 20%). This capability is enabled for numeric fields and dates (for example, to model a start and end date).

MQL provides the following support for range-value attributes:

• Range-value attributes can be applied to numeric and date attributes.
• Dimension can also be applied to the numeric fields.
• The range can have only one UOM value.
• Searching is performed against both values.
• When setting values for a range-value attribute, you can specify either endpoint as inclusive (by default), exclusive (using the keywords minexclude and maxeclude), or open-ended (if no value is specified).

You define an attribute as having a range by specifying its type as rangevalue at the time of creation. Only numeric and date attribute types can be defined as range-value attributes. If you attempt to define a string or Boolean attribute type as rangevalue, an error will be thrown.

To define an attribute as specifying a range of values, use the command:

add attribute NAME rangevalue; 

To define an attribute as not specifying a range, use one of the commands:

add attribute NAME notrangevalue; 
add attribute NAME !rangevalue; 
add attribute NAME;

By default, attributes do not specify a range of values. You cannot specify default values for range-value attributes. Import and export can handle range-value attribute definitions and instances.

For more information, see MQL Command Reference: attribute Command.

To modify an existing attribute to become multi-value, use this command:

modify attribute NAME multivalue;

Only existing singlevalue attributes can be modified to become multivalue. If the attribute is already a rangevalue, an error will be thrown. If there are instances of a singlevalue attribute in the database, the modification moves all instances from the existing attribute tables to the new multi-/range-value attribute tables in the database.

If an attribute is already multivalue and has multiple values (instances), changing it to singlevalue is not allowed and will cause an error to be thrown. For example, you can use either of the following commands to change a multivalue attribute to singlevalue:

modify attribute NAME notmultivalue; 
modify attribute NAME !multivalue;

The above is valid if the attribute is already either multivalue or rangevalue and instances exist.

If the attribute is not already multivalue, you can use the following command. This syntax is permitted for a singlevalue attribute with no instances.

modify attribute NAME rangevalue;

The above is OK if the existing rangevalue attribute does not have any instances in the database. If the attribute is already specified as multivalue, an error will be thrown. The following table shows a summary of what is permitted and what is not:

If the existing rangevalue attribute does not have any instances in the database, you can use one of the following commands. If the attribute is already specified as multivalue, an error will be thrown.

modify attribute NAME notrangevalue; 
modify attribute NAME !rangevalue;

This table defines if an attribute conversions is allowed:

When a multi-value or range-value attribute has been added to a type or relationship, you can add business objects and connections with attribute values. You can provide a comma-separated list of values for a multi-value attribute using one of the following methods:

add bus T N R ATTR_NAME 1,2,3; 
mod bus T N R ATTR_NAME 3,4,5; 
mod bus T N R ATTR_NAME test1,test2,test3;

If the values themselves have spaces or commas, then they must be passed in single or double quotes using one of the following methods:

mod bus T N R ATTR_NAME 'foo bar1','foo bar2',foo bar3';
mod bus T N R ATTR_NAME "foo bar1","foo bar2","foo bar3";

Attempting to provide a comma-separated list of values for an attribute that is not multi-value will cause an error to be thrown.

For range-value attributes, the keywords minexclude and maxexclude indicate that the range endpoints do not include the minimum and maximum values, respectively. This is similar to interval notation in algebra.

Range-value attributes can have the following possible values:

If a business object needs the range-value attribute to be open-ended for the minimum or maximum values, then that value does not need to be provided.

The following table shows some examples of adding and modifying range-value attributes for a business object.

The following example uses a combination of single-value, multi-value, and range-value attributes with other business objects in a single MQL command:

mod bus T N R singleattr 2 multiattr test1,test2,test3
 rangeattr 1::5 policy p multiattr add test4;

The above command has the following effect:

1. The single-value attribute named singleattr is assigned the value 2.
2. The multi-value attribute named multiattr is assigned the values test1, test2, test3.
3. The range-value attribute named rangeattr is assigned the range 1::5.
4. The policy p is assigned to the business object.
5. A fourth value of test4 is added to the multi-value attribute named multiattr.

You can remove a value from a multi-value attribute, for example, as follows:

mod bus T N R ATTR_NAME remove 3;

The above command removes the value 3 from the multi-value attribute on the business object. If there are multiple rows with the same value, then all of those rows are removed.

A multi-value attribute can have more than one instance of the same value. The command add bus T N R intattr 1,2,3,1; will create four separate rows in the database table.

The following table shows some examples of adding/modifying/removing multi-value attributes for a business object. The same applies to attributes for relationships. In these examples, intattr is an integer, multi-valued attribute. The order values in the database are 1-based.

Printing a business object returns multiple values for a multi-value attribute, in ascending order. For example, if there exists a type T1 with an integer multi-value attribute M1 and there is one object of this type, printing this object produces the following results:

add bus T1 test 0 policy P1 M1 1,2,3; 
print bus T N R select attribute[M1].value; 
   attribute[M1].value = 1 
   attribute[M1].value = 2 
   attribute[M1].value = 3

There is no selectable that can retrieve all of the values of a multi-value attribute as a single string.

For range-value attributes, the existing value selectable returns two values, as shown below:

add bus T1 test 0 policy P1 M1 1::3; 
print bus T N R select attribute[M1].value; 
   attribute[M1].value = 1 
   attribute[M1].value = 3  

The minval and maxval selectables for business objects print the minimum and maximum values, respectively, of range-value attributes. For example:

Given a business object with a multi-value attribute M1 created with the command:

add bus T1 test 0 policy P1 M1 "1","2","3";

The following table lists the outputs produced by various queries of this business object:

Given a business object with a range-value attribute M1 created with the command:

add bus T1 test 0 policy P1 M1 1:3;

The results from greater-than '>' and less-than '<' operators for range-value attributes are interpreted such that the entire range for the attribute should be '>' or '<' the literal.

The following table lists the outputs produced by various queries of this business object:

Given a business object with a multi-value attribute M1 created with the commands:

add bus T1 test 0 policy P1 M1 1,2,3; 
add bus T1 test2 0 policy P1 M1 4,5,6; 
add bus T1 test3 0 policy P1 M1 7, 2,3;

The following table lists the outputs produced by various queries of this business object:

Given a business object with a multi-value attribute M1 created with the commands:

add bus T1 test 0 policy P1 M1str val1,val2,val3;
add bus T1 test2 0 policy P1 M1 val4,val5,val6;  
add bus T1 test3 0 policy P1 M1str Val1,val2,val3;

The following table lists the outputs produced by various queries of this business object:

In the case of multi-value attributes, using ".value" in the Where clause (for example, where attribute[M1].value == 'val1' ) is less efficient than without (for example, where attribute[M1] == 'val1' ). This is because the Where clause condition is resolved by a precise select statement, and when ".value" is not used, evaluation is done in memory, which is more intensive. It is, therefore, advisable to construct Where clauses without ".value".

Expressions with multi-value selectables in the Where clause maintain the data type of the underlying data. In cases where there are multiple expressions involving the same attribute (for example, where [(attribute[M1] == val1) && (attribute[M1] == valN) .. ], only the first expression (attribute[M1] == val1) is SQL'ized, and the rest are resolved in memory.

In Where Used queries, it is prohibited to use multi-value or range-value attributes. The Orderby clause is also not allowed if multi-value or range-value attributes are used in a query.

In the case of range-value attributes, some operators (mainly the ones dealing with strings and patterns such as match, nmatch, smatch, and so on) do not produce accurate results. They treat the value [|(minval::maxval)|] as a string and make a comparison against it.

Dimensions can be added for multi-value attributes, as is currently possible in the system. For more information, see Dimensions). Only one dimension is allowed per attribute type, whether multi- or single-value. However, for multi-value attributes, every single value can have its own UOM when a dimension is applied to the attribute type (i.e., each attribute value can have its own UOM value). There is a one-to-one correspondence between the entries in the attribute table and in the unit table. For example:

add dimension d; 
mod dimension d add unit km label kilometers unitdescription  kilometer_desc multiplier 1000
 kilometer_desc multiplier 1000 offset 0 setting METRIC true;; add attribute M1 type integer
add attribute M1 type integer dimension d multival true; 
add attribute R1 type integer dimension d rangeval true; 
add type T1 attribute M1,R1; 
add bus T1 N1 0 M1 "100km","200km","300km" R1 "10 km::100 km";

Since the attribute M1 has three values, the above add bus statement should see three separate entries for the UOM values in the unit table and three corresponding values in the attribute table in the database. The entries in the two tables are tied together with the order number.

The following attribute selectables return multiple values, all of which are output based on the order number:

attribute.inputvalue;
attribute.inputunit;
attribute.dbvalue;
attribute.dbunit;
attribute.unitvalue;
attribute.generic;

For range-value attributes, there is just one UOM value for the entire range in the unit table.

The history entry for a multi-value attribute is delimited by a comma and truncated to 255 characters maximum length. If a single attribute setting sets the attribute to multiple values, which concatenates the values delimited by ',' and generates a string that is longer than 255 characters, only the first 255 characters of the attribute value are stored in the history tables in the database.

The history entry for the attribute modifications also includes the existing value of the attribute, and the same 255 character limitation applies to the existing attribute values.

For a multi-value attribute, an example of a history entry would be:

history = modify - user: creator time: Fri Jul 20, 2012 11:00:54 AM 
   EDT state: s1 attr: 1,2,3,[..up to 255 chars] was 20,30,40[..up to 
   255 chars]

For a range-value attribute, the range is specified. An example of a history entry would be:

history = modify - user: creator time: Fri Jul 20, 2012 11:00:54 AM 
   EDT state: s1 attr: 20::30 was 10::20

The ATTRTYPEKIND macro specifies whether an attribute is single-value, multi-value, or range-value, respectively, by having one of these values:

• Single
• Multi
• Range

You can use the NEWATTRVALUE macro to enter new multi-value and range-value attribute values delimited by "^G" (the beep character). The trigger would then have to parse the string to get the multiple values for such attributes.

You can use the ATTRVALUE macro to enter current multi-value and range-value attribute values, again delimited by "^G" (the beep character). The trigger would then have to parse the string to get the multiple values for such attributes.

Unique keys and indexes are not supported for multi-value or range-value attributes. This means that:

• If a multi-value or range-value attribute is used as a field while defining an index or unique key, an error will be thrown.
• If there is an existing unique key or index that has a single-value attribute and you try to modify that attribute to become a multi-value or range-value attribute, an error will be thrown.

Adaplets do not support multi-value or range-value attributes. This means that:

• If you modify an existing attribute to become a multi-value or range-value attribute, the first time that the attribute vault is loaded after the change, an error will be thrown.
• If any new attributes are added that are multi-value or range-value attributes and they are used in an adaplet mapping file, again an error will be thrown.