Hier werden die Unterschiede zwischen zwei Versionen angezeigt.
Beide Seiten der vorigen Revision Vorhergehende Überarbeitung Nächste Überarbeitung | Vorhergehende Überarbeitung | ||
software:dashboard:analyses:variable_analyses [2015/06/30 11:12] stefan.barth |
software:dashboard:analyses:variable_analyses [2021/07/01 09:52] (aktuell) |
||
---|---|---|---|
Zeile 6: | Zeile 6: | ||
Select Variablen1.1, Variablen1.2, Variablen1.3 from | Select Variablen1.1, Variablen1.2, Variablen1.3 from | ||
( | ( | ||
- | SELECT var1.stringvalue as `1`, var2.stringvalue as `2`, var3.stringvalue as `3`, inst.ID | + | SELECT var1.stringvalue as '1', var2.stringvalue as '2', var3.stringvalue as '3', inst.ID |
FROM view_instance inst | FROM view_instance inst | ||
LEFT join view_variable var1 on inst.ID = var1.instanceID and var1.name = "" | 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 var2 on inst.ID = var2.instanceID and var2.name = "" | ||
LEFT join view_variable var3 on inst.ID = var3.instanceID and var3.name = "" | LEFT join view_variable var3 on inst.ID = var3.instanceID and var3.name = "" | ||
- | where inst.definitionname = "" AND inst.archiv = "" AND inst.end IS NOT NULL | + | where inst.definitionname = "xyz" AND inst.archiv = "false" AND inst.end IS NULL |
) as Variablen1 | ) as Variablen1 | ||
INNER JOIN | INNER JOIN | ||
- | SELECT var1.stringvalue as `1`, var2.stringvalue as `2`, var3.stringvalue as `3`, inst.ID | + | ( |
+ | SELECT var1.stringvalue as '1', var2.stringvalue as '2', var3.stringvalue as '3', inst.ID | ||
FROM view_instance inst | FROM view_instance inst | ||
LEFT join view_variable var1 on inst.ID = var1.instanceID and var1.name = "" | 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 var2 on inst.ID = var2.instanceID and var2.name = "" | ||
LEFT join view_variable var3 on inst.ID = var3.instanceID and var3.name = "" | LEFT join view_variable var3 on inst.ID = var3.instanceID and var3.name = "" | ||
- | where inst.definitionname = "" AND inst.archiv = "" AND inst.end IS NOT NULL | + | where inst.definitionname = "xyz" AND inst.archiv = "false" AND inst.end IS NULL |
) as Variablen2 | ) as Variablen2 | ||
ON Variablen1.ID = Variablen2.ID | ON Variablen1.ID = Variablen2.ID | ||
+ | </code> | ||
+ | |||
+ | ==== Prozessinstanzen inklusive beliebig vielen Prozessvariablen auflisten ==== | ||
+ | Mit folgendem Statement kann eine Auflistung von Prozessinstanzen inklusive Darstellung von Indexvariablen sowie beliebigen Prozessvariablen erstellt werden. | ||
+ | |||
+ | //Hinweis: Zur Vorsortierung der Ergebnisliste muss die komplette SQL-Anweisung als INNER-SELECT geschachtelt und anschließend sortiert werden. // | ||
+ | <code sql> | ||
+ | SELECT DISTINCT | ||
+ | pi.name, | ||
+ | /* | ||
+ | Auslesen der Indexvariablen 1 bis 10 bei Bedarf: | ||
+ | */ | ||
+ | IF(pi.value1 IS NOT NULL, pi.value1, '-') AS 'Index 1', | ||
+ | IF(pi.value2 IS NOT NULL, pi.value2, '-') AS 'Index 2', | ||
+ | IF(pi.value3 IS NOT NULL, pi.value3, '-') AS 'Index 3', | ||
+ | /* | ||
+ | Auslesen der gewünschten Prozessvariablen: | ||
+ | - Für jede Variable muss eine processVariables.- Zeile eingefügt werden | ||
+ | */ | ||
+ | processVariables.VARIABLE_1_OHNE_LEERZEICHEN AS 'Header Variable 1', | ||
+ | processVariables.VARIABLE_2_OHNE_LEERZEICHEN AS 'Header Variable 2', | ||
+ | processVariables.VARIABLE_3_OHNE_LEERZEICHEN AS 'Header Variable 3', | ||
+ | identP.name AS 'StarterID', | ||
+ | pi.definitionName AS 'Prozessdefinition', | ||
+ | pi.id AS 'ProzessID' | ||
+ | FROM view_instance pi | ||
+ | LEFT JOIN view_identity identP ON pi.creationUserId = identP.id | ||
+ | LEFT JOIN ( | ||
+ | SELECT piInner.id, | ||
+ | /* | ||
+ | Erstellen einer MAX(...) Zeile für jede gewünschte Variable | ||
+ | */ | ||
+ | MAX( IF(var.name = 'PROZESSVARIABLE 1', var.stringvalue, '-')) AS VARIABLE_1_OHNE_LEERZEICHEN, | ||
+ | MAX( IF(var.name = 'PROZESSVARIABLE 2', var.stringvalue, '-')) AS VARIABLE_2_OHNE_LEERZEICHEN, | ||
+ | MAX( IF(var.name = 'PROZESSVARIABLE 3', var.stringvalue, '-')) AS VARIABLE_3_OHNE_LEERZEICHEN | ||
+ | FROM view_instance piInner | ||
+ | /* | ||
+ | Im folgenden IN (...) Statement müssen alle Variablennamen aufgelistet werden, die selektiert werden sollen: | ||
+ | */ | ||
+ | LEFT JOIN view_variable var ON var.instanceId = piInner.id AND var.name IN ('PROZESSVARIABLE 1','PROZESSVARIABLE 2','PROZESSVARIABLE 3') | ||
+ | GROUP BY piInner.id | ||
+ | ) processVariables ON pi.id = processVariables.id | ||
</code> | </code> |