RunQuery Script Examples

This page contains script examples for the RunQuery script command. Note that single quotes (') in the Configuration parameter needs to be escaped by using two single quotes (usually expression queries don't contain single quotes).

Following command reads a datatable and stores it as a temporary table #MyTempTable in the scripting sandbox:

(SELECT 'TargetTable', '#MyTempTable') UNION ALL
(SELECT 'Configuration', '{
	"Root": "DatatableById(123).SqlDataFrame.Collect()"
}')
--#RunQuery

Following command reads rows from a datatable where Region is Dallas, and stores the result as a temporary table #MyTempTable in the scripting sandbox. The execution works efficiently as only the filtered rows are moved between the databases.

(SELECT 'TargetTable', '#MyTempTable') UNION ALL
(SELECT 'Configuration', '{
	"Root": "DatatableById(123).SqlDataFrame.Where(Column(\"Region\") == \"Dallas\").Collect()"
}')
--#RunQuery

Following command reads rows from a datatable where Cost is less than 1000, and stores the result as a temporary table #MyTempTable in the scripting sandbox. The execution works efficiently as only the filtered rows are moved between the databases.

(SELECT 'TargetTable', '#MyTempTable') UNION ALL
(SELECT 'Configuration', '{
	"Root": "DatatableById(123).Where(Column(\"Cost\") < 1000).Collect()"
}')
--#RunQuery

Following command runs an expression query (dimensioning by Company Code attribute and calculate cases count for each for model id 123) and stores results to a temporary table MyTempTable in the scripting sandbox:

(SELECT 'TargetTable', '#MyTempTable') UNION ALL
(SELECT 'Configuration', '{
	"Dimensions": [
		{
			"Name": "dimension0",
			"Expression": "Attribute(\"Company Code\")"
		}
	],
	"Values": [
		{
			"Name": "measure0",
			"Expression": "Count(_)",
			"AggregationExpression": "Sum(_)"
		}
	],
	"Ordering": [
		{
			"Name": "measure0",
			"Direction": "Descending"
		}
	],
	"AggregateOthers": true,
	"MaximumRowCount": 20,
	"EnableResultCaching": true,
	"ModelId": 123
}')
--#RunQuery

Following command gets a list of cases and stores results to a datatable MyDataTable:

(SELECT 'DataTableName', 'MyDataTable') UNION ALL
(SELECT 'Configuration', '{
	"Dimensions": null,
	"Values": [
		{
			"Name": "dimension0",
			"Expression": "Name"
		},
		{
			"Name": "dimension1",
			"Expression": "StartTime"
		},
		{
			"Name": "dimension2",
			"Expression": "EndTime"
		},
		{
			"Name": "dimension3",
			"Expression": "Duration?.TotalDays"
		},
		{
			"Name": "dimension4",
			"Expression": "LastEvent.TypeName"
		},
		{
			"Name": "dimension5",
			"Expression": "Variation.EventTypeCount"
		}
	],
	"AggregateOthers": false,
	"MaximumRowCount": 200,
	"Root": "Cases",
	"ModelId": 123
}')
--#RunQuery

Following command gets a list of events and stores results to a datatable MyDataTable:

(SELECT 'DataTableName', 'MyDataTable') UNION ALL
(SELECT 'Configuration', '{
	"Dimensions": null,
	"Values": [
		{
			"Name": "Case id",
			"Expression": "Case.Name"
		},
		{
			"Name": "Event type name",
			"Expression": "TypeName"
		},
		{
			"Name": "Time",
			"Expression": "Timestamp"
		},
		{
			"Name": "Difference from previous",
			"Expression": "(Timestamp-PreviousInCase.Timestamp)?.TotalDays"
		},
		{
			"Name": "Event index in case",
			"Expression": "IndexInCase+1"
		},
		{
			"Name": "Automation",
			"Expression": "Attribute(\"Automation\")"
		}
	],
	"Root": "Events",
	"ModelId": 123
}')
--#RunQuery

Following command gets a list of datatables and stores results to a datatable MyDataTable:

(SELECT 'DataTableName', 'MyDataTable') UNION ALL
(SELECT 'Configuration', '{
	"Dimensions": null,
	"Values": [
		{
			"Name": "Datatable name",
			"Expression": "Name"
		},
		{
			"Name": "Project name",
			"Expression": "Project.Name"
		},
		{
			"Name": "Datatable id",
			"Expression": "ToString(Id)"
		},
		{
			"Name": "Row count",
			"Expression": "NRows"
		},
		{
			"Name": "Column count",
			"Expression": "NColumns"
		},
		{
			"Name": "Data last imported",
			"Expression": "LastImportDate"
		}
	],
	"Root": "Datatables",
	"ContextType": "generic",
}')
--#RunQuery

Following command gets a list of models and stores results to a datatable MyDataTable:

(SELECT 'DataTableName', 'MyDataTable') UNION ALL
(SELECT 'Configuration', '{
	"Dimensions": null,
	"Values": [
		{
			"Name": "Model name",
			"Expression": "Name"
		},
		{
			"Name": "Project name",
			"Expression": "Project"
		},
		{
			"Name": "Model status",
			"Expression": "Status"
		},
		{
			"Name": "Model last modified",
			"Expression": "LastModifiedDate"
		},
		{
			"Name": "Memory size",
			"Expression": "EstimatedMemory"
		},
		{
			"Name": "Model id",
			"Expression": "Id"
		}
	],
	"Ordering": [[
		{
			"Name": "Model name",
			"Direction": "Ascending"
		}
	]],
	"AggregateOthers": false,
	"Root": "Models",
	"ContextType": "generic"
}')
--#RunQuery

Following command reads a datatable and stores its contents to another datatable (id 456):

(SELECT 'DataTableId', '456') UNION ALL
(SELECT 'Configuration', '{
	"Root": "DatatableById(123).SqlDataFrame.Collect()"
}')
--#RunQuery

Following command reads a datatable and stores its contents to another datatable (name MyDatatable in project MyProject):

(SELECT 'ProjectName', 'MyProject') UNION ALL
(SELECT 'DataTableName', 'MyDatatable') UNION ALL
(SELECT 'Configuration', '{
	"Root": "DatatableById(123).SqlDataFrame.Collect()"
}')
--#RunQuery

Following command reads a datatable and stores its contents to another datatable (name MyDatatable in project id 456):

(SELECT 'ProjectId', '456') UNION ALL
(SELECT 'DataTableName', '456') UNION ALL
(SELECT 'Configuration', '{
	"Root": "DatatableById(123).SqlDataFrame.Collect()"
}')
--#RunQuery