The following example shows an expression that returns the value of a property (i.e.memberAccess-clause):

Copy 

Current.CustomerCode

The following example shows an expression that combines the result of two comparision operations:

Copy 

C.CustomerCode="Test" And C.CustomerName="Test"

The following example shows an expression with arithmethic operations:

Copy 

Current.ProcessName = 'calc' And (Current.EndTime - Current.BeginTime) * 24 * 60 < 0.25

Anmerkung
In TCQL you can use the subtract-operator (-) with date + time values. If you subtract two date + time values you will get the number of days between the two values. TCQL behaves just like Microsoft Office Excel (e.g. #2010-01-02# - #2010-01-01# will result in 1.0, #2010-01-01 14:00:00# - #2010-01-01 12:00:00# will result in 2 / 24 = 0.083333...).

The following example demonstrates how you can use the add operator to concat strings:

Copy 

Current.ProjectName + ': ' + Current.NumberOfHours

The following example shows the use of the In-operator:

Copy 

From P In Project 
Where :Iif(P.NumberOfHours=0, 1, P.NumberOfHours) In ( 1, 2 ) 
Select P

The following example shows the use of the Like-operator:

Copy 

From T In Timesheet 
Where T.Description Like '%time%' 
Select T

Anmerkung
Just like in SQL TCQL's Like operator supports two wildcard characters: % is the wildcard for 0..n characters, _ stands for exactly one character.

TCQL contains a number of functions that you can use in expressions. time cockpit currently supports the following functions:

• :Iif( condition, true-value, false-value )
• :Len( string-expression )
• :Substring( string-expression, start-index, length )
• :Replace( string-expression, string-to-find, replacing-string )
• :Year( date-time-expression )
• :Month( date-time-expression )
• :Day( date-time-expression )
• :Now()
• :Today()
• :FirstOfMonth( date-time-expression )
• :LastOfMonth( date-time-expression )
• :AddDays( date-time-expression, number-of-days-to-add )
• :AddMonths( date-time-expression, number-of-months-to-add )
• :AddYears( date-time-expression, number-of-years-to-add )
• :IsNullOrEmpty( string-expression )
• :FormatDate( date-expression, format-string ) (see MSDN Library for details about format string)
• :FormatNumber( numeric-expression, format-string ) (see MSDN Library for details about format strings)
• :FormatDateCanonical( date-expression [, boolean-expression] ) (see MSDN Library for SQL Server's canonical date format)
• :FormatDateAsPeriod( date-expression )

Anmerkung
The functions FormatDate and FormatNumber are currently not supported to be used in TCQL queries. You can only use them for in-memory handling (e.g. in formatting profiles).

The following example shows an expression that uses a function call:

Copy 

:Iif(Y.First > Y.Second, "A", "B")

Anmerkung
In contrast to SQL you do not have to write a group by clause in TCQL. The TCQL runtime will include a grouping for every expression that is not used inside an aggregation function.

The following example shows two queries that include expressions using aggregation functions:

Copy 

From T In Timesheet 
Select New With { 
        .TotalHours = Sum(T.DurationInHours) 
}

From T In Timesheet 
Select New With { 
        T.Project,
        .NumberOfBookings = Count() 
}

TCQL supports the use of subqueries in the select and the where-clause. You can reference entities using the relations' back reference names.

The following example shows the use of a subquery in the select-clause:

Copy 

From P In Project
Order By P.ProjectName
Select New With {
        P.ProjectName,
        .TotalHours = ( From T In P.Timesheets Select New With { .TotalHours = Sum(T.DurationInHours) } )
}

The following example shows the use of a subquery in the select-clause. Note that the relations' back references are used over multiple levels:

Copy 

From Cg In CustomerGroup
Order By Cg.CustomerGroupCode
Select New With {
        Cg.CustomerGroupCode,
        .TotalHours = ( From T In Cg.Customers.Projects.Timesheets Select New With { .TotalHours = Sum(T.DurationInHours) } )
}

The following example shows the use of a subqueries both in the select and the where-clause:

Copy 

From C In Customer
Order By C.CustomerName
Select New With {
        C.CustomerName,
        .MaxProjectName = ( 
                From P In C.Projects 
                Where ( From T In P.Timesheets Select New With { .TotalDuration = Sum(T.DurationInHours) } ) > 5
                Select New With { .ProjectName = Max(P.ProjectName) } )
}

The following example shows the use of a subquery in the where-clause:

Copy 

From P In Project
Where ( From T In P.Timesheets 
        Select New With { .TotalHours = Sum(T.DurationInHours) } ) > 5
Select P

Anmerkung
By now time cockpit only supports subqueries in TCQL queries but not in elements of the metadata repository (e.g. calculated properties, validation rules).

The following example shows how to use FormatDateCanonical:

Copy 

:FormatDateCanonical(#2010-10-07#)        //returns "2010-10-07"

:FormatDateCanonical(#2010-10-07#, True)        //returns "2010-10-07 00:00:00"

The following example shows how to use FormatDateAsPeriod:

Copy 

:FormatDateAsPeriod(#2010-10-07#)        //returns "2010/10"

:FormatDateAsPeriod(#2010-10-07 23:59:59#)        //returns "2010/10"

The following example shows a query that uses In Set to filter a result set based on the content of a set:

Copy 

From C In CostCenter
Where C.Code In Set("MyCostCenters", "Code")
Select C

The following example uses a In Set clause to create a calculated column in the Select clause of a query:

Copy 

From C In CostCenter
Select New With
{
        C.Code,
        C.Description,
        .IsOneOfMyCostCenters = :Iif(C.Code In Set("MyCostCenters"), True, False)
}

The following example shows a query that uses Environment.CurrentUser to filter a result set based on the current user:

Copy 

From U In UserDetail 
Where U.UserDetailUuid = Environment.CurrentUser.UserDetailUuid
Select U

The following example could appear in a permission definition. It could be used to define that only the user with the user name my.user@trash-mail.com is allowed to write to a specific entity or field.

Copy 

Environment.CurrentUser.Username = 'my.user@trash-mail.com'

Weitere Resourcen