RunQuery Script Examples - Revision history

MarHink: Added example for querying all case data columns in a way that supports also calculated case attributes.

2023-01-24T13:52:52Z

Added example for querying all case data columns in a way that supports also calculated case attributes.

@@ Line 335: / Line 335: @@
 }')
 --#RunQuery
-</pre>
+</pre>The following command generates a datatable containing all the cases with all their attribute values, including calculated attribute values. (model id: 123, target data table name: "MyDatatable"):<syntaxhighlight lang="sql">

Ollvihe at 14:05, 19 January 2023

2023-01-19T14:05:57Z

← Older revision		Revision as of 14:05, 19 January 2023
Line 272:		Line 272:
	"Name": "Name",		"Name": "Name",
	"Expression": "Name"		"Expression": "Name"
			},
			{
			"Name": "Project",
			"Expression": "Project.Name"
	},		},
	{		{
	"Name": "Status",		"Name": "Status",
	"Expression": "Status"		"Expression": "Status"
			},
			{
			"Name": "Language",
			"Expression": "Language"
	},		},
	{		{
Line 285:		Line 293:
	"Expression": "LastModifiedBy"		"Expression": "LastModifiedBy"
	},		},
	{
	~~"Name": "Project",~~
	~~"Expression": "Project.Name"~~
	},
	{
	~~"Name": "Language",~~
	~~"Expression": "Language"~~
	}
	{		{
	"Name": "Last run result",		"Name": "Last run result",

Ollvihe at 14:01, 19 January 2023

2023-01-19T14:01:22Z

@@ Line 258: / Line 258: @@
 	"AggregateOthers": false,
 	"Root": "Models",
 	"ContextType": "generic"
 }')

Ollvihe at 11:26, 19 January 2023

2023-01-19T11:26:34Z

@@ Line 136: / Line 136: @@
 	"Root": "Events",
 	"ModelId": 123
 }')
 --#RunQuery

Ollvihe at 11:20, 19 January 2023

2023-01-19T11:20:04Z

← Older revision		Revision as of 11:20, 19 January 2023
Line 34:		Line 34:
	"Dimensions": [		"Dimensions": [
	{		{
	"Name": "~~dimension0~~",		"Name": "Company Code",
	"Expression": "Attribute(\"Company Code\")"		"Expression": "Attribute(\"Company Code\")"
	}		}
Line 40:		Line 40:
	"Values": [		"Values": [
	{		{
	"Name": "~~measure0~~",		"Name": "Case count",
	"Expression": "Count(_)",		"Expression": "Count(_)",
	"AggregationExpression": "Sum(_)"		"AggregationExpression": "Sum(_)"
Line 51:		Line 51:
	}		}
	],		],
			"Root": "Root",
	"AggregateOthers": true,		"AggregateOthers": true,
	"MaximumRowCount": 20,		"MaximumRowCount": 20,
Line 66:		Line 67:
	"Values": [		"Values": [
	{		{
	"Name": "~~dimension0~~",		"Name": "Case ID",
	"Expression": "Name"		"Expression": "Name"
	},		},
	{		{
	"Name": "~~dimension1~~",		"Name": "Start time",
	"Expression": "StartTime"		"Expression": "StartTime"
	},		},
	{		{
	"Name": "~~dimension2~~",		"Name": "End time",
	"Expression": "EndTime"		"Expression": "EndTime"
	},		},
	{		{
	"Name": "~~dimension3~~",		"Name": "Case duration",
	"Expression": "Duration?.TotalDays"		"Expression": "Duration?.TotalDays"
	},		},
	{		{
	"Name": "~~dimension4~~",		"Name": "Latest event",
	"Expression": "LastEvent.TypeName"		"Expression": "LastEvent.TypeName"
	},		},
	{		{
	"Name": "~~dimension5~~",		"Name": "Event count",
	"Expression": "Variation.EventTypeCount"		"Expression": "Variation.EventTypeCount"
			},
			{
			"Name": "Event type count",
			"Expression": "Variation.UniqueEventTypeCount"
	}		}
	],		],
Line 105:		Line 110:
	"Values": [		"Values": [
	{		{
	"Name": "Case id",		"Name": "Case ID",
	"Expression": "Case.Name"		"Expression": "Case.Name"
	},		},
Line 113:		Line 118:
	},		},
	{		{
	"Name": "~~Time~~",		"Name": "Event time",
	"Expression": "Timestamp"		"Expression": "Timestamp"
	},		},
Line 122:		Line 127:
	{		{
	"Name": "Event index in case",		"Name": "Event index in case",
	"Expression": "IndexInCase+1"		"Expression": "IndexInCase + 1"
	},		},
	{		{

Ollvihe at 11:11, 19 January 2023

2023-01-19T11:11:59Z

← Older revision		Revision as of 11:11, 19 January 2023
Line 167:		Line 167:
	],		],
	"Root": "Datatables",		"Root": "Datatables",
	"ContextType": "generic",		"ContextType": "generic"
	}')		}')
	--#RunQuery		--#RunQuery

Ollvihe at 11:11, 19 January 2023

2023-01-19T11:11:19Z

← Older revision		Revision as of 11:11, 19 January 2023
Line 134:		Line 134:
	--#RunQuery		--#RunQuery
	</pre>		</pre>


	Following command gets a list of datatables and stores results to a datatable ''MyDataTable'':		Following command gets a list of datatables and stores results to a datatable ''MyDataTable'':
Line 172:		Line 171:
	--#RunQuery		--#RunQuery
	</pre>		</pre>


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

Ollvihe at 11:26, 20 December 2021

2021-12-20T11:26:23Z

@@ Line 214: / Line 214: @@
 	"Root": "Models",
 	"ContextType": "generic"
 }')
 --#RunQuery
 </pre>

Ollvihe at 11:22, 20 December 2021

2021-12-20T11:22:14Z

@@ Line 134: / Line 134: @@
 --#RunQuery
 </pre>
 Following command gets a list of models and stores results to a datatable ''MyDataTable'':

Ollvihe at 11:16, 20 December 2021

2021-12-20T11:16:45Z

← Older revision		Revision as of 11:16, 20 December 2021
Line 59:		Line 59:
	</pre>		</pre>

	Following command gets a list of cases ~~from model id 123~~ and stores results to a datatable ''MyDataTable'':		Following command gets a list of cases and stores results to a datatable ''MyDataTable'':
	<pre>		<pre>
	(SELECT 'DataTableName', 'MyDataTable') UNION ALL		(SELECT 'DataTableName', 'MyDataTable') UNION ALL
Line 98:		Line 98:
	</pre>		</pre>

	Following command gets a list of events ~~from model id 123~~ and stores results to a datatable ''MyDataTable'':		Following command gets a list of events and stores results to a datatable ''MyDataTable'':
	<pre>		<pre>
	(SELECT 'DataTableName', 'MyDataTable') UNION ALL		(SELECT 'DataTableName', 'MyDataTable') UNION ALL