Select Clauses in Queries

In all cases, the expression is processed from the context of a starting object. In a query, the starting points are business objects that meet other selection criteria (vault, type, and so on). The phrase "starting point" is used because the select mechanism actually uses the same concept of navigation from one object to another that makes the rest of the system so flexible. This is possible because most information is actually represented internally by a small object and not by a text string or numeric value as it appears to the user.

These internal objects are all linked in much the same way business objects are connected by relationships. The links can be traversed to travel from one object to another (navigation). The presence of these links is indicated in the select expression notation by a period.

The type of select clause depends on how much data results from the clause:

Single-valued

Each single-valued Select clause produces a single field of output. Select clauses that do NOT allow square brackets are all single valued. Selecting attributes are a special case; they allow 0 or 1 value to be output according to whether the attribute is present on the business object. In keeping with the desire to have the number of outputs equal the number of Select clauses, attribute selects output an empty field in the case where the attribute does not exist on the business object.

However, other clauses that allow square brackets are format, state, relationship, to, from, revisions, history, and method. These are used in two ways:

• To check for existence. For example:

  relationship[BOM];

  format[ASCII];

  format[ASCII].hasfile.

  In these cases, the number of outputs is not ambiguous, and a true or false value is always returned.

• To get subfields. For example:

  relationship[BOM].to.name;

  from[].to.name format[ASCII].file.

These might represent zero, one or many pieces of data, depending on the number of relationships, formats, files, and so on, that are possessed by the business object, so it is not possible to guarantee that the number of outputs will equal the number of Select clauses. Therefore, selecting subfields will produce output fields only for data that is actually present. They do not output empty fields to represent the absence of data.

If you enter the single-valued Print Businessobject command for Assembly A, as follows:

print bus Assembly A '' select format[ ].file dump; 

The results indicate a Word document is included in the object Assembly A:

andersen:D:\test\Monitor FSP.doc:

If you enter the single-valued Print Businessobject command for Assembly W:

print bus Assembly W '' select format[ ].file dump;

The results indicate a Word document is included in the object Assembly W:

andersen:d:\test\Monitor FSP.doc:

Multi-valued

If you enter the multi-valued Print Businessobject command for Assembly AW:

print bus Assembly AW '' select format[ ].file dump;

The results indicate two Word documents are included in the object Assembly AW, separated by a comma:

andersen:d:\test\select.txt, andersen:d:\test\Monitor FSP.doc:

NULL-valued

If you enter the NULL valued Print Businessobject command for Assembly NONE:

print bus Assembly NONE '' select format[ ].file dump;

The results are NULL.

If you enter the Print Businessobject command with the hasfile clause for Assembly A:

print bus Assembly A '' select format[ ].hasfile dump;

The TRUE response indicates that there is a document included in the object Assembly A, without its file details.

If you enter the Print Businessobject command with the hasfile clause for Assembly W:

print bus Assembly W '' select format[ ].hasfile dump;

print bus Assembly W '' select format[ ].hasfile dump;

The TRUE response indicates that there is a document included in the object Assembly W, without its file details.

If you enter the Print Businessobject command with the Hasfile clause for Assembly AW:

print bus Assembly AW '' select format[ ].hasfile dump;

The TRUE,TRUE response indicates there are two documents included in the object Assembly AW, without their file details. One TRUE is displayed for each document.

If you enter the Print Businessobject command with the hasfile clause for Assembly NONE:

print bus Assembly NONE '' select format[ ].hasfile dump;

The NULL response indicates that there are no documents included in the object Assembly NONE.

• A NULL Select clause is NEVER equal (==, ~~, ~=) to anything
• A NULL Select clause is ALWAYS not equal (!=, !~~, !~=) to everything.

So, for our example, we have:

temp query bus Assembly  * '' where 'format[Assembly].hasfile==TRUE';

Results in:

Assembly A 
Assembly AW 

And then:

temp query bus Assembly  * '' where 'format[Assembly].hasfile != TRUE';

Results in:

Assembly NONE 
Assembly W

For example, consider these objects:

• Assembly A contains an ASCII file:

  format.file = d:\doc\select.txt

• Assembly AW contains an ASCII file and a Word document:

  format.file = d:\doc\select.txt
      format.file = d:\doc\specification.doc

• Assembly W contains a Word document:

  format.file = d:\doc\specification.doc

• Assembly DELETED contains no files.
• Assembly NONE contains no files.

If you enter:

temp query bus Assembly * * where ' "format.file" MATCH "*.doc" ';

Results in:

Assembly AW (multi-valued, and 2nd one is a match) 
Assembly W

If you enter:

temp query bus Assembly * * where ' "format.file" MATCH "*.txt" ';

Results in:

Assembly A 
Assembly AW (multi-valued, and 1st one is a match)

Using NMATCH will also pick up the objects with no files. If you enter:

temp query bus Assembly * * where ' "format.file" NMATCH "*.txt" ';

Results in:

Assembly AW (multi-valued, and NMATCH is TRUE for the 2nd one)

Assembly W (singlevalued, and NMATCH is TRUE)

Assembly DELETED (NULL, so NMATCH is always TRUE)

Assembly NONE (NULL, so NMATCH is always TRUE)

The use of these keywords in this manner solves problems of functionality and performance that are difficult, if even possible, to solve any other way.

Suppose the following query is run:

temp query bus Part * * where ((current == Approved) &&
  (attribute[Material Category] ~~ 'Plastic') &&
  (to[Component Substitution].from.name ~~ '*Clutch*') &&
    (to[Component Substitution].from.name ~~ '*Transmission*'));

The way the system runs such a query is that it would first find all objects that matched this query:

temp query bus Part * * where ((current == Approved) &&
   (attribute[Material Category] ~~ 'Plastic'));

Then the system would test the truth of the remaining clauses against each object found from the first query. This method has the potential of being extremely slow. There could be a large number of objects returned that have to be checked against the remaining clauses, and few of them might test true. Furthermore, the work needed to test the remaining clauses can be very intensive. All the to-relationships of an object must be obtained and then the name of the object at the other end must be tested until a match is found. Since the objects at the other ends can live in different vaults, a single join cannot accomplish this goal, so multiple SQL commands are needed.

These performance issues can be avoided using the toset[] selectable. Two MQL commands are required instead of one, so you must perform the query in a program. The two commands would be:

temp query bus * "*Clutch*,*Transmission*" * into set t1;temp query bus Part * * where ((current == Approved) &&   (attribute[Material Category]~~'Plastic') &&   (toset[t1,Component Substitution] == True);

The first command finds all objects that satisfy the conditions that the “objects at the other end” needed to satisfy. In this example, these conditions have been replaced by a clause that uses toset[]. These selectables, toset[] (and fromset[]), must include brackets that contain a set name followed, optionally, by a relationship type name. Multiple relationship names can be given, each separated by a comma from the previous one. In the above example,toset[] returns True if there is a relationship from an object in the set t1 of type Component Substitution to the object being tested. If no relationship type is specified, the query returns True as long as some relationship exists between such objects, regardless of type. Thefromset[] selectable works the same as toset[] except that the ends are reversed—the relationship must be to an object in t1 and from the object being tested.

With toset and fromset selectables, the values “True” and “False” are case sensitive and always appear in title case (initial capital letter followed by lowercase letters). In queries with these selectables, you must type “True” or “False” using this case to get a valid result.

What distinguishes this query from the single-command query is that the toset condition can be included with the other conditions when objects are gathered so that only objects that satisfy all the conditions of the query are displayed. As long as the first query does not put too many objects into set t1, this query should have much better performance.

The fromset and toset selectables can be followed by additional selectables, similar to the way that the selectables from and to work. In such cases, the selectable that follows is evaluated against each relationship that satisfies the condition indicated by the fromset or toset selectable. For example, fromset[t1,assembly,component].name returns a list of the names of the relationships of type assembly or component from a given object to an object in set t1.

Additionally, these selectables can be used to express conditions that cannot be expressed in a single query. Using the example above, the following conditions:

(to[Component Substitution].from.name ~~ '*Clutch*')

and

(to[Component Substitution].from.name ~~ '*Transmission*')

Each clause expresses that a given object be the to-object in a relationship of type Component Substitution where the object at the other end satisfies a given condition. These two conditions can be true even if no one object at the other end satisfies both conditions. If you need the two conditions be satisfied by the same object, this query does not express that, and no single query can. However, toset[] can be used to express this condition by making sure that the set in question contains only objects that satisfy both conditions. This goal is accomplished by replacing the command that created the set by this command:

temp query bus * "*Clutch*" * where "name match '*Transmission*'" into set t1;

Sets are workspace objects and are shared by anyone using the same login name. If two people (or applications) are logged in with the same user name, they might overwrite each other's sets if the same names (t1, t2, and so on) are used. To avoid this, developers should wrap the creation of the set and the final query inside a transaction boundary. This will keep the set from becoming visible to other sessions until it is no longer needed.

kindof might not be suitable for use in all schemas. It is designed for use in large and deep type hierarchies, where specifying TYPE in a query is inefficient.

type.kindof[TYPE_IN_HIERARCHY] can do the following:

• Get all children of the specified parent type
• Get the parent of the specified child type
• Resolve a parent/child relationship to TRUE or FALSE

For example, a Document might be a parent type having children HRForm, Purchase Request, and Proposal, (all derived from Document). You can search for the children types of Document using the following query syntax:

MQL<> temp query bus * * * where "type.kindof[Document]";

Or

MQL<> temp query bus * * * where "type.kindof[Document]==TRUE";

Both queries above result in a listing of all HRForm, Purchase Request, and Proposal objects. Any subtypes of these, such as EmploymentHistory form will also be returned.

You can also use “kindof” with print commands. When the parent name is specified in brackets, the field evaluates to true or false, depending on whether the examined type is a subtype of the type in brackets. The example below resolves the child/parent relationship of Proposal/Document to TRUE:

MQL<> print type Proposal select kindof[Document];
business type   Proposal 
    kindof[Document] = TRUE

If not passed a name in brackets, “kindof” evaluates to the name of the type’s base class, as shown below:

MQL<> print type Proposal select kindof; 
business type   Proposal 
     kindof = Document

Below is the syntax for using “print bus” and specifying type, name, and revision:

print bus Proposal WebDesign 1.0 select type.kindof[Document]; 
business object Proposal WebDesign 1.0 
     type.kindof[Document] = TRUE

and

print bus Proposal WebDesign 1.0 select type.kindof; 
business object Proposal WebDesign 1.0 
     type.kindof = Document

When escaping is enabled, the character that follows a backslash loses any special meaning it might have in the particular context.

You can specify that a backslash (\) should be treated as an escape character for any other character when used in:

• MQL commands
• Expressions, such as used in the following:
  • Where clauses (including queries, expand businessobject command, and filters)
  • Table column definitions
  • Evaluate expression command
  • Expression access
• Output of a range program
• Program arguments, such as appear in these contexts:
  • Execute program command
  • Execute businessobject command
  • Triggers

This can be specified globally or on a case-by-case basis.

set escape on;

Other commands available are:

set escape off;

print escape;

• When escape is set to on, a backslash will always act as an escape character.
• Use print escape to check whether it is enabled or not.
• Use set escape off to disable it.

The escape status is determined the first time an application is started. When setting escape, all newly started applications will use the setting, but any applications that were already started will not, including the session that made the setting. Business Administrators should decide which way to set escape and set it once.

If you want to avoid enabling the escape character globally, you can enable it on an as needed basis, prefixing each relevant string to be parsed with the keyword escape plus a space. When processed on the command line, these extra characters are first stripped off and the remainder will be processed in the usual way.

When more than one expression is specified in a command or definition, (for example, in expand bus where commands) you can escape one and not the other by including the escape clause in only the expression that needs it.

Regardless of whether escaping is enabled or disabled, expressions using the match keyword always treat ‘*’ and ‘?’ characters as wildcards and those using the is equal to “==” or does not equal “!=” keyword always treat the ‘*’ and ‘?’ characters as literals. In other words, escaping does not allow an override of this behavior.

For example the following would not work as required:

temp query bus * * * where "attribute[height]=="5'"";

Without using the escape mechanism and backslashes, this where clause would be processed as:

attribute[height]==

The text after the equal sign would be ignored, since the second quotation mark (before the 5) seems to indicate the end of the where clause (where clauses must be enclosed in single or double quotes). To correctly search for objects that have a height of five feet, you would use:

escape temp query bus * * * where "attribute[height]==\"5'\"";

With the backslashes and escape processing turned on, the where clause of this command is correctly processed as:

attribute[height]=="5'"

In some cases, however, escape processing is needed for both parsing the command into tokens and also for parsing the where clause for evaluation. The where clause parsing is done after the command is parsed as a second pass. The rules for this parsing are somewhat different, as expressions have their own syntax and special keywords. For example, to find all objects that have a height of 5’6” is tricky, since the value itself contains both single and double quotes and must also be surrounded by quotes. In this case you would need to escape process the Where clause (and include the escape character) to make it work as a Where clause, as follows:

escape attribute[height]=='5\'6"'

To perform the query, you would then need to use escape processing at the command level as well as at the expression level. You could use the following:

escape temp query bus * * * where "escape attribute[height]=='5\\'6\"'";

You must escape the backslash so that one would be in place after the initial escape processing of the command, for the second pass that escape processes the where clause. A triple backslash is required if the character used to enclose the expression is included in the expression itself, such as:

escape temp query bus * * * where 'escape attribute[height]==\'5\\\'6"\'';

In summary, escapes are needed when you want to place a value within a string that will be parsed; to have the string parsed as one token, you need to place quotes around it. Without escape processing, this only works if the string does not itself contain the quote character. With escape processing, you can use such quotes as a delimiter safely if you modify the string by following these simple rules in this order:

1. Escape each escape character in the string.
2. Escape each quote character (of the same type — single or double) in the string.

Expressions and program arguments that start with “escape” will be treated similarly, as will the output of a range program.

In general, you can use Tcl to avoid the need to add quotes around tokens to make the MQL command processor treat them correctly. That then removes the need to use an escape character with such quotes.

When an MQL command is executed in Tcl, it is Tcl that initially breaks the command into tokens. When 3DSpace receives these tokens from Tcl, it attempts, as best as possible, to put them together into a command string so that the MQL command processor will treat each of the tokens received from Tcl as a single token. So if the token contains a space or a quote character or any of a number of other characters, internally 3DSpace puts quotes around it and then put it together with the other tokens to create an MQL command string that is parsed as any normal MQL command is parsed. When escape processing is on, 3DSpace also escapes the contents of the string properly so that it will be interpreted as one token by the MQL command processor.

In Tcl programs, if you put values into a variable and then use the variable to set attribute values, you should enable escape (either globally or within the command), and 3DSpace adds the necessary backslashes to the processed commands. For example:

tcl; 
set myvar { my string with many weird characters such as ';#" - but no squiggly brace } 
mql escape modify businessobject type name rev MyAttr $myvar

If you needed to use a squiggly brace inside the squiggly braces of the set command, you would have to backslash it — to satisfy Tcl’s demands, but not MQL’s.

To get Tcl special characters passed successfully to Tcl with a minimum of fuss and worry is to create MQL commands as a Tcl list, with the list elements representing the distinct tokens as you want MQL to see them. If you want a TCL special character to be ignored by Tcl and passed through as part of a string to MQL, you need to use \.

When constructing commands in Tcl, tcl will consume backslashes because it always considers backslash as an escape character. And you have to provide additional backslashes to escape any other characters that are meaningful to Tcl (", =, ...). With complicated Tcl/MQL commands, it is more manageable to use the following programming technique:

1. Create where clauses as a single tcl string.
2. Create commands as a tcl list of strings.
3. Execute the tcl list using eval.

For example:

# Tcl parsing will turn \\ into \ ==> MQL gets TEST\'4
  set sWhere "escape name == TEST\\'4"
  set lCmd [list mql temp query bus * * * where $sWhere]
  eval $lCmd
  
  # Tcl parsing will turn \\\" into \" ==> MQL gets TEST\"4
  set sWhere "escape name == TEST\\\"4"
  set lCmd [list mql temp query bus * * * where $sWhere]
  eval $lCmd
  
  # Tcl parsing will turn \\ into \ ==> MQL gets TEST\*4
  set sWhere "escape name == TEST\\*4"
  set lCmd [list mql temp query bus * * * where $sWhere]
  eval $lCmd
  
  # Tcl parsing will turn \\\\ into \\ ==> MQL gets TEST\\4
  set sWhere "escape name == TEST\\\\4"
  set lCmd [list mql temp query bus * * * where $sWhere]
  eval $lCmd
  
  # Tcl parsing will turn \\\= into \= ==> MQL gets TEST\=4
  set sWhere "escape name == TEST\\\=4"
  set lCmd [list mql temp query bus * * * where $sWhere]
  eval $lCmd
  
  # Same logic for attributes.
  set sWhere "escape attribute\[string-u\] == A\\'B"
  set lCmd [list mql temp query bus * * * where $sWhere]
  eval $lCmd
  
  set sWhere "escape attribute\[string-u\] == A\\\"B"
  set lCmd [list mql temp query bus * * * where $sWhere]
  eval $lCmd
  
  set sWhere "escape attribute\[string-u\] == A\\*B"
  set lCmd [list mql temp query bus * * * where $sWhere]
  eval $lCmd
  
  set sWhere "escape attribute\[string-u\] == A\\\\B"
  set lCmd [list mql temp query bus * * * where $sWhere]
  eval $lCmd
  
  set sWhere "escape attribute\[string-u\] == A\\\=B"
  set lCmd [list mql temp query bus * * * where $sWhere]
  eval $lCmd

More examples, followed by the output they generate:

tcl;
eval {
 
 # Example 1: getting double-quotes passed through tcl to mql for inclusion in an attribute value
  set lCmd [list mql modify bus T1 N1 0 "Multiline String"  "String with \"double-quotes\" and more"]
  puts "\nlCmd=$lCmd"
  set mqlret [catch {eval $lCmd} sOut]
  if {$mqlret != 0} {
      puts "Error: $sOut"
  }
 
 # Example two: getting square brackets through - frequent in dealing with various selects.
  set lCmd [list mql print bus T1 N1 0 select "attribute\[Multiline String\]" "state\[one\].actual" dump]
  puts "\nlCmd=$lCmd"
  set mqlret [catch {eval $lCmd} sOut]
  if {$mqlret != 0} {
      puts "Error: $sOut"
  } else {
     puts "Result: $sOut"  }
 
 # Example 3: getting single-quotes passed through tcl to mql for inclusion in an attribute value
 # Easy - single quotes are not special to tcl
  set lCmd [list mql modify bus T1 N1 0 "Multiline String"  "String with 'single-quotes' and more"]
  puts "\nlCmd=$lCmd"
  set mqlret [catch {eval $lCmd} sOut]
  if {$mqlret != 0} {
      puts "Error: $sOut"
  }
  # Verify:
  set lCmd [list mql print bus T1 N1 0 select "attribute\[Multiline String\]" dump]
  set mqlret [catch {eval $lCmd} sOut]
  if {$mqlret != 0} {
      puts "Error: $sOut"
  } else {
     puts "Result: $sOut"
  }
}
 

Output from the above looks like this:

lCmd=mql modify bus T1 N1 0 {Multiline String} {String with 
"double-quotes" and more}
 
lCmd=mql print bus T1 N1 0 select {attribute[Multiline String]} 
{state[one].actual} dump
Result: String with "double-quotes" and more,Fri Aug 20, 2004 8:58:10 AM EDT
 
lCmd=mql modify bus T1 N1 0 {Multiline String} {String with 'single-quotes' and more}
Result: String with 'single-quotes' and more

One exception to escape processing is that it cannot be used for characters that are part of a keyword in an expression. Keywords are such things as match, attribute, ge, ==, and AND. They have a special meaning within expressions and backslashing their characters is neither necessary nor will it work properly.

Use of special characters in administrative object names must be avoided. Even escaping special characters in this case does not work as shown in the example below (the name of the attribute is “My]Attr”):

escape print bus MyType MyName MyRev select attribute[My\]Attr];

There is also one instance where a backslash does not change the function of the following character. In an MQL command, if a line with a comment ends with a backslash, the comment does not continue to the next line. For more information, see Appendix: Macros.

You can modify any query that you own, and copy any query to your own workspace that exists in any user definition to which you belong or that is defined as visible to you. As an alternative to copying definitions, business administrators can change their workspace to that of another user to work with queries that they do not own.