State Solutions

Expert

Provides the ability to execute a fully qualified SQL statement over one or more datasets.

Version 1

HTTP Request
POST /ado/v1/Expert

Header

ParameterDescription
Ocp-Apim-Subscription-KeyThe subscription key you received when you purchased a plan.

Request Body

Mandatory

ParameterTypeDescription
querystringFully qualified SQL statement that performs a query over the given datasets. The query is permitted to make use of any and all of the built in functions available to the framework.
dataobjectObject containing one or more properties. Each property is defined as an object array.

Common

Further Documentation: Common Parameters

ParameterTypeDescription
schemaObjectYou can use this parameter to override the inferred schema for properties in the incoming dataset(s).
A field will be inferred unless specific explicitly within this object.
advancedOptionsObjectIs an object with the following properties.

cultureName (String)
The specified culture determines the behaviour for aspects related to formatting numeric values and dates. Is extremely important when converting strings to dates, e.g. 05/03/2022 will be treated differently between locales. For more information on the accepted values for this property, please consult the documentation from Microsoft … https://docs.microsoft.com/en-us/dotnet/api/system.globalization.cultureinfo?view=net-6.0#culture-names-and-identifiers

isBoolean (string[])
A list of strings that are the names of all properties in the resulting dataset that should be treated as boolean values.

By default, the framework treats boolean values as 1 (true) or 0 (false). In order to differentiate the integer as a boolean, the property in question needs to be called out explicitly in this list.

Examples

Single Dataset
Multiple Datasets

A non-complex dataset is loaded and then transformed using numerous built-in functions.

The example itself is somewhat meaningless but highlights the flexibility of the framework as it pertains to the built-in custom functions.

Notes

Schema

The Date field is required to be cast specifically as DateTime. Failure to do this will result in an error when executing the query due to it being treated as a string.

However, changing the SQL statement to cast the Date field specifically within the query vs overriding the inferred schema is also a valid approach. e.g.

FormatDateTime(AddTime(DateTime([Date]), '16:21:46'), 'dd MMM yyy hh:mm')Code language: JavaScript (javascript)

Although the incoming date formats within each record are different, the framework resolves them correctly.

The schema is a fully qualified object with dataset names as properties given not every property with the same name may share the same data type.

Request

{
    "Query": "SELECT FormatDateTime(AddTime([Date], '16:21:46'), 'dd MMM yyy hh:mm') AS [DateTime], Trim([HeaderText]) AS [HeaderText], Concat(Concat(Trim([HeaderText]), ','), [DocumentType]) AS [ConcatenatedField], Format(Int([FiscalYear]), '#,##') AS [FY], Reverse([HeaderText]) AS 'Reverse', Proper([HeaderText], '') AS [Proper], [Header].[DocumentType] FROM [Header]",
    "Schema": {
        "Header": 
        {
            "FiscalYear": "Integer",
            "Date": "DateTime"
        }
    },
    "Data": {
        "Header": [
            {
                "DocumentNumber": "00001",
                "CompanyCode": "1000",
                "FiscalYear": "2022",
                "DocumentType": "AA",
                "HeaderText": "Test Document 1",
                "Date": "25/05/2021"
            },
            {
                "DocumentNumber": "00001",
                "CompanyCode": "2000",
                "FiscalYear": "2023",
                "DocumentType": "AA",
                "HeaderText": "Test Document 1",
                "Date": "17-10-2013"
            },
            {
                "DocumentNumber": "00002",
                "CompanyCode": "1000",
                "FiscalYear": "2022",
                "DocumentType": "AA",
                "HeaderText": "Test Document 2",
                "Date": "21 Aug 2022"
            }
        ]
    }
}
Code language: JSON / JSON with Comments (json)

Response

[
    {
        "DateTime": "25 May 2021 04:21",
        "HeaderText": "Test Document 1",
        "ConcatenatedField": "Test Document 1,AA",
        "FY": "2,022",
        "Reverse": "1 tnemucoD tseT",
        "Proper": "Test Document 1",
        "DocumentType": "AA"
    },
    {
        "DateTime": "17 Oct 2013 04:21",
        "HeaderText": "Test Document 1",
        "ConcatenatedField": "Test Document 1,AA",
        "FY": "2,023",
        "Reverse": "1 tnemucoD tseT",
        "Proper": "Test Document 1",
        "DocumentType": "AA"
    },
    {
        "DateTime": "21 Aug 2022 04:21",
        "HeaderText": "Test Document 2",
        "ConcatenatedField": "Test Document 2,AA",
        "FY": "2,022",
        "Reverse": "2 tnemucoD tseT",
        "Proper": "Test Document 2",
        "DocumentType": "AA"
    }
]
Code language: JSON / JSON with Comments (json)

Demonstrates a common join operation across a header and line table with the exception that a formatted amount field is calculated on the fly.

Although a Join operation could be used to achieve the same outcome, the Join operation does not have the ability to execute custom functions and the output of each property name (fully qualified or not) is determined by the operation.

The expert function allows for full control over the output.

Request

{
    "Query": "SELECT [Header].*, [LineItems].*, Format([Amount], '$#,##00.00') AS [FormattedAmount] FROM [Header] LEFT JOIN [LineItems] ON [Header].[DocumentNumber] = [LineItems].[DocumentNumber] AND [Header].[CompanyCode] = [LineItems].[CompanyCode] AND [Header].[FiscalYear] = [LineItems].[FiscalYear]",
    "Schema": {
        "Header": 
        {
            "FiscalYear": "Integer"
        }
    },
    "advancedOptions": {
        "CultureName": "en-AU"
    },
    "Data": {
        "Header": [
            {
                "DocumentNumber": "00001",
                "CompanyCode": "1000",
                "FiscalYear": "2022",
                "DocumentType": "AA",
                "HeaderText": "Test Document 1",
                "Date": "25/05/2022"
            },
            {
                "DocumentNumber": "00002",
                "CompanyCode": "1000",
                "FiscalYear": "2022",
                "DocumentType": "AA",
                "HeaderText": "Test Document 2",
                "Date": "28/05/2022"
            }            
        ],
        "LineItems": [
            {
                "DocumentNumber": "00001",
                "CompanyCode": "1000",
                "FiscalYear": "2022",
                "Vendor": "2600",
                "ItemText": "Vendor line 1",
                "Amount": 110
            },
            {
                "DocumentNumber": "00001",
                "CompanyCode": "1000",
                "FiscalYear": "2022",
                "GLAccount": "607000",
                "CostCenter": "10001000",
                "ItemText": "Expense line 1",
                "Amount": 100
            },
            {
                "DocumentNumber": "00001",
                "CompanyCode": "1000",
                "FiscalYear": "2022",
                "GLAccount": "607000",
                "CostCenter": "10001000",
                "ItemText": "Tax line 1",
                "Amount": 10
            },
            {
                "DocumentNumber": "00002",
                "CompanyCode": "1000",
                "FiscalYear": "2022",
                "Vendor": "3400",
                "ItemText": "Vendor line 2",
                "Amount": 1100
            },
            {
                "DocumentNumber": "00002",
                "CompanyCode": "1000",
                "FiscalYear": "2022",
                "GLAccount": "607000",
                "CostCenter": "10001000",
                "ItemText": "Expense line 2",
                "Amount": 1000
            },
            {
                "DocumentNumber": "00002",
                "CompanyCode": "1000",
                "FiscalYear": "2022",
                "GLAccount": "607000",
                "CostCenter": "10001000",
                "ItemText": "Tax line 2",
                "Amount": 100
            }                          
        ]
    }
}
Code language: JSON / JSON with Comments (json)

Response

[
    {
        "DocumentType": "AA",
        "HeaderText": "Test Document 1",
        "Date": "25/05/2022",
        "DocumentNumber": "00001",
        "CompanyCode": "1000",
        "FiscalYear": 2022,
        "Vendor": null,
        "ItemText": "Expense line 1",
        "Amount": 100,
        "GLAccount": "607000",
        "CostCenter": "10001000",
        "FormattedAmount": "$100.00"
    },
    {
        "DocumentType": "AA",
        "HeaderText": "Test Document 1",
        "Date": "25/05/2022",
        "DocumentNumber": "00001",
        "CompanyCode": "1000",
        "FiscalYear": 2022,
        "Vendor": null,
        "ItemText": "Tax line 1",
        "Amount": 10,
        "GLAccount": "607000",
        "CostCenter": "10001000",
        "FormattedAmount": "$10.00"
    },
    {
        "DocumentType": "AA",
        "HeaderText": "Test Document 1",
        "Date": "25/05/2022",
        "DocumentNumber": "00001",
        "CompanyCode": "1000",
        "FiscalYear": 2022,
        "Vendor": "2600",
        "ItemText": "Vendor line 1",
        "Amount": 110,
        "GLAccount": null,
        "CostCenter": null,
        "FormattedAmount": "$110.00"
    },
    {
        "DocumentType": "AA",
        "HeaderText": "Test Document 2",
        "Date": "28/05/2022",
        "DocumentNumber": "00002",
        "CompanyCode": "1000",
        "FiscalYear": 2022,
        "Vendor": null,
        "ItemText": "Expense line 2",
        "Amount": 1000,
        "GLAccount": "607000",
        "CostCenter": "10001000",
        "FormattedAmount": "$1,000.00"
    },
    {
        "DocumentType": "AA",
        "HeaderText": "Test Document 2",
        "Date": "28/05/2022",
        "DocumentNumber": "00002",
        "CompanyCode": "1000",
        "FiscalYear": 2022,
        "Vendor": null,
        "ItemText": "Tax line 2",
        "Amount": 100,
        "GLAccount": "607000",
        "CostCenter": "10001000",
        "FormattedAmount": "$100.00"
    },
    {
        "DocumentType": "AA",
        "HeaderText": "Test Document 2",
        "Date": "28/05/2022",
        "DocumentNumber": "00002",
        "CompanyCode": "1000",
        "FiscalYear": 2022,
        "Vendor": "3400",
        "ItemText": "Vendor line 2",
        "Amount": 1100,
        "GLAccount": null,
        "CostCenter": null,
        "FormattedAmount": "$1,100.00"
    }
]
Code language: JSON / JSON with Comments (json)