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