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