Where Clause

Syntax

While the Where clause can test for equality, it can test for many other characteristics as well. The syntax for the Where clause details the different ways that you can specify the search criteria, using familiar forms of expression:

where "QUERY_EXPR"

where 'QUERY_EXPR'

• QUERY_EXPR is the search criteria to be used.

In general, the syntax for a query expression is in one of the following forms:

Each form is described further in the sections that follow. For more information, see Guidelines for Defining Queries.

Query Expression (QUERY_EXPR)

This form simply means that a query expression can be contained within parentheses. Although not required, parentheses can add to readability and are useful when writing complex expressions. Parentheses can be used to group subclauses of the where clause, but not as start/end delimiters.

Comparative Expressions

This form of a Boolean expression considers comparisons such as greater than, less than, and equality. You have two arithmetic expressions and a relational operator:

• ARITHM_EXPR is an arithmetic expression that yields a single value that could be numeric, a character string, a date, or a Boolean. For more information, see Arithmetic Expressions (ARITHM_EXPR).
• RELATIONAL_OP is a relational operator. For more information, see Relational Operators (RELATIONAL_OP).

When writing comparative expressions, you can have any mixture of arithmetic expressions. Since all arithmetic expressions yield a single value, they are interchangeable as long as they are of the same type. You cannot mix different value types in the same comparison expression. If you try to mix types, an error message will result.

You cannot compare values of different types because some operators do not work with some values. For example, testing for an uppercase or lowercase match does not make sense if you are working with numeric values. Even when you have values of the same type, you must be sure to use a relational operator that is appropriate for the values being compared. If the operator is incorrect for the values being compared, an error message will result.

Arithmetic Expressions (ARITHM_EXPR)

Use the following syntax:

• ARITHM_EXPR can be a selectable field name that yields a numeric value, an arithmetic operand (value), or another arithmetic expression. While arithmetic expressions include all data types, arithmetic expressions apply only to Integer or Real value types.
• BINARY_ARITHMETIC_OP is one of four arithmetic operators:
  • Plus sign (+) for addition
  • Minus sign (-) for subtraction
  • Asterisk (*) for multiplication
  • Slash (/) for division

Arithmetic expressions can be written three ways:

These forms allow you to write comparative expressions such as:

In the last comparative expression, all three forms of an arithmetic expression are used. This expression compares the results of two arithmetic expressions. One value in each arithmetic expression is a field name (Parts In Stock or Parts Needed) and one is a value supplied by you (10 or 5). Before the comparison can take place, each arithmetic expression must be evaluated and reduced to a single value. Then the two values can be compared.

Include a space on either side of each arithmetic operator (+, -, *, /) to correctly separate it from the operands.

Relational Operators (RELATIONAL_OP)

Relational operators can be used to compare values of all data types unless specified otherwise.

== 
eq 
EQ 

!= 
neq 
NEQ 

< 
lt 
LT 

> 
gt 
GT 

<= 
le 
LE 

>= 
ge 
GE 

~~ 
smatch 
SMATCH 

!~~ 
nsmatch 
NSMATCH 

~= 
match 
MATCH 

!~= 
nmatch 
NMATCH 

To find objects where a particular property is non-blank, use a double asterisk. For example, if you want be sure that all objects in the result of your query contain a description, use this where clause:

There is a difference between where clause terms that can find an empty value in the database (empty terms) and those that find no data in the database (NULL terms). A query with a where clause of the form 'selectable == ""' returns objects where that term is an empty term, but not if that term is NULL. Examples:

• where 'attribute[str] == ""' only returns objects that have the attribute (either from their type definition or an applied interface) AND the value is empty.
• where 'from[RELTYPE].attribute[str] == ""' only returns objects that have connections of that RELTYPE AND at least one of the connections has the attribute AND the attribute is empty.
• where 'project.property[PROPNAME].value == ""' only returns objects that have a project owner AND that project has a property PROPNAME assigned AND the value of the property is empty.

Matchlist Expressions

Expressions can use two keywords, matchlist and smatchlist, which enable you to specify a list of strings on the right-side of these keywords. These work the same as the match and smatch keywords except that an additional operand is used as a separator character for the list of strings. The format is:

• STRING_LIST is the list of strings to be compared
• SEPARATOR_CHAR defines the character that separates the list of strings. If no separator character is given, the first right-hand operand is treated exactly as match and smatch treat it, that is, as one string.

Following are some examples:

temp query bus Errata * * where "current matchlist 'Open,Test' ','";

temp query bus Errata * * where "attribute[Priority] matchlist '0,1,2' ','";

temp query bus Errata * * where "attribute[Notes] smatchlist '*Yin*,*Williams*'','";

The last query will find all Errata in which the names “Yin” or “Williams” are included in the Notes section, and the search will be case-insensitive. In the above examples, a comma is specified as the separator character in the second right-hand operand. The first right-hand operand is treated as a list of strings delimited by this separator character.

In matchlist and smatchlist expressions, you cannot use these characters as a separator character: * . \ (asterisk, period, or backslash)

If either left or right operand is a Select clause, the result of evaluating the operand is a list of strings. In all other cases, the evaluation of the operand just gives a single string. match, smatch, matchlist, and smatchlist are evaluated by a pair-wise comparison of the two lists. If any comparison yields true, then the result is true. Otherwise, it is false.

Searching Based on Lengthy String Fields

The Oracle database stores most string attribute values in the lxStringTable for the object’s vault. However, lxStringTable cannot hold more than 254 bytes of data. When a string attribute’s value is larger than this limit, the data is stored in the descriptions table (lxDescriptionTable), and a pointer to this table is placed in the lxStringTable.

When performing an “includes” search (using match operators: match, match case, not match, not match case) on string attribute values, the 3DSpace searches on both lxDescription and lxString tables only when the attribute involved is of type “multiline.” Also, if you use the equal operators (==, !=) and give a string of more than 254 bytes to be equal to, 3DSpace checks the values in the lxDescriptionTable only.

To search on description or other string attribute values for given text, and to force the search of both tables, you can use the “long” match operators. These operators can be used in any expression (including the Where clause entry screen).

Alternatively, you can include the .value syntax in the Where clause, as shown below:

attribute[LongString].value ~~ “matchstring”

Boolean Expressions: BOOLEAN_EXPR

This form of query expression means that the query expression can be either a single arithmetic expression or a selectable business object property that yields an arithmetic expression. For example, assume you want to find a list of honor students. The criteria for honor roll might be described as:

When this clause is processed, 3DSpace looks for all objects that have this attribute and includes the value of “Grade Point Average.” If the attribute is not found within the object definition or if the attribute value returns false when the Where clause is evaluated, the object is excluded from the query output. To include a selectable object property in a Where clause, you must use its proper name and notation.

You can obtain this by using the Print Businessobject Selectable command. For more information, see Select Clause. This command prints a list of all field names that can be used and indicates how they must be written.

A Boolean expression contains one or more comparisons. These comparisons return a value of TRUE, FALSE or UNKNOWN. For example, UNKNOWN might be returned as a string by a string attribute. It could also be returned by a Program, since Programs can be invoked in Expressions and return a string.

Values for Boolean expressions can be True, False, or Unknown, and should not be confused with Boolean type attributes, whose values can be only True or False. Mixed case (Unknown) and lowercase (unknown) are allowed.

The syntax for a Boolean expression uses two basic forms:

When using Boolean operands in expressions, you must specify ==TRUE or ==FALSE in the expression. The operand by itself will always evaluate to TRUE when used in conjunction with other SQL convertible query fields.

The first form of the Boolean expression assumes you have two Boolean values whose relationship you want to compare. While there are only three Boolean operators, there are multiple ways that these operators can be specified as shown in the Boolean Relationship table below.

When specifying a Boolean value in this form, follow the same syntax rules as for specifying a query expression. You can create long lists of Boolean expressions. For example, you could write Where clauses such as:

When MQL processes these clauses, it obtains the Boolean values for each field name and then evaluates the Boolean relationship. If the results of the evaluation are true, the object is included in the query output. If false, it is excluded.

Boolean “and” can be represented: AND, and, && .

Boolean “or” can be represented: OR, or ,|| .

Boolean “unknown” can be represented: UNKNOWN, unknown.

The following table shows the results for Boolean relationships:

The second form of the Boolean expression assumes that you have a single Boolean value. This value (represented by QUERY_EXP) can be operated upon to yield yet another Boolean value. The unary Boolean operator is the NOT operator. This operator causes the 3DSpace to use the opposite value of the current Boolean value. For example, with the expression NOT(True), the final value of the Boolean expression would be False. The unary operator has three different notations:

NOT
not
!

!UNKNOWN yields a result of UNKNOWN. The one you use is a matter of preference.

Complex Boolean Expression

With the addition of comparative expressions, query expressions can become very complicated. You can specify any amount of search criteria. Only if all the criteria is met will the object be included.

The more criteria you list, the more difficult it is to maintain readability. Remember that you can use parentheses to help readability.

When MQL encounters a complex query expression, it uses the following rules to evaluate it:

1. All arithmetic expressions are evaluated from left to right and innermost parentheses to outermost.
2. All comparative expressions are evaluated from left to right and innermost parentheses to outermost.
3. All AND operations are evaluated from left to right and innermost parentheses to outermost.
4. All OR operations are evaluated from left to right and innermost parentheses to outermost.

For example, you might use the following query to find all Drawings connected to Assembly types that have not been released and have connections from Markups.

print query relationship;
   query relationship
      business * * *
      vault *
      owner *
   where '(type==Drawing)
      && (relationship[Drawing].to.type==Assembly)
      && (current==Released)
      && (relationship[Markup].from.type==Markup)';

The type can be specified as part of the business object or in a Boolean expression.

If-Then-Else

If-then-else logic is available for Expressions. The syntax is:

if EXPRESSION1 then EXPRESSION2 else EXPRESSION3

The EXPRESSION1 term must evaluate to TRUE, FALSE, or UNKNOWN. If the EXPRESSION1 term evaluates to UNKNOWN, it is treated as TRUE.

The if-then-else expression returns the result of evaluating EXPRESSION2 or EXPRESSION3 depending on whether or not EXPRESSION1 is TRUE or FALSE.

Only one of EXPRESSION2 or EXPRESSION3 is evaluated. So if the expressions have side-effects (that can happen since expressions can run programs), these effects will not occur unless the expression is evaluated.

eval expr ' if (attribute[Actual Weight] > attribute[Target Weight]) then
 ("OVER") else ("OK")' on bus 'Body Panel' 610210 0;

Substring

The substring operator works within an expression to provide the ability to get a part of a string; the syntax is:

substring FIRST_CHAR LAST_CHAR EXPRESSION

The substring operator works as follows:

• The FIRST_CHAR and LAST_CHAR terms must evaluate to numbers that are positive or negative, and whose absolute value is between 1 and the number of characters in the string returned by EXPRESSION.
• The numbers returned by these terms indicate a character in the string returned by EXPRESSION.
• The characters are counted so that ‘1’ refers to the first character. A negative number indicates the character found by counting in the reverse direction. So ‘-1” refers to the last character.
• The substring operator returns the part of the string returned by EXPRESSION consisting of the characters from the FIRST_CHAR character to the LAST_CHAR character, inclusive.
• If FIRST_CHARevaluates to a character that is after the character indicated by LAST_CHAR, an empty string is returned.

To obtain the last 4 characters of a 10-character phone number, use:

eval expression 'substring -4 -1 attribute[Phone Number]' on bus Vendor 'XYZ Co.' 0;

Where Clause and Patterns Criteria on Selectables

You can include additional criteria (Where clause and name patterns) to refine the returned list of items when using the person.assignment, parent, and ancestor selectables. The syntax allowed in brackets follows the conventions set by similar functionality for the “relationship” keyword on business objects.

• If the string in brackets has a bar character (|), the text before the bar is treated as a pattern (string with wildcard and/or a comma-separated list) and the text after the bar is treated as an expression that acts as a Where-clause.
• If the string in brackets does not have a bar character, the entire string is treated as a pattern.

For example:

temp query bus Part * * where 'context.user.assignment[r*|isarole].name==organization'

This returns all Part objects that have their organization set to any roles assigned to the context user that begin with 'r'.