Oracle Analyses related to variables
Variable-Related
This article offers examples for a variable related analyses with the dashboard.
Process variable template
Variables bundled in groups of three perform better. It is better to use multiple groups of three than one giant group. It is even better if the values (Indices) from the view_instance can be employed.
SELECT Variable1.1,Variable1.3,Variable1.3
FROM
(
SELECT var1.stringvalue AS "1", var2.stringvalue AS "2", var3.stringvalue AS "3", inst.ID
FROM view_instance inst
LEFT JOIN view_variable var1 ON inst.ID = var1.instanceID AND var1."name" = ''
LEFT JOIN view_variable var2 ON inst.ID = var2.instanceID AND var2."name" = ''
LEFT JOIN view_variable var3 ON inst.ID = var3.instanceID AND var3."name" = ''
) AS Variable1
INNER JOIN
(
SELECT var1.stringvalue AS "1", var2.stringvalue AS "2", var3.stringvalue AS "3", inst.ID
FROM view_instance inst
LEFT JOIN view_variable var1 ON inst.ID = var1.instanceID AND var1."name" = ''
LEFT JOIN view_variable var2 ON inst.ID = var2.instanceID AND var2."name" = ''
LEFT JOIN view_variable var3 ON inst.ID = var3.instanceID AND var3."name" = ''
) AS Variable2
ON Variable1.ID = Variable2.ID;
Listing processinstances as well as any amount of processvariables
With the following statement a list with processintances as well as a presentation of indexvariables and any processvariables can be created.
Notice: To presort the resulting list the whole SQL-Query has to be based as an INNER-SELECT and then sorted.
SELECT DISTINCT
pi."name",
/*
Selecting the indexvariables from 1 to 10, if desired:
*/
CASE WHEN pi.value1 IS NOT NULL THEN pi.value1 ELSE '-' END AS "Index 1",
CASE WHEN pi.value2 IS NOT NULL THEN pi.value2 ELSE '-' END AS "Index 2",
CASE WHEN pi.value3 IS NOT NULL THEN pi.value3 ELSE '-' END AS "Index 3",
/*
Selecting the required processvariables:
- A processVariables.- Line has to be added for every variable
*/
processVariables.VARIABLE_1_WITHOUT_SPACE AS "Header Variable 1",
processVariables.VARIABLE_2_WITHOUT_SPACE AS "Header Variable 2",
processVariables.VARIABLE_3_WITHOUT_SPACE AS "Header Variable 3",
identP.name AS "StarterID",
pi.definitionName AS "Processdefinition",
pi.id AS "ProcessID"
FROM view_instance pi
LEFT JOIN view_identity identP ON pi.creationUserId = identP.id
LEFT JOIN (
SELECT piInner.id,
/*
Creating a MAX(...) line for every desired variable
*/
MAX( CASE WHEN var.name = 'PROCESSVARIABLE 1' THEN var.stringvalue ELSE '-' END ) AS VARIABLE_1_WITHOUT_SPACE,
MAX( CASE WHEN var.name = 'PROCESSVARIABLE 2' THEN var.stringvalue ELSE '-' END ) AS VARIABLE_2_WITHOUT_SPACE,
MAX( CASE WHEN var.name = 'PROCESSVARIABLE 3' THEN var.stringvalue ELSE '-' END ) AS VARIABLE_3_WITHOUT_SPACE
FROM view_instance piInner
/*
In the following IN(...) statement all variablenames have to be listed, that are to be selected:
*/
LEFT JOIN view_variable var ON var.instanceId = piInner.id AND var.name IN ('PROCESSVARIABLE 1', 'PROCESSVARIABLE 2', 'PROCESSVARIABLE 3')
GROUP BY piInner.id
) processVariables ON pi.id = processVariables.id;
© TIM Solutions GmbH | AGB | Datenschutz | Impressum