{"id":3008,"date":"2019-11-16T13:11:17","date_gmt":"2019-11-16T12:11:17","guid":{"rendered":"http:\/\/www.capri-soft.de\/blog\/?p=3008"},"modified":"2019-11-16T15:43:56","modified_gmt":"2019-11-16T14:43:56","slug":"qware-risk-manager-sql-statements-query","status":"publish","type":"post","link":"https:\/\/www.capri-soft.de\/blog\/?p=3008","title":{"rendered":"QWare Risk Manager SQL Statements \/ Query"},"content":{"rendered":"\n<p>The following statements can be used to get the Risk Measure Tree from the Bayoonet QWare Risk Manager <\/p>\n\n\n\n<p>First you should create the view QWareSegment:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; title: ; notranslate\" title=\"\">\nSELECT        QwareRiskmanager.dbo.Project.ProjectNo, QwareRiskmanager.dbo.Project.ProjectTitle, QwareRiskmanager.dbo.Version.VersionId AS ProjectVersion, \n                         QwareRiskmanager.dbo.Version.ProductVersion, QwareRiskmanager.dbo.FunctionSet.Type, QwareRiskmanager.dbo.FunctionSetFunction.FunctionNo, \n                         QwareRiskmanager.dbo.&#x5B;Function].Description AS FunctionDescription, QwareRiskmanager.dbo.FunctionHazard.HazardNo, \n                         QwareRiskmanager.dbo.Hazard.Description AS HazardDescription, QwareRiskmanager.dbo.FunctionHazardCause.CauseNo, \n                         QwareRiskmanager.dbo.Cause.Description AS CauseDescription, QwareRiskmanager.dbo.Measure.Description AS MeasureDescription, \n                         QwareRiskmanager.dbo.FunctionHazardCauseMeasure.MeasureNo, QwareRiskmanager.dbo.Measure.NewMeasureNo, \n                         QwareRiskmanager.dbo.FunctionSetFunction.FunctionId, QwareRiskmanager.dbo.FunctionHazard.HazardId, QwareRiskmanager.dbo.FunctionHazardCause.CauseId, \n                         QwareRiskmanager.dbo.FunctionHazardCauseMeasure.MeasureId, seva.Abbreviation AS AfterSeverityAbbreviation, \n                         sevb.Abbreviation AS BeforeSeverityAbbreviation, propa.Abbreviation AS AfterProbabilityAbbreviation, propb.Abbreviation AS BeforeProbabilityAbbreviation, \n                         QwareRiskmanager.dbo.FunctionHazardCause.CommentAfter, QwareRiskmanager.dbo.FunctionHazardCause.CommentBefore\nFROM            QwareRiskmanager.dbo.Project LEFT OUTER JOIN\n                         QwareRiskmanager.dbo.Version ON QwareRiskmanager.dbo.Project.ProjectId = QwareRiskmanager.dbo.Version.ProjectId LEFT OUTER JOIN\n                         QwareRiskmanager.dbo.FunctionSet ON QwareRiskmanager.dbo.Version.VersionId = QwareRiskmanager.dbo.FunctionSet.VersionId LEFT OUTER JOIN\n                         QwareRiskmanager.dbo.FunctionSetFunction ON \n                         QwareRiskmanager.dbo.FunctionSet.FunctionSetId = QwareRiskmanager.dbo.FunctionSetFunction.FunctionSetId LEFT OUTER JOIN\n                         QwareRiskmanager.dbo.&#x5B;Function] ON QwareRiskmanager.dbo.FunctionSetFunction.FunctionId = QwareRiskmanager.dbo.&#x5B;Function].FunctionId LEFT OUTER JOIN\n                         QwareRiskmanager.dbo.FunctionHazard ON \n                         QwareRiskmanager.dbo.FunctionSetFunction.FunctionSetFunctionId = QwareRiskmanager.dbo.FunctionHazard.FunctionSetFunctionId LEFT OUTER JOIN\n                         QwareRiskmanager.dbo.FunctionHazardCause ON \n                         QwareRiskmanager.dbo.FunctionHazard.FunctionHazardId = QwareRiskmanager.dbo.FunctionHazardCause.FunctionHazardId LEFT OUTER JOIN\n                         QwareRiskmanager.dbo.FunctionHazardCauseMeasure ON \n                         QwareRiskmanager.dbo.FunctionHazardCause.FunctionHazardCauseId = QwareRiskmanager.dbo.FunctionHazardCauseMeasure.FunctionHazardCauseId LEFT OUTER\n                          JOIN\n                         QwareRiskmanager.dbo.Measure ON \n                         QwareRiskmanager.dbo.FunctionHazardCauseMeasure.MeasureId = QwareRiskmanager.dbo.Measure.MeasureId LEFT OUTER JOIN\n                         QwareRiskmanager.dbo.Hazard ON QwareRiskmanager.dbo.FunctionHazard.HazardId = QwareRiskmanager.dbo.Hazard.HazardId LEFT OUTER JOIN\n                         QwareRiskmanager.dbo.Cause ON QwareRiskmanager.dbo.FunctionHazardCause.CauseId = QwareRiskmanager.dbo.Cause.CauseId LEFT OUTER JOIN\n                         QwareRiskmanager.dbo.Severity AS seva ON QwareRiskmanager.dbo.FunctionHazardCause.SeverityAfterId = seva.SeverityId AND \n                         QwareRiskmanager.dbo.Version.VersionId = seva.VersionId LEFT OUTER JOIN\n                         QwareRiskmanager.dbo.Severity AS sevb ON QwareRiskmanager.dbo.FunctionHazardCause.SeverityBeforeId = sevb.SeverityId AND \n                         QwareRiskmanager.dbo.Version.VersionId = sevb.VersionId LEFT OUTER JOIN\n                         QwareRiskmanager.dbo.Probability AS propa ON QwareRiskmanager.dbo.FunctionHazardCause.ProbabilityAfterId = propa.ProbabilityId AND \n                         QwareRiskmanager.dbo.Version.VersionId = propa.VersionId LEFT OUTER JOIN\n                        QwareRiskmanager.dbo.Probability AS propb ON QwareRiskmanager.dbo.FunctionHazardCause.ProbabilityBeforeId = propb.ProbabilityId AND \n                         QwareRiskmanager.dbo.Version.VersionId = propb.VersionId\n\n<\/pre><\/div>\n\n\n<p>After that you should create the view QWareImport that gives us the Risk Measure Tree:<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; title: ; notranslate\" title=\"\">\nCREATE VIEW &#x5B;dbo].&#x5B;QWareImport]\nAS\nSELECT     CAST(REPLACE(RTRIM(REPLACE((ISNULL(FunctionNo, &#039;&#039;) + &#039;+&#039; + ISNULL(HazardNo, &#039;&#039;) + &#039;+&#039; + ISNULL(CauseNo, &#039;&#039;) + &#039;+&#039; + ISNULL(MeasureNo, &#039;&#039;)), &#039;+&#039;, &#039; &#039;)), &#039; &#039;, &#039;+&#039;) \n                      AS varchar(255)) AS BB_ItemID, CAST(ProjectNo AS varchar(255)) AS ProjectNo, CAST(ProjectVersion AS varchar(255)) AS ProjectVersion, \n                      CAST(ProductVersion AS varchar(255)) AS ProductVersion, ISNULL(CAST(FunctionId AS varchar(255)), &#039;&#039;) AS FunctionId, ISNULL(CAST(FunctionNo AS varchar(255)), &#039;&#039;) \n                      AS BB_FID, ISNULL(CAST(FunctionDescription AS varchar(2048)), &#039;&#039;) AS BB_FText, ISNULL(CAST(HazardId AS varchar(255)), &#039;&#039;) AS HazardId, \n                      ISNULL(CAST(HazardNo AS varchar(255)), &#039;&#039;) AS BB_HID, ISNULL(CAST(HazardDescription AS varchar(2048)), &#039;&#039;) AS BB_HText, ISNULL(CAST(CauseId AS varchar(255)), \n                      &#039;&#039;) AS CauseId, ISNULL(CAST(CauseNo AS varchar(255)), &#039;&#039;) AS BB_CID, ISNULL(CAST(CauseDescription AS varchar(2048)), &#039;&#039;) AS BB_CText, \n                      ISNULL(CAST(MeasureId AS varchar(255)), &#039;&#039;) AS MeasureId, ISNULL(CAST(MeasureNo AS varchar(255)), &#039;&#039;) AS BB_MID, \n                      ISNULL(CAST(MeasureDescription AS varchar(2048)), &#039;&#039;) AS BB_MText, ISNULL(CAST(AfterSeverityAbbreviation AS varchar(255)), &#039;&#039;) AS BB_EAM_Severity, \n                      ISNULL(CAST(BeforeSeverityAbbreviation AS varchar(255)), &#039;&#039;) AS BB_EBM_Severity, ISNULL(CAST(AfterProbabilityAbbreviation AS varchar(255)), &#039;&#039;) \n                      AS BB_EAM_Probability, ISNULL(CAST(BeforeProbabilityAbbreviation AS varchar(255)), &#039;&#039;) AS BB_EBM_Probability, ISNULL(CAST(CommentAfter AS varchar(2048)), &#039;&#039;) \n                      AS BB_CCommentAM, ISNULL(CAST(CommentBefore AS varchar(2048)), &#039;&#039;) AS BB_CCommentBM, ISNULL(CAST(NewMeasureNo AS varchar(2048)), &#039;&#039;) \n                      AS BB_MNr\nFROM         DT_Qware.dbo.QWareSegment\nUNION\nSELECT     CAST(REPLACE(RTRIM(REPLACE((ISNULL(FunctionNo, &#039;&#039;) + &#039;+&#039; + ISNULL(HazardNo, &#039;&#039;) + &#039;+&#039; + ISNULL(CauseNo, &#039;&#039;)), &#039;+&#039;, &#039; &#039;)), &#039; &#039;, &#039;+&#039;) AS varchar(255)) AS BB_ItemID, \n                      CAST(ProjectNo AS varchar(255)) AS ProjectNo, CAST(ProjectVersion AS varchar(255)) AS ProjectVersion, CAST(ProductVersion AS varchar(255)) AS ProductVersion, \n                      ISNULL(CAST(FunctionId AS varchar(255)), &#039;&#039;) AS FunctionId, ISNULL(CAST(FunctionNo AS varchar(255)), &#039;&#039;) AS BB_FID, \n                      ISNULL(CAST(FunctionDescription AS varchar(2048)), &#039;&#039;) AS BB_FText, ISNULL(CAST(HazardId AS varchar(255)), &#039;&#039;) AS HazardId, \n                      ISNULL(CAST(HazardNo AS varchar(255)), &#039;&#039;) AS BB_HID, ISNULL(CAST(HazardDescription AS varchar(2048)), &#039;&#039;) AS BB_HText, ISNULL(CAST(CauseId AS varchar(255)), \n                      &#039;&#039;) AS CauseId, ISNULL(CAST(CauseNo AS varchar(255)), &#039;&#039;) AS BB_CID, ISNULL(CAST(CauseDescription AS varchar(2048)), &#039;&#039;) AS BB_CText, &#039;&#039; AS MeasureId, \n                      &#039;&#039; AS BB_MID, &#039;&#039; AS BB_MText, &#039;&#039; AS BB_EAM_Severity, &#039;&#039; AS BB_EBM_Severity, &#039;&#039; AS BB_EAM_Probability, &#039;&#039; AS BB_EBM_Probability, &#039;&#039; AS BB_CCommentAM, \n                      &#039;&#039; AS BB_CCommentBM, &#039;&#039; AS BB_MNr\nFROM         DT_Qware.dbo.QWareSegment\nUNION\nSELECT     CAST(REPLACE(RTRIM(REPLACE((ISNULL(FunctionNo, &#039;&#039;) + &#039;+&#039; + ISNULL(HazardNo, &#039;&#039;)), &#039;+&#039;, &#039; &#039;)), &#039; &#039;, &#039;+&#039;) AS varchar(255)) AS BB_ItemID, CAST(ProjectNo AS varchar(255))\n                       AS ProjectNo, CAST(ProjectVersion AS varchar(255)) AS ProjectVersion, CAST(ProductVersion AS varchar(255)) AS ProductVersion, \n                      ISNULL(CAST(FunctionId AS varchar(255)), &#039;&#039;) AS FunctionId, ISNULL(CAST(FunctionNo AS varchar(255)), &#039;&#039;) AS BB_FID, \n                      ISNULL(CAST(FunctionDescription AS varchar(2048)), &#039;&#039;) AS BB_FText, ISNULL(CAST(HazardId AS varchar(255)), &#039;&#039;) AS HazardId, \n                      ISNULL(CAST(HazardNo AS varchar(255)), &#039;&#039;) AS BB_HID, ISNULL(CAST(HazardDescription AS varchar(2048)), &#039;&#039;) AS BB_HText, &#039;&#039; AS CauseId, &#039;&#039; AS BB_CID, \n                      &#039;&#039; AS BB_CText, &#039;&#039; AS MeasureId, &#039;&#039; AS BB_MID, &#039;&#039; AS BB_MText, &#039;&#039; AS BB_EAM_Severity, &#039;&#039; AS BB_EBM_Severity, &#039;&#039; AS BB_EAM_Probability, \n                      &#039;&#039; AS BB_EBM_Probability, &#039;&#039; AS BB_CCommentAM, &#039;&#039; AS BB_CCommentBM, &#039;&#039; AS BB_MNr\nFROM         DT_Qware.dbo.QWareSegment\nUNION\nSELECT     CAST(REPLACE(RTRIM(REPLACE((ISNULL(FunctionNo, &#039;&#039;)), &#039;+&#039;, &#039; &#039;)), &#039; &#039;, &#039;+&#039;) AS varchar(255)) AS BB_ItemID, CAST(ProjectNo AS varchar(255)) AS ProjectNo, \n                      CAST(ProjectVersion AS varchar(255)) AS ProjectVersion, CAST(ProductVersion AS varchar(255)) AS ProductVersion, ISNULL(CAST(FunctionId AS varchar(255)), &#039;&#039;) \n                      AS FunctionId, ISNULL(CAST(FunctionNo AS varchar(255)), &#039;&#039;) AS BB_FID, ISNULL(CAST(FunctionDescription AS varchar(2048)), &#039;&#039;) AS BB_FText, &#039;&#039; AS HazardId, \n                      &#039;&#039; AS BB_HID, &#039;&#039; AS BB_HText, &#039;&#039; AS CauseId, &#039;&#039; AS BB_CID, &#039;&#039; AS BB_CText, &#039;&#039; AS MeasureId, &#039;&#039; AS BB_MID, &#039;&#039; AS BB_MText, &#039;&#039; AS BB_EAM_Severity, \n                      &#039;&#039; AS BB_EBM_Severity, &#039;&#039; AS BB_EAM_Probability, &#039;&#039; AS BB_EBM_Probability, &#039;&#039; AS BB_CCommentAM, &#039;&#039; AS BB_CCommentBM, &#039;&#039; AS BB_MNr\nFROM         DT_Qware.dbo.QWareSegment\n\n<\/pre><\/div>\n\n\n<p>To query a unique Risk Project in the appropriate version you have to perform the following selection<\/p>\n\n\n<div class=\"wp-block-syntaxhighlighter-code \"><pre class=\"brush: sql; title: ; notranslate\" title=\"\">\nSELECT BB_ItemID, \n\t\tProjectVersion, \n\t\tProductVersion, \n\t\tFunctionId, \n\t\tBB_FID, \n\t\tBB_FText, \n\t\tHazardId, \n\t\tBB_HID, \n\t\tBB_HText, \n\t\tCauseId, \n\t\tBB_CID, \n\t\tBB_CText, \n\t\tMeasureId, \n\t\tBB_MID, \n\t\tBB_MText, \n\t\tBB_EAM_Severity, \n\t\tBB_EBM_Severity, \n\t\tBB_EAM_Probability, \n\t\tBB_EBM_Probability, \n\t\tBB_CCommentAM, \n\t\tBB_CCommentBM,\n\t\tBB_MNr\nFROM DP_Qware.dbo.QWareImport\nWHERE ProjectNo=&#039;**ProjectNo**&#039; AND ProductVersion=&#039;**ProductVersion**&#039; \nORDER BY 1\n<\/pre><\/div><iframe src=\"http:\/\/www.facebook.com\/plugins\/like.php?href=https%3A%2F%2Fwww.capri-soft.de%2Fblog%2F%3Fp%3D3008&amp;layout=standard&amp;show_faces=true&amp;width=450&amp;action=like&amp;colorscheme=light\" scrolling=\"no\" frameborder=\"0\" allowTransparency=\"true\" style=\"border:none; overflow:hidden; width:450px;margin-top:5px;\"><\/iframe>","protected":false},"excerpt":{"rendered":"<p>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: After that you should create the view QWareImport that gives us the Risk Measure Tree: To query a unique Risk Project in the appropriate version you have to perform the &hellip; <a href=\"https:\/\/www.capri-soft.de\/blog\/?p=3008\" class=\"more-link\"><span class=\"screen-reader-text\">QWare Risk Manager SQL Statements \/ Query<\/span> weiterlesen <span class=\"meta-nav\">&rarr;<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":true,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2}},"categories":[1],"tags":[],"class_list":["post-3008","post","type-post","status-publish","format-standard","hentry","category-allgemein"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p4yGeN-Mw","jetpack_likes_enabled":true,"jetpack-related-posts":[],"_links":{"self":[{"href":"https:\/\/www.capri-soft.de\/blog\/index.php?rest_route=\/wp\/v2\/posts\/3008","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.capri-soft.de\/blog\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.capri-soft.de\/blog\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.capri-soft.de\/blog\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.capri-soft.de\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=3008"}],"version-history":[{"count":3,"href":"https:\/\/www.capri-soft.de\/blog\/index.php?rest_route=\/wp\/v2\/posts\/3008\/revisions"}],"predecessor-version":[{"id":3019,"href":"https:\/\/www.capri-soft.de\/blog\/index.php?rest_route=\/wp\/v2\/posts\/3008\/revisions\/3019"}],"wp:attachment":[{"href":"https:\/\/www.capri-soft.de\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=3008"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.capri-soft.de\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=3008"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.capri-soft.de\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=3008"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}