$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

• Data table