$Database.Get

Overview

Executes the specified query and returns results as a DataTable instance.

DataTable $Database.Get(query: Query)

Arguments

Query query

Query to execute.

Example

Basic Query

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

Example

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.

var myTable = $Database.Get({
    Parameters : {
        TargetSchema : 'HR',
        TargetTable : 'Groups'
    },
    Columns : [
        {Name : 'Order'}
    ],
    Start : 0,
    MaxLength : 1,
    Where : {
        Criteria : [
            {Name : 'Name', Value : 'Administrators', Comparison : 'Different'}
        ]
    },
    Order : [
        {Name : 'Name', Type : 'Descending'}
    ]
});

Example

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.

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'
        }]
    }
});

Example

In the query below days gained by an employee is selected with the sum function.

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

$Xml.SetValue('GeneralInfo/EarnedDays', daysGained.Rows()[0]["DaysGained"]);

// OR

totalLeaves.Each(function () {
    $Xml.SetValue('GeneralInfo/EarnedDays', this.DaysGained);
});

In the example below, offers by suppliers are grouped by the day they made with their count.

Example

Query with Expression - Advanced

var offerCountByDayTable = $Database.Get({
    Parameters : {
        TargetSchema : 'OFR',
        TargetTable : 'Offers'
    },
    Columns : [{
        Name : "Day",
        Expression : "DateTrunc('Day', OfferDate)"
    },
        {
            Name : "Amount",
            Expression : "Count(DateTrunc('Day', OfferDate))"

        }],
    Order : [
        {Name : "DateTrunc('Day',OfferDate)", Type : 'Ascending'}
    ]
});

offerCountByDayTable.Each(function () {
    console.info(this.Amount + ' offers made on the date ' + this.Day.toISOString());
});