QWare Risk Manager SQL Statements / Query

The following statements can be used to get the Risk Measure Tree from the Bayoonet QWare Risk Manager

First you should create the view QWareSegment:

SELECT        QwareRiskmanager.dbo.Project.ProjectNo, QwareRiskmanager.dbo.Project.ProjectTitle, QwareRiskmanager.dbo.Version.VersionId AS ProjectVersion, 
                         QwareRiskmanager.dbo.Version.ProductVersion, QwareRiskmanager.dbo.FunctionSet.Type, QwareRiskmanager.dbo.FunctionSetFunction.FunctionNo, 
                         QwareRiskmanager.dbo.[Function].Description AS FunctionDescription, QwareRiskmanager.dbo.FunctionHazard.HazardNo, 
                         QwareRiskmanager.dbo.Hazard.Description AS HazardDescription, QwareRiskmanager.dbo.FunctionHazardCause.CauseNo, 
                         QwareRiskmanager.dbo.Cause.Description AS CauseDescription, QwareRiskmanager.dbo.Measure.Description AS MeasureDescription, 
                         QwareRiskmanager.dbo.FunctionHazardCauseMeasure.MeasureNo, QwareRiskmanager.dbo.Measure.NewMeasureNo, 
                         QwareRiskmanager.dbo.FunctionSetFunction.FunctionId, QwareRiskmanager.dbo.FunctionHazard.HazardId, QwareRiskmanager.dbo.FunctionHazardCause.CauseId, 
                         QwareRiskmanager.dbo.FunctionHazardCauseMeasure.MeasureId, seva.Abbreviation AS AfterSeverityAbbreviation, 
                         sevb.Abbreviation AS BeforeSeverityAbbreviation, propa.Abbreviation AS AfterProbabilityAbbreviation, propb.Abbreviation AS BeforeProbabilityAbbreviation, 
                         QwareRiskmanager.dbo.FunctionHazardCause.CommentAfter, QwareRiskmanager.dbo.FunctionHazardCause.CommentBefore
FROM            QwareRiskmanager.dbo.Project LEFT OUTER JOIN
                         QwareRiskmanager.dbo.Version ON QwareRiskmanager.dbo.Project.ProjectId = QwareRiskmanager.dbo.Version.ProjectId LEFT OUTER JOIN
                         QwareRiskmanager.dbo.FunctionSet ON QwareRiskmanager.dbo.Version.VersionId = QwareRiskmanager.dbo.FunctionSet.VersionId LEFT OUTER JOIN
                         QwareRiskmanager.dbo.FunctionSetFunction ON 
                         QwareRiskmanager.dbo.FunctionSet.FunctionSetId = QwareRiskmanager.dbo.FunctionSetFunction.FunctionSetId LEFT OUTER JOIN
                         QwareRiskmanager.dbo.[Function] ON QwareRiskmanager.dbo.FunctionSetFunction.FunctionId = QwareRiskmanager.dbo.[Function].FunctionId LEFT OUTER JOIN
                         QwareRiskmanager.dbo.FunctionHazard ON 
                         QwareRiskmanager.dbo.FunctionSetFunction.FunctionSetFunctionId = QwareRiskmanager.dbo.FunctionHazard.FunctionSetFunctionId LEFT OUTER JOIN
                         QwareRiskmanager.dbo.FunctionHazardCause ON 
                         QwareRiskmanager.dbo.FunctionHazard.FunctionHazardId = QwareRiskmanager.dbo.FunctionHazardCause.FunctionHazardId LEFT OUTER JOIN
                         QwareRiskmanager.dbo.FunctionHazardCauseMeasure ON 
                         QwareRiskmanager.dbo.FunctionHazardCause.FunctionHazardCauseId = QwareRiskmanager.dbo.FunctionHazardCauseMeasure.FunctionHazardCauseId LEFT OUTER
                          JOIN
                         QwareRiskmanager.dbo.Measure ON 
                         QwareRiskmanager.dbo.FunctionHazardCauseMeasure.MeasureId = QwareRiskmanager.dbo.Measure.MeasureId LEFT OUTER JOIN
                         QwareRiskmanager.dbo.Hazard ON QwareRiskmanager.dbo.FunctionHazard.HazardId = QwareRiskmanager.dbo.Hazard.HazardId LEFT OUTER JOIN
                         QwareRiskmanager.dbo.Cause ON QwareRiskmanager.dbo.FunctionHazardCause.CauseId = QwareRiskmanager.dbo.Cause.CauseId LEFT OUTER JOIN
                         QwareRiskmanager.dbo.Severity AS seva ON QwareRiskmanager.dbo.FunctionHazardCause.SeverityAfterId = seva.SeverityId AND 
                         QwareRiskmanager.dbo.Version.VersionId = seva.VersionId LEFT OUTER JOIN
                         QwareRiskmanager.dbo.Severity AS sevb ON QwareRiskmanager.dbo.FunctionHazardCause.SeverityBeforeId = sevb.SeverityId AND 
                         QwareRiskmanager.dbo.Version.VersionId = sevb.VersionId LEFT OUTER JOIN
                         QwareRiskmanager.dbo.Probability AS propa ON QwareRiskmanager.dbo.FunctionHazardCause.ProbabilityAfterId = propa.ProbabilityId AND 
                         QwareRiskmanager.dbo.Version.VersionId = propa.VersionId LEFT OUTER JOIN
                        QwareRiskmanager.dbo.Probability AS propb ON QwareRiskmanager.dbo.FunctionHazardCause.ProbabilityBeforeId = propb.ProbabilityId AND 
                         QwareRiskmanager.dbo.Version.VersionId = propb.VersionId

After that you should create the view QWareImport that gives us the Risk Measure Tree:

CREATE VIEW [dbo].[QWareImport]
AS
SELECT     CAST(REPLACE(RTRIM(REPLACE((ISNULL(FunctionNo, '') + '+' + ISNULL(HazardNo, '') + '+' + ISNULL(CauseNo, '') + '+' + ISNULL(MeasureNo, '')), '+', ' ')), ' ', '+') 
                      AS varchar(255)) AS BB_ItemID, CAST(ProjectNo AS varchar(255)) AS ProjectNo, CAST(ProjectVersion AS varchar(255)) AS ProjectVersion, 
                      CAST(ProductVersion AS varchar(255)) AS ProductVersion, ISNULL(CAST(FunctionId AS varchar(255)), '') AS FunctionId, ISNULL(CAST(FunctionNo AS varchar(255)), '') 
                      AS BB_FID, ISNULL(CAST(FunctionDescription AS varchar(2048)), '') AS BB_FText, ISNULL(CAST(HazardId AS varchar(255)), '') AS HazardId, 
                      ISNULL(CAST(HazardNo AS varchar(255)), '') AS BB_HID, ISNULL(CAST(HazardDescription AS varchar(2048)), '') AS BB_HText, ISNULL(CAST(CauseId AS varchar(255)), 
                      '') AS CauseId, ISNULL(CAST(CauseNo AS varchar(255)), '') AS BB_CID, ISNULL(CAST(CauseDescription AS varchar(2048)), '') AS BB_CText, 
                      ISNULL(CAST(MeasureId AS varchar(255)), '') AS MeasureId, ISNULL(CAST(MeasureNo AS varchar(255)), '') AS BB_MID, 
                      ISNULL(CAST(MeasureDescription AS varchar(2048)), '') AS BB_MText, ISNULL(CAST(AfterSeverityAbbreviation AS varchar(255)), '') AS BB_EAM_Severity, 
                      ISNULL(CAST(BeforeSeverityAbbreviation AS varchar(255)), '') AS BB_EBM_Severity, ISNULL(CAST(AfterProbabilityAbbreviation AS varchar(255)), '') 
                      AS BB_EAM_Probability, ISNULL(CAST(BeforeProbabilityAbbreviation AS varchar(255)), '') AS BB_EBM_Probability, ISNULL(CAST(CommentAfter AS varchar(2048)), '') 
                      AS BB_CCommentAM, ISNULL(CAST(CommentBefore AS varchar(2048)), '') AS BB_CCommentBM, ISNULL(CAST(NewMeasureNo AS varchar(2048)), '') 
                      AS BB_MNr
FROM         DT_Qware.dbo.QWareSegment
UNION
SELECT     CAST(REPLACE(RTRIM(REPLACE((ISNULL(FunctionNo, '') + '+' + ISNULL(HazardNo, '') + '+' + ISNULL(CauseNo, '')), '+', ' ')), ' ', '+') AS varchar(255)) AS BB_ItemID, 
                      CAST(ProjectNo AS varchar(255)) AS ProjectNo, CAST(ProjectVersion AS varchar(255)) AS ProjectVersion, CAST(ProductVersion AS varchar(255)) AS ProductVersion, 
                      ISNULL(CAST(FunctionId AS varchar(255)), '') AS FunctionId, ISNULL(CAST(FunctionNo AS varchar(255)), '') AS BB_FID, 
                      ISNULL(CAST(FunctionDescription AS varchar(2048)), '') AS BB_FText, ISNULL(CAST(HazardId AS varchar(255)), '') AS HazardId, 
                      ISNULL(CAST(HazardNo AS varchar(255)), '') AS BB_HID, ISNULL(CAST(HazardDescription AS varchar(2048)), '') AS BB_HText, ISNULL(CAST(CauseId AS varchar(255)), 
                      '') AS CauseId, ISNULL(CAST(CauseNo AS varchar(255)), '') AS BB_CID, ISNULL(CAST(CauseDescription AS varchar(2048)), '') AS BB_CText, '' AS MeasureId, 
                      '' AS BB_MID, '' AS BB_MText, '' AS BB_EAM_Severity, '' AS BB_EBM_Severity, '' AS BB_EAM_Probability, '' AS BB_EBM_Probability, '' AS BB_CCommentAM, 
                      '' AS BB_CCommentBM, '' AS BB_MNr
FROM         DT_Qware.dbo.QWareSegment
UNION
SELECT     CAST(REPLACE(RTRIM(REPLACE((ISNULL(FunctionNo, '') + '+' + ISNULL(HazardNo, '')), '+', ' ')), ' ', '+') AS varchar(255)) AS BB_ItemID, CAST(ProjectNo AS varchar(255))
                       AS ProjectNo, CAST(ProjectVersion AS varchar(255)) AS ProjectVersion, CAST(ProductVersion AS varchar(255)) AS ProductVersion, 
                      ISNULL(CAST(FunctionId AS varchar(255)), '') AS FunctionId, ISNULL(CAST(FunctionNo AS varchar(255)), '') AS BB_FID, 
                      ISNULL(CAST(FunctionDescription AS varchar(2048)), '') AS BB_FText, ISNULL(CAST(HazardId AS varchar(255)), '') AS HazardId, 
                      ISNULL(CAST(HazardNo AS varchar(255)), '') AS BB_HID, ISNULL(CAST(HazardDescription AS varchar(2048)), '') AS BB_HText, '' AS CauseId, '' AS BB_CID, 
                      '' AS BB_CText, '' AS MeasureId, '' AS BB_MID, '' AS BB_MText, '' AS BB_EAM_Severity, '' AS BB_EBM_Severity, '' AS BB_EAM_Probability, 
                      '' AS BB_EBM_Probability, '' AS BB_CCommentAM, '' AS BB_CCommentBM, '' AS BB_MNr
FROM         DT_Qware.dbo.QWareSegment
UNION
SELECT     CAST(REPLACE(RTRIM(REPLACE((ISNULL(FunctionNo, '')), '+', ' ')), ' ', '+') AS varchar(255)) AS BB_ItemID, CAST(ProjectNo AS varchar(255)) AS ProjectNo, 
                      CAST(ProjectVersion AS varchar(255)) AS ProjectVersion, CAST(ProductVersion AS varchar(255)) AS ProductVersion, ISNULL(CAST(FunctionId AS varchar(255)), '') 
                      AS FunctionId, ISNULL(CAST(FunctionNo AS varchar(255)), '') AS BB_FID, ISNULL(CAST(FunctionDescription AS varchar(2048)), '') AS BB_FText, '' AS HazardId, 
                      '' AS BB_HID, '' AS BB_HText, '' AS CauseId, '' AS BB_CID, '' AS BB_CText, '' AS MeasureId, '' AS BB_MID, '' AS BB_MText, '' AS BB_EAM_Severity, 
                      '' AS BB_EBM_Severity, '' AS BB_EAM_Probability, '' AS BB_EBM_Probability, '' AS BB_CCommentAM, '' AS BB_CCommentBM, '' AS BB_MNr
FROM         DT_Qware.dbo.QWareSegment

To query a unique Risk Project in the appropriate version you have to perform the following selection

SELECT BB_ItemID, 
		ProjectVersion, 
		ProductVersion, 
		FunctionId, 
		BB_FID, 
		BB_FText, 
		HazardId, 
		BB_HID, 
		BB_HText, 
		CauseId, 
		BB_CID, 
		BB_CText, 
		MeasureId, 
		BB_MID, 
		BB_MText, 
		BB_EAM_Severity, 
		BB_EBM_Severity, 
		BB_EAM_Probability, 
		BB_EBM_Probability, 
		BB_CCommentAM, 
		BB_CCommentBM,
		BB_MNr
FROM DP_Qware.dbo.QWareImport
WHERE ProjectNo='**ProjectNo**' AND ProductVersion='**ProductVersion**' 
ORDER BY 1

Atlassian JIRA mit MS SQL : Select the text, date or string value from a custom field of an issue

Problem

A string, date or a text-value of an custom field shall be retrieved from an issue

Solution

SELECT	pk.PROJECT_KEY+'-'+CAST(ji.issuenum AS varchar(max)) as issue, 
		cfv.STRINGVALUE, 
		cfv.TEXTVALUE,
		cfv.DATEVALUE
FROM jiraissue ji 
INNER JOIN customfieldvalue cfv ON ji.id=cfv.issue 
INNER JOIN project_key pk ON ji.PROJECT=pk.PROJECT_ID 
WHERE pk.PROJECT_KEY+'-'+CAST(ji.issuenum AS varchar(max))='AT-15' 
AND cfv.CUSTOMFIELD=(SELECT TOP 1 id FROM customfield WHERE cfname='Requested document')

Atlassian JIRA MS SQL Server: Get Affects version or fixed version from the Database by query

Problem

To fetch the values „Affects version“ or „Fixed Version“ from the JIRA Database via SQL, the use of different relations/tables is necessary

Solution – Lösung

You can use the ASSOCIATION_TYPE=’IssueVersion‘ for Affects version and ASSOCIATION_TYPE=’IssueFixVersion‘ to get the Fixed version

The next example shows how to retrieve Affects Version for Issue-ID AT-15:

SELECT projectversion.id, vname, project_key.PROJECT_KEY+'-'+CAST(jiraissue.issuenum AS varchar(max)) as pkey, ASSOCIATION_TYPE
FROM   projectversion, nodeassociation, jiraissue, project_key
WHERE  SINK_NODE_ID = projectversion.id
AND    SOURCE_NODE_ID = jiraissue.id
AND    project_key.PROJECT_ID=jiraissue.PROJECT
AND    ASSOCIATION_TYPE='IssueVersion' 
AND    project_key.PROJECT_KEY+'-'+CAST(jiraissue.issuenum AS varchar(max))='AT-15'