Skip to content

$Database.Get

Overview

DataTable $Database.Get ( options )

xecutes the specified query and returns results as a data table instance.

Arguments

Object options Specifies the query options.

Remarks

Formula

Formula specifies the method of evaluation of row values. Formula enumeration can be one of following values;

Value Description
None No aggregation is applied. All values in rows.
Count Count of row values.
CountDistinct Count of distinct values in rows.
Sum Sum of row values. Valid for numeric type columns.
SumDistinct Sum of distinct values in rows. Valid for numeric type columns.
Avg Average of row values. Valid for numeric type columns.
AvgDistinct Average of distinct values in rows. Valid for numeric type columns.
Min Minimum value in rows.
Max Maximum value in row values.

Comparison

Specifies the comparison method of criteria value.

Value Description
Equals All rows with same value.
LessThan All rows that less than criteria value.
LessThanOrEqualTo All rows that less or equals to criteria value.
GreaterThan All rows that greater than criteria value.
GreaterThanOrEqualTo All rows that greater or equals to criteria value.
Different All rows with different than criteria value.
Like All rows that likes to criteria value. Criteria value can be used with * (star character) Like ABC, ABC, ABC

Examples

Basic Query;

var myTable = $Database.Get({
   Parameters : {
     TargetSchema: 'HR',
     TargetTable: 'Groups'
   },
   Where: {
     Criteria: [
       {
         name: 'Name',
         value: 'Administrators'
       }
     ]
   }, 
    Order : [
       { Name : 'Order', Type : 'Ascending' }
   ]
 });

In the example below we queried the Makers table. The result is ordered in descending order and there is only one row because we set the Start parameter to 0 and MaxLength to 1.

Basic Query;

var myTable = $Database.Get({
   Parameters: {
       TargetSchema: 'ENotification',
       TargetTable: 'Makers'
   },
   Columns: [
       { Name : 'Order'}
   ],
   Start : 0,
   MaxLength : 1,
   Where : {
       Criteria: [
           { Name: 'IsActive', Value: true },
           { Name : 'Durum', Value : 'IPT', Comparison : 'Different' }
       ]
   },
   Order : [
       { Name : 'Order', Type : 'Descending' }
   ]
});

For example if you need a more complex query, containing multiple AND/OR conditions like "(X OR Y OR Z) AND W", you can use Blocks option. Check the code sample below.

Advanced Query;

var myTable = $Database.Get({
    Parameters: {
        TargetSchema: 'HR',
        TargetTable: 'UserLogons'
    },
    Where: {
        Blocks: [{
            Condition: 'And',
            Criteria: [{
                name: 'User.EMailAddress',
                value: '...',
                Condition: 'Or'
            }, {
                name: 'User.EMailAddress',
                value: '...',
                Condition: 'Or'
            }, {
                name: 'User.EMailAddress',
                value: '...'
            }, ]
        }],
        Criteria: [{
            name: 'User.Disabled',
            value: 'true',
            Condition: 'And',
            Comparison: 'Different'
        }]
    }
});

Query with Formulas;

var daysGained = $Database.Get({
    Parameters: {
        TargetSchema: 'HR',
        TargetTable: 'LeaveAllowance'
    },
    Columns: [{
        Name: 'DaysGained',
        Formula: "Sum"
    }],
    Start: 0,
    MaxLength: 1,
    Where: {
        Criteria: [{
            Name: 'Employee',
            Value: $Initiator.Id
        }]
    }
});
$Xml.SetValue('GeneralInfo/EarnedDays', daysGained.Rows()[0]["DaysGained"]);

See Also