$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"]);