ASQL

T-Operator 

You can reach attributes as well as relations for different DDs in CIs with the T-Operator. 

Syntax:  

T(<ClassName>).<AttributeName>  

T(<ClassName>).<RelationName>.<AttributeName>  

Examples (context SPSComputerClassBase):  

• T(SPSAssetClassBase).lnventoryNumber Inventory number of a computer  

• T(SPSCommonClassBase).Location.Name Location name of a computer 

In the above examples, lnventoryNumber is an attribute of the asset class base and the assigned Location is the name of the relation from the common class base to the location class base. 

Relations only store the ID. For example, in the case of the principal user relation, the asset class base contains the ID of the user class base. The two classes [asset & user] are fully connected and by using the relation name, all attributes of the related class can be reached. 

You can either simply define the target attribute you want to display or modify the display name by an expression. 

Example:  

Display of the first name of the main user:  

T(SPSAssetClassBase).AssignedUser.FirstName 

Create a display expression:  

T(SPSAssetClassBase).AssignedUser[LastName + ISNULL(‘, ’+ FirstName,’’)]  

If you do not define a target attribute, the system will show by default the display expression defined in the target class. 

Directive Attributes

[Expression-ObjectID] - refers to object ID in current context;

[Display-Expression] - returns the diplay string of the object

SUBQUERY Function  

You can use a subquery in different ways in Matrix42. Normally, you can use a subquery in Column Definitions, and in WHERE Conditions. Use subqueries for performance reasons in Matrix42; subqueries often perform faster when used together with NM relations. Aggregate functions normally only work together with subqueries in Column Definitions.

Example from Matrix42 UI:

Below is the complete Value from the above screenshot: 

ID in  SUBQUERY([SchemaRelation-SPSSecurityClassRole2SPSUserClassBase] as roles, roles.=members.=T(SPSScRoleClassBase).=AddressedActivities.=ID, roles.MemberOf = CURRENTUSERID())

We use here the inner join syntax without any side-effects inside the subquery as it is independent of the outer scope, there is no base alias inside of the subquery.

Below is an example of how a subquery can be used in Display Expressions. 

Shows the amount of accounts for persons:

SUBQUERY(
   SPSAccountClassBase as account,
   COUNT(all, account.*),
   account.owner=base.ID)

Generally, the SUBQUERY function is used to query related classes or to limit the result set.  

Syntax:  

SUBQUERY (<BaseClass> AS <Alias>, <TargetAttribute>, <Filter>)  

In which:  

• <BaseClass> BaseClass for the SubQuery  
• <Alias> The alias will be used for the <TargetAttribute> and <Filter>  
• <TargetAttribute> Defines the result set for the Subquery  
• <Filter> Limits the result set  

The Subquery function can also be used as an argument for the in operator or an argument for the exists method.

Example:  

We want a column in the list view for persons (SPSUserType with SPSUserClassBase) to show the number of assets they have assigned. 

=SUBQUERY(SPSAssetClassBase AS ACB, Count(all, ACB.*), ACB.AssignedUser.ID=base.ID) 

When you do counting (or you are using other aggregate functions) it is the best approach to count directly the Class you want to count, in this example SPSAssetClassBase .

CASE Function  

The CASE function allows you to discriminate between various cases depending on the values displayed.  

Syntax:  

CASE WHEN <attribute> = <value> THEN ‘Condition1 met‘ ... ELSE ‘No condition met‘ END 

In our example we will use the SQL function DateDiff(), which calculates the time difference between an attribute and the current date (getdate()) in the context of SPSContractItemClassBase.  

 =CASE WHEN DateDiff(month, getdate(), ValidUntil) < 0 THEN ‘expired’ ELSE ‘active’ END 

Special Functions 

• CurrentUserID() 
  CurrentUserID ( ) 
  Returns the ID column of the SPSUserBaseClass for the current logged in user in Console. 
• InteractiveUserID() 
  InteractiveUserID ( ) 
  Returns the ID column of the SPSUserBaseClass for the current logged in user in portal. 
• Recursive() 
  RECURSIVE ( relation_identifier ) 
  Provides all object instances of an hierarchical tree structures that are part of a parent or child branch. Can be used only for the following classes (data definitions): SPSOrgUnitClassBase, SPSLocationClassBase, SPSScCategoryClassBase, SPSArticleCategoryClassBase. 
  Examples: 
  • Recursive(OUFor).T(SPSOrgUnitClassBase).Name='XYZ' 
    Context: SPSOrgUnitClassBase. Provides all Organization Units that are part of the parent branch of the Organization Unit XYZ. 
  • T(SPSCommonClassBase).Recursive(OU).
T(SPSOrgUnitClassBase).Name='XYZ' 
    Context: SPSOrgUnitClassBase. Provides all Organization Units that are part of the child branch of the Organization Unit XYZ. 
  • Recursive(Parent).Name='Service Desk' 
    Context: SPSScCategoryClassBase. Provides all Categories that are part of the parent branch of the Category Service Desk. 
• Join() 
  JOIN( <TargetClass>, <Predicate>) 
  Defines a custom relational join between 2 data definitions. It can be used like a relation attribute, but the join condition can be defined. In the condition you can use all direct attributes from the left side (the context) with the alias L and all direct attributes from the TargetClass with the alias R. 
  Example: 
  Join(SPSAccountClassBase, R.AccountName like L.LastName  + ’%,%’ + L.FirstName).ID IS NOT NULL 
  Context: SPSUserClassBase. A condition to find all users where an account exists with an account name like <lastname, firstname> 
• TypeContext() 
  TYPECONTEXT( <TargetCI> ) 
  Defines an alias for a relation to the type class of a datadefinition. This is especially needed if you want to navigate from a commonclass to a type class. You cannot use the UsedInType column as the relation attribute is unspecific of the type. Normally you can use the T operator for this kind of navigation but for special cases like GDI staging classes you need to use this operator (The T operator is not supported for staging schemas) 
  Example: 
  TYPECONTEXT(SPSUserType).RelatedSPSUserClassBase.LastName 
  Context: SPSCommonClassBase. A way to navigate from the SPSCommonClassBase to the person CI and then to one of its memberclasses. 
• Combine() 
  COMBINE(<BaseClass> AS <Alias>, <TargetExpression>, <Filter>) 
  Provides a list of all concatenated TargetExpressions from the BaseClass that match the filter condition. 
  Example: SUBSTRING(COMBINE(SPSAccountClassBase as A, ', ' + A.AccountName, A.Owner = base.ID), 3, 100000) Context: SPSUserClassBase. Provides a list of all accounts of a user with their names separated by a comma. You must remove the first comma by using a substring function. 

Standard SQL functions 

ASQL supports the usage of all important functions provided natively by MS SQL Server’s Transact-SQL syntax. Below you will find the list of supported functions. Please refer to http://msdn.microsoft.com/de-de/library/bb510741.aspx for detailed information about the single functions. 

• Count() 
  COUNT ( { [ [ ALL | DISTINCT , ] expression ] | * } ) 
  Returns the number of items in a group. COUNT always returns an int data type value. This function can be used only within the SUBQUERY function. 

• Sum() 
  SUM ( expression ) 
  Returns the sum of all the values in the expression. SUM can be used with numeric columns only. Null values are ignored. This function can be used only within the SUBQUERY function.  

• Min() 
  MIN ( expression ) 
  Returns the minimum of all the values in the expression. MIN  can be used with numeric and datetime columns only. Null values are ignored. This function can be used only within the SUBQUERY function. 

• Max() 
  MAX ( expression ) 
  Returns the maximum of all the values in the expression. MAX  can be used with numeric and datetime columns only. Null values are ignored. This function can be used only within the SUBQUERY function. 

• IsNull() 
  ISNULL ( check_expression , replacement_value ) 
  Replaces NULL with the specified replacement value. 

• Coalesce() 
  COALESCE ( expression [ ,...n ] ) 
  Returns the first nonnull expression among its arguments. 

• Reverse() 
  REVERSE ( string_expression ) 
  Returns the reverse of a string value. 

• Left() 
  LEFT ( character_expression , integer_expression ) 
  Returns the left part of a character string with the specified number of characters. 

• Right() 
  RIGHT ( character_expression ,integer_expression ) 
  Returns the right part of a character string with the specified number of characters. 

• LTrim() 
  LTRIM ( character_expression ) 
  Returns a character expression after it removes leading blanks. 

• RTrim() 
  RTRIM ( character_expression ) 
  Returns a character string after truncating all trailing blanks. 

• Len() 
  LEN ( string_expression ) 
  Returns the number of characters of the specified string expression, excluding trailing blanks. 

• SubString() 
  SUBSTRING ( value_expression ,start_expression ,length_expression ) 
  Returns part of a character, binary, text, or image expression. 

• Replace() 
  REPLACE ( string_expression,string_pattern,string_replacement) 
  Replaces all occurrences of a specified string value with another string value. 

• PatIndex() 
  PATINDEX ( '%pattern%' , expression ) 
  Returns the starting position of the first occurrence of a pattern in a specified expression, or zeros if the pattern is not found, on all valid text and character data types. 

• GetDate() 
  GETDATE ( ) 
  Returns the current database system timestamp as a datetime value without the database time zone offset. This value is derived from the operating system of the computer on which the instance of SQL Server is running. 

• DateAdd() 
  DATEADD ( datepart , number , date ) 
  Returns a specified date with the specified number interval (signed integer) added to a specified datepart of that date. 

• DateDiff() 
  DATEDIFF ( datepart ,startdate ,enddate ) 
  Returns the count (signed integer) of the specified datepart boundaries crossed between the specified startdate and enddate. 

• DatePart() 
  DATEPART ( datepart , date ) 
  Returns an integer that represents the specified datepart of the specified date. 
  Important: dw(weekday) is not supported

• NewID() 
  NEWID ( ) 
  Creates a unique value of type uniqueidentifier. 

• Cast() 
  CAST ( expression, data_type, [date_format] ) 
  Explicitly converts an expression of one data type to another. 
  To cast Date to string, use the "date_format" to specify the date format. Like 'yyyy-MM-dd'