Dies ist tatsächlich gar nicht so einfach, hier eine ausführliche Erklärung.
Eine einfache Query im Tab Datenbank des JobSelect, die alle offenen Schritte der Vorgänge eines Prozesses testProcess mit der Prozesstabelle PT_TEST anzeigt (zu i.status siehe TechDoc-Doku->Beschreibung der JobRouter Datenbank):
SELECT i.incident AS Vorgangsnummer, i.step AS Schrittnummer, pt.KUNDE AS Kunde
FROM
PT_TEST AS pt
INNER JOIN JRINCIDENTS AS i ON pt.step_id = i.process_step_id
WHERE
i.status IN (0, 1, 2)
So weit, so gut. Aber wie bekomme ich die Schrittbezeichnung hier rein?
Die (Übersetzungen der) Schrittbezeichnung ist als XML-Struktur in der Tabelle JRPROCESSTRANSLATIONS gespeichert, und zwar in der Zeile "steps". Hier ein Screenshot der Tabelle:
Als erstes müssen wir also diese Tabelle joinen:
SELECT i.incident AS Vorgangsnummer, i.step AS Schrittnummer, stepTrans.translation AS Schrittbezeichnung, pt.KUNDE AS Kunde
FROM
PT_TEST AS pt
INNER JOIN JRINCIDENTS AS i ON pt.step_id = i.process_step_id
INNER JOIN JRPROCESSTRANSLATIONS AS stepTrans ON stepTrans.processname = i.processname AND stepTrans.version = i.version AND stepTrans.translation_type='steps'
WHERE
i.status IN (0, 1, 2)
Damit haben wir die richtige Zeile und Spalte aus der Translations-Tabelle als XML, das Ergebnis sieht so aus:
Die darin gespeicherte XML-Struktur sieht so aus:
Wobei die id der Schrittnummer entspricht.
Wir müssen also nur noch die korrekte Schrittbezeichnung (auf deutsch) aus dem XML-String extrahieren.
Zuerst wandeln wir den Wert aus translation in langen Text um:
SELECT i.incident AS Vorgangsnummer, i.step AS Schrittnummer,
CAST(stepTrans.translation AS NVARCHAR(MAX)) AS Schrittbezeichnung,
pt.KUNDE AS Kunde
FROM
PT_TEST AS pt
INNER JOIN JRINCIDENTS AS i ON pt.step_id = i.process_step_id
INNER JOIN JRPROCESSTRANSLATIONS AS stepTrans ON stepTrans.processname = i.processname AND stepTrans.version = i.version AND stepTrans.translation_type='steps'
WHERE
i.status IN (0, 1, 2)
Im Folgenden werde ich nur noch die Veränderungen an der entsprechenden Zeile darstellen.
Eine Sache, die in diesem spezielle Fall nicht notwendig ist (aber auch nicht schadet), an anderen Stellen des JobRouter jedoch schon: Angaben zum UTF-8 Encoding werden entfernt, da DB-intern immer UTF-16 verwendet wird und dies an anderen Stellen im JobRouter zu einem Crash führen kann:
REPLACE(CAST(stepTrans.translation AS NVARCHAR(MAX)), 'encoding="UTF-8"', '') AS Schrittbezeichnung,
Das Resultat wandeln wir mit der Standard-Funktionalität CAST AS XML des SQL Server in eine XML-Struktur um:
CAST(REPLACE(CAST(stepTrans.translation AS NVARCHAR(MAX)), 'encoding="UTF-8"', '') AS XML) AS Schrittbezeichnung
Darin müssen wir nun unseren gesuchten Wert finden.
Dazu benutzen wir die Methode .value() mit einer entsprechenden XQuery-Abfrage. Die Syntax ist:
value (XQuery, SQLType)
Der SQL-Type ist zur Sicherheit NVARCHAR(MAX), fehlt noch die XQuery:
.value('(//translation[@id=sql:column("i.step")]/label/german)[1]', 'NVARCHAR(max)')
Erklärung:
.value('(// --> Suche im gesamten XML-Baum
.value('(//translation[@id= --> nach einem Knoten namens translation, der das Attribut id mit folgendem Wert enthält
.value('(//translation[@id=sql:column("i.step")] --> und hier kommt die Magic: wir können einen Wert aus dem SQL-Query-Ergebnis mitgeben (muss selektiert sein!), nämlich die Schrittnummer aus JRINCIDENTS(= die id aus dem XML-Baum)
.value('(//translation[@id=sql:column("i.step")]/label/german)[1]', 'NVARCHAR(max)') --> dort suche nach dem Child label und dort nach dem Child german und gib das erste Ergebnis zurück ( [1] )
Zusammen also:
SELECT i.incident AS Vorgangsnummer, i.step AS Schrittnummer,
CAST(REPLACE(CAST(stepTrans.translation AS NVARCHAR(MAX)), 'encoding="UTF-8"', '') AS XML).value('(//translation[@id=sql:column("i.step")]/label/german)[1]', 'NVARCHAR(max)') AS Schrittbezeichnung,
pt.KUNDE AS Kunde
FROM
PT_TEST AS pt
INNER JOIN JRINCIDENTS AS i ON pt.step_id = i.process_step_id
INNER JOIN JRPROCESSTRANSLATIONS AS stepTrans ON stepTrans.processname = i.processname AND stepTrans.version = i.version AND stepTrans.translation_type='steps'
WHERE
i.status IN (0, 1, 2)
Und damit hat man die Schrittbezeichnung
:-)