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());
});
See Also
- DataTable
- Expressions