Index: Application/Ringtoets/src/Application.Ringtoets.Migration.Core/EmbeddedResources/Migration_17.1_17.2.sql =================================================================== diff -u -ra25ab906f08e5e263ebb35920acc3ee1bdd928df -rd6ea7409862b495dba150e049a8ebe843885d800 --- Application/Ringtoets/src/Application.Ringtoets.Migration.Core/EmbeddedResources/Migration_17.1_17.2.sql (.../Migration_17.1_17.2.sql) (revision a25ab906f08e5e263ebb35920acc3ee1bdd928df) +++ Application/Ringtoets/src/Application.Ringtoets.Migration.Core/EmbeddedResources/Migration_17.1_17.2.sql (.../Migration_17.1_17.2.sql) (revision d6ea7409862b495dba150e049a8ebe843885d800) @@ -7,7 +7,7 @@ ATTACH DATABASE "{0}" AS SOURCEPROJECT; -CREATE TEMP TABLE TempAssessmentReturnPeriod +CREATE TEMP TABLE TempAssessmentReturnPeriod ( [AssessmentSectionId] TEXT, [SignalingReturnPeriod] INTEGER NOT NULL, @@ -265,7 +265,7 @@ [Composition], [ReferenceLinePointXml], [Order]) -SELECT +SELECT [AssessmentSectionEntityId], [ProjectEntityId], CASE @@ -368,7 +368,7 @@ [IdenticalApertures], [FailureProbabilityReparation], [InflowModelType]) -SELECT +SELECT [ClosingStructureEntityId], [FailureMechanismEntityId], [Order], @@ -420,7 +420,7 @@ FROM [SOURCEPROJECT].ClosingStructureEntity ) ); -INSERT INTO ClosingStructuresCalculationEntity( +INSERT INTO ClosingStructuresCalculationEntity ( [ClosingStructuresCalculationEntityId], [CalculationGroupEntityId], [ForeshoreProfileEntityId], @@ -510,23 +510,23 @@ [StormDurationMean], [FactorStormDurationOpenStructure], 0 -FROM [SOURCEPROJECT].ClosingStructuresCalculationEntity; + FROM [SOURCEPROJECT].ClosingStructuresCalculationEntity; INSERT INTO ClosingStructuresFailureMechanismMetaEntity ( [ClosingStructuresFailureMechanismMetaEntityId], [FailureMechanismEntityId], [N2A], [ClosingStructureCollectionSourcePath], [ForeshoreProfileCollectionSourcePath]) -SELECT +SELECT [ClosingStructuresFailureMechanismMetaEntityId], [FailureMechanismEntityId], [N2A], - CASE + CASE WHEN COUNT([ClosingStructureEntityId]) THEN "" ELSE NULL END, - CASE + CASE WHEN COUNT([ForeshoreProfileEntityId]) THEN "" ELSE NULL @@ -535,7 +535,7 @@ LEFT JOIN [SOURCEPROJECT].ClosingStructureEntity USING(FailureMechanismEntityId) LEFT JOIN [SOURCEPROJECT].ForeshoreProfileEntity USING(FailureMechanismEntityId) GROUP BY FailureMechanismEntityID; -INSERT INTO ClosingStructuresOutputEntity( +INSERT INTO ClosingStructuresOutputEntity ( [ClosingStructuresOutputEntityId], [ClosingStructuresCalculationEntityId], [GeneralResultFaultTreeIllustrationPointEntityId], @@ -559,9 +559,8 @@ [FailureMechanismSectionEntityId], [ClosingStructuresCalculationEntityId], [LayerOne], - [LayerThree] -) -SELECT + [LayerThree]) +SELECT [ClosingStructuresSectionResultEntityId], [FailureMechanismSectionEntityId], [ClosingStructuresCalculationEntityId], @@ -580,7 +579,7 @@ INSERT INTO DuneLocationOutputEntity SELECT * FROM [SOURCEPROJECT].DuneLocationOutputEntity; INSERT INTO FailureMechanismEntity SELECT * FROM [SOURCEPROJECT].FailureMechanismEntity; INSERT INTO FailureMechanismSectionEntity SELECT * FROM [SOURCEPROJECT].FailureMechanismSectionEntity; -INSERT INTO ForeshoreProfileEntity( +INSERT INTO ForeshoreProfileEntity ( [ForeshoreProfileEntityId], [FailureMechanismEntityId], [Id], @@ -597,9 +596,9 @@ [ForeshoreProfileEntityId], [FailureMechanismEntityId], CASE - WHEN Suffix - THEN [Id] || SUBSTR(QUOTE(ZEROBLOB((SuffixPreLength + 1) / 2)), 3, SuffixPreLength) || Suffix - ELSE [Id] + WHEN Suffix + THEN [Id] || SUBSTR(QUOTE(ZEROBLOB((SuffixPreLength + 1) / 2)), 3, SuffixPreLength) || Suffix + ELSE [Id] END AS [Id], [Name], [Orientation], @@ -626,7 +625,7 @@ FROM [SOURCEPROJECT].ForeshoreProfileEntity ) ); -INSERT INTO GrassCoverErosionInwardsCalculationEntity( +INSERT INTO GrassCoverErosionInwardsCalculationEntity ( [GrassCoverErosionInwardsCalculationEntityId], [CalculationGroupEntityId], [HydraulicLocationEntityId], @@ -669,7 +668,7 @@ 0, 0 FROM [SOURCEPROJECT].GrassCoverErosionInwardsCalculationEntity; -INSERT INTO GrassCoverErosionInwardsDikeHeightOutputEntity( +INSERT INTO GrassCoverErosionInwardsDikeHeightOutputEntity ( [GrassCoverErosionInwardsDikeHeightOutputEntityId], [GrassCoverErosionInwardsOutputEntityId], [GeneralResultFaultTreeIllustrationPointEntityId], @@ -691,7 +690,7 @@ [CalculationConvergence] FROM [SOURCEPROJECT].GrassCoverErosionInwardsDikeHeightOutputEntity; INSERT INTO GrassCoverErosionInwardsFailureMechanismMetaEntity SELECT * FROM [SOURCEPROJECT].GrassCoverErosionInwardsFailureMechanismMetaEntity; -INSERT INTO GrassCoverErosionInwardsOutputEntity( +INSERT INTO GrassCoverErosionInwardsOutputEntity ( [GrassCoverErosionInwardsOutputEntityId], [GrassCoverErosionInwardsCalculationEntityId], [GeneralResultFaultTreeIllustrationPointEntityId], @@ -716,7 +715,7 @@ [Reliability], [FactorOfSafety] FROM [SOURCEPROJECT].GrassCoverErosionInwardsOutputEntity; -INSERT INTO GrassCoverErosionInwardsOvertoppingRateOutputEntity( +INSERT INTO GrassCoverErosionInwardsOvertoppingRateOutputEntity ( [GrassCoverErosionInwardsOvertoppingRateOutputEntityId], [GrassCoverErosionInwardsOutputEntityId], [GeneralResultFaultTreeIllustrationPointEntityId], @@ -737,7 +736,7 @@ [CalculatedReliability], [CalculationConvergence] FROM [SOURCEPROJECT].GrassCoverErosionInwardsOvertoppingRateOutputEntity; -INSERT INTO GrassCoverErosionInwardsSectionResultEntity( +INSERT INTO GrassCoverErosionInwardsSectionResultEntity ( [GrassCoverErosionInwardsSectionResultEntityId], [FailureMechanismSectionEntityId], [GrassCoverErosionInwardsCalculationEntityId], @@ -760,7 +759,7 @@ [FailureMechanismEntityId], [N], [ForeshoreProfileCollectionSourcePath]) -SELECT +SELECT [GrassCoverErosionOutwardsFailureMechanismMetaEntityId], [FailureMechanismEntityId], [N], @@ -772,7 +771,7 @@ FROM [SOURCEPROJECT].GrassCoverErosionOutwardsFailureMechanismMetaEntity LEFT JOIN [SOURCEPROJECT].ForeshoreProfileEntity USING(FailureMechanismEntityId) GROUP BY FailureMechanismEntityID; -INSERT INTO GrassCoverErosionOutwardsHydraulicLocationEntity( +INSERT INTO GrassCoverErosionOutwardsHydraulicLocationEntity ( [GrassCoverErosionOutwardsHydraulicLocationEntityId], [FailureMechanismEntityId], [LocationId], @@ -782,7 +781,7 @@ [ShouldWaveHeightIllustrationPointsBeCalculated], [ShouldDesignWaterLevelIllustrationPointsBeCalculated], [Order]) -SELECT +SELECT [GrassCoverErosionOutwardsHydraulicLocationEntityId], [FailureMechanismEntityId], [LocationId], @@ -793,7 +792,7 @@ 0, [Order] FROM [SOURCEPROJECT].GrassCoverErosionOutwardsHydraulicLocationEntity; -INSERT INTO GrassCoverErosionOutwardsHydraulicLocationOutputEntity( +INSERT INTO GrassCoverErosionOutwardsHydraulicLocationOutputEntity ( [GrassCoverErosionOutwardsHydraulicLocationOutputEntityId], [GrassCoverErosionOutwardsHydraulicLocationEntityId], [GeneralResultSubMechanismIllustrationPointEntityId], @@ -843,14 +842,14 @@ [StorageStructureAreaCoefficientOfVariation], [AllowedLevelIncreaseStorageMean], [AllowedLevelIncreaseStorageStandardDeviation]) -SELECT +SELECT [HeightStructureEntityId], [FailureMechanismEntityId], [Order], [Name], CASE WHEN Suffix - THEN [Id] || SUBSTR(QUOTE(ZEROBLOB((SuffixPreLength + 1) / 2)), 3, SuffixPreLength)|| Suffix + THEN [Id] || SUBSTR(QUOTE(ZEROBLOB((SuffixPreLength + 1) / 2)), 3, SuffixPreLength)|| Suffix ELSE [Id] END AS [Id], [X], @@ -884,7 +883,7 @@ FROM [SOURCEPROJECT].HeightStructureEntity ) ); -INSERT INTO HeightStructuresCalculationEntity( +INSERT INTO HeightStructuresCalculationEntity ( [HeightStructuresCalculationEntityId], [CalculationGroupEntityId], [HydraulicLocationEntityId], @@ -953,16 +952,16 @@ [N], [HeightStructureCollectionSourcePath], [ForeshoreProfileCollectionSourcePath]) -SELECT +SELECT [HeightStructuresFailureMechanismMetaEntityId], [FailureMechanismEntityId], [N], - CASE + CASE WHEN COUNT([HeightStructureEntityId]) THEN "" ELSE NULL END, - CASE + CASE WHEN COUNT([ForeshoreProfileEntityId]) THEN "" ELSE NULL @@ -971,7 +970,7 @@ LEFT JOIN [SOURCEPROJECT].HeightStructureEntity USING(FailureMechanismEntityId) LEFT JOIN [SOURCEPROJECT].ForeshoreProfileEntity USING(FailureMechanismEntityId) GROUP BY FailureMechanismEntityID; -INSERT INTO HeightStructuresOutputEntity( +INSERT INTO HeightStructuresOutputEntity ( [HeightStructuresOutputEntityId], [HeightStructuresCalculationEntityId], [GeneralResultFaultTreeIllustrationPointEntityId], @@ -990,14 +989,13 @@ [Reliability], [FactorOfSafety] FROM [SOURCEPROJECT].HeightStructuresOutputEntity; -INSERT INTO HeightStructuresSectionResultEntity( +INSERT INTO HeightStructuresSectionResultEntity ( [HeightStructuresSectionResultEntityId], [FailureMechanismSectionEntityId], [HeightStructuresCalculationEntityId], [LayerOne], - [LayerThree] -) -SELECT + [LayerThree]) +SELECT [HeightStructuresSectionResultEntityId], [FailureMechanismSectionEntityId], [HeightStructuresCalculationEntityId], @@ -1019,7 +1017,7 @@ [ShouldWaveHeightIllustrationPointsBeCalculated], [ShouldDesignWaterLevelIllustrationPointsBeCalculated], [Order]) -SELECT +SELECT [HydraulicLocationEntityId], [AssessmentSectionEntityId], [LocationId], @@ -1028,9 +1026,9 @@ [LocationY], 0, 0, - [Order] + [Order] FROM [SOURCEPROJECT].HydraulicLocationEntity; -INSERT INTO HydraulicLocationOutputEntity( +INSERT INTO HydraulicLocationOutputEntity ( [HydraulicLocationEntityOutputId], [HydraulicLocationEntityId], [GeneralResultSubMechanismIllustrationPointEntityId], @@ -1041,7 +1039,7 @@ [CalculatedProbability], [CalculatedReliability], [CalculationConvergence]) -SELECT +SELECT [HydraulicLocationEntityOutputId], [HydraulicLocationEntityId], NULL, @@ -1090,7 +1088,7 @@ [ScenarioContribution], [AssessmentLevel], [UseAssessmentLevelManualInput]) -SELECT +SELECT [PipingCalculationEntityId], [CalculationGroupEntityId], [SurfaceLineEntityId], @@ -1118,20 +1116,20 @@ [WaterVolumetricWeight], [StochasticSoilModelCollectionSourcePath], [SurfaceLineCollectionSourcePath]) -SELECT +SELECT [PipingFailureMechanismMetaEntityId], [FailureMechanismEntityId], [A], [WaterVolumetricWeight], [StochasticSoilModelSourcePath], [SurfaceLineSourcePath] FROM [SOURCEPROJECT].PipingFailureMechanismMetaEntity; -INSERT INTO PipingSectionResultEntity( +INSERT INTO PipingSectionResultEntity ( [PipingSectionResultEntityId], [FailureMechanismSectionEntityId], [LayerOne], [LayerThree]) -SELECT +SELECT [PipingSectionResultEntityId], [FailureMechanismSectionEntityId], [LayerOne], @@ -1146,7 +1144,7 @@ INSERT INTO PipingSemiProbabilisticOutputEntity SELECT * FROM [SOURCEPROJECT].PipingSemiProbabilisticOutputEntity; INSERT INTO PipingStructureSectionResultEntity SELECT * FROM [SOURCEPROJECT].PipingStructureSectionResultEntity; INSERT INTO ProjectEntity SELECT * FROM [SOURCEPROJECT].ProjectEntity; -INSERT INTO PipingSoilLayerEntity( +INSERT INTO PipingSoilLayerEntity ( [PipingSoilLayerEntityId], [PipingSoilProfileEntityId], [Top], @@ -1161,7 +1159,7 @@ [PermeabilityMean], [PermeabilityCoefficientOfVariation], [Order]) -SELECT +SELECT [SoilLayerEntityId], [SoilProfileEntityId], [Top], @@ -1182,12 +1180,12 @@ [PermeabilityCoefficientOfVariation], [Order] FROM [SOURCEPROJECT].SoilLayerEntity; -INSERT INTO PipingSoilProfileEntity( +INSERT INTO PipingSoilProfileEntity ( [PipingSoilProfileEntityId], [Bottom], [Name], [SourceType]) -SELECT +SELECT [SoilProfileEntityId], [Bottom], CASE @@ -1327,7 +1325,7 @@ FROM [SOURCEPROJECT].StabilityPointStructureEntity ) ); -INSERT INTO StabilityPointStructuresCalculationEntity( +INSERT INTO StabilityPointStructuresCalculationEntity ( [StabilityPointStructuresCalculationEntityId], [CalculationGroupEntityId], [ForeshoreProfileEntityId], @@ -1392,7 +1390,7 @@ [DrainCoefficientMean], [FailureProbabilityStructureWithErosion], [ShouldIllustrationPointsBeCalculated]) -SELECT +SELECT [StabilityPointStructuresCalculationEntityId], [CalculationGroupEntityId], [ForeshoreProfileEntityId], @@ -1468,7 +1466,7 @@ [StrengthStabilityPointConstructionFailureMechanismMetaEntityId], [FailureMechanismEntityId], [N], - CASE + CASE WHEN COUNT([ForeshoreProfileEntityId]) THEN "" ELSE NULL @@ -1482,7 +1480,7 @@ LEFT JOIN [SOURCEPROJECT].ForeshoreProfileEntity USING (FailureMechanismEntityId) LEFT JOIN [SOURCEPROJECT].StabilityPointStructureEntity USING (FailureMechanismEntityId) GROUP BY FailureMechanismEntityId; -INSERT INTO StabilityPointStructuresOutputEntity( +INSERT INTO StabilityPointStructuresOutputEntity ( [StabilityPointStructuresOutputEntity], [StabilityPointStructuresCalculationEntityId], [GeneralResultFaultTreeIllustrationPointEntityId], @@ -1501,7 +1499,7 @@ [Reliability], [FactorOfSafety] FROM [SOURCEPROJECT].StabilityPointStructuresOutputEntity; -INSERT INTO StabilityPointStructuresSectionResultEntity( +INSERT INTO StabilityPointStructuresSectionResultEntity ( [StabilityPointStructuresSectionResultEntityId], [FailureMechanismSectionEntityId], [StabilityPointStructuresCalculationEntityId], @@ -1536,7 +1534,7 @@ LEFT JOIN [SOURCEPROJECT].ForeshoreProfileEntity USING (FailureMechanismEntityID) WHERE FailureMechanismType = 7 GROUP BY FailureMechanismEntityID; -INSERT INTO StochasticSoilModelEntity( +INSERT INTO StochasticSoilModelEntity ( [StochasticSoilModelEntityId], [FailureMechanismEntityId], [Name], @@ -1549,13 +1547,13 @@ [StochasticSoilModelSegmentPointXml], [Order] FROM [SOURCEPROJECT].StochasticSoilModelEntity; -INSERT INTO PipingStochasticSoilProfileEntity( +INSERT INTO PipingStochasticSoilProfileEntity ( [PipingStochasticSoilProfileEntityId], [PipingSoilProfileEntityId], [StochasticSoilModelEntityId], [Probability], [Order]) -SELECT +SELECT [StochasticSoilProfileEntityId], [SoilProfileEntityId], [StochasticSoilModelEntityId], @@ -1571,7 +1569,7 @@ [ReferenceLineIntersectionY], [PointsXml], [Order]) -SELECT +SELECT [SurfaceLineEntityId], [FailureMechanismEntityId], CASE @@ -1587,13 +1585,13 @@ FROM [SOURCEPROJECT].SurfaceLineEntity; INSERT INTO TechnicalInnovationSectionResultEntity SELECT * FROM [SOURCEPROJECT].TechnicalInnovationSectionResultEntity; INSERT INTO VersionEntity ( - [VersionId], - [Version], - [Timestamp], + [VersionId], + [Version], + [Timestamp], [FingerPrint]) SELECT [VersionId], - "17.2", - [Timestamp], + "17.2", + [Timestamp], [FingerPrint] FROM [SOURCEPROJECT].VersionEntity; INSERT INTO WaterPressureAsphaltCoverSectionResultEntity SELECT * FROM [SOURCEPROJECT].WaterPressureAsphaltCoverSectionResultEntity; @@ -1615,21 +1613,21 @@ Insert new data */ INSERT INTO MacroStabilityInwardsFailureMechanismMetaEntity ( - [FailureMechanismEntityId], + [FailureMechanismEntityId], [A]) SELECT FailureMechanismEntityId, 0.033 FROM FailureMechanismEntity WHERE FailureMechanismType = 2; -- Insert new groups INSERT INTO CalculationGroupEntity ( - [Name], - [Order]) + [Name], + [Order]) SELECT - "Berekeningen", - 0 -FROM FailureMechanismEntity -WHERE FailureMechanismType = 2; + "Berekeningen", + 0 + FROM FailureMechanismEntity + WHERE FailureMechanismType = 2; -- Create temp table to store the new calculation group ids CREATE TEMP TABLE TempCalculationGroupEntity @@ -1640,54 +1638,54 @@ ) WITHOUT ROWID; -- Store the new calculation group ids -INSERT INTO TempCalculationGroupEntity( - [CalculationGroupId], - [FailureMechanismId]) +INSERT INTO TempCalculationGroupEntity ( + [CalculationGroupId], + [FailureMechanismId]) SELECT - last_insert_rowid() - ( - SELECT COUNT() - FROM AssessmentSectionEntity ase - WHERE fme.AssessmentSectionEntityId >= ase.AssessmentSectionEntityId - ) + 1, - FailureMechanismEntityId -FROM FailureMechanismEntity fme -WHERE fme.FailureMechanismType = 2 -ORDER BY AssessmentSectionEntityId; + last_insert_rowid() - ( + SELECT COUNT() + FROM AssessmentSectionEntity ase + WHERE fme.AssessmentSectionEntityId >= ase.AssessmentSectionEntityId + ) + 1, + FailureMechanismEntityId + FROM FailureMechanismEntity fme + WHERE fme.FailureMechanismType = 2 + ORDER BY AssessmentSectionEntityId; -- Link groups to all Macro (2) failure mechanisms UPDATE FailureMechanismEntity -SET CalculationGroupEntityId = ( - SELECT CalculationGroupId - FROM TempCalculationGroupEntity - WHERE FailureMechanismId = FailureMechanismEntity.FailureMechanismEntityId -) -WHERE FailureMechanismType = 2; + SET CalculationGroupEntityId = ( + SELECT CalculationGroupId + FROM TempCalculationGroupEntity + WHERE FailureMechanismId = FailureMechanismEntity.FailureMechanismEntityId + ) + WHERE FailureMechanismType = 2; -- Cleanup DROP TABLE TempCalculationGroupEntity; /* -Write migration logging +Write migration logging */ ATTACH DATABASE "{1}" AS LOGDATABASE; -CREATE TABLE IF NOT EXISTS [LOGDATABASE].'MigrationLogEntity' +CREATE TABLE IF NOT EXISTS [LOGDATABASE].'MigrationLogEntity' ( - 'MigrationLogEntityId' INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, - 'FromVersion' VARCHAR(20) NOT NULL, - 'ToVersion' VARCHAR(20) NOT NULL, + 'MigrationLogEntityId' INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, + 'FromVersion' VARCHAR(20) NOT NULL, + 'ToVersion' VARCHAR(20) NOT NULL, 'LogMessage' TEXT NOT NULL ); -INSERT INTO [LOGDATABASE].MigrationLogEntity( +INSERT INTO [LOGDATABASE].MigrationLogEntity ( [FromVersion], [ToVersion], [LogMessage]) VALUES ("17.1", "17.2", "Gevolgen van de migratie van versie 17.1 naar versie 17.2:"); CREATE TEMP TABLE TempFailureMechanisms ( - 'FailureMechanismType' INTEGER NOT NULL, + 'FailureMechanismType' INTEGER NOT NULL, 'FailureMechanismName' VARCHAR(255) NOT NULL ); @@ -1710,7 +1708,7 @@ INSERT INTO TempFailureMechanisms VALUES (17, 'Sterkte en stabiliteit langsconstructies'); INSERT INTO TempFailureMechanisms VALUES (18, 'Technische innovaties'); -CREATE TEMP TABLE TempAssessmentSectionChanges +CREATE TEMP TABLE TempAssessmentSectionChanges ( [AssessmentSectionId], [AssessmentSectionName], @@ -1720,7 +1718,7 @@ INSERT INTO TempAssessmentSectionChanges SELECT - [AssessmentSectionEntityId], + [AssessmentSectionEntityId], [Name], 0, "De ondergrens is gelijk gesteld aan 1/" || CAST(ROUND(CAST(1.0 / [LowerLimitNorm] AS FLOAT)) AS INT) || @@ -1747,11 +1745,11 @@ UNION -SELECT - [AssessmentSectionEntityId], +SELECT + [AssessmentSectionEntityId], [Name], 2, - "De norm van het dijktraject is gelijk gesteld aan de " || + "De norm van het dijktraject is gelijk gesteld aan de " || CASE WHEN [NormativeNormType] IS 1 THEN "ondergrens" @@ -1768,7 +1766,7 @@ ); INSERT INTO TempAssessmentSectionFailureMechanism -SELECT +SELECT [AssessmentSectionEntityId], [Name], [FailureMechanismEntityId], @@ -1919,56 +1917,56 @@ WITH RECURSIVE FailureMechanismMessages ( - [FailureMechanismId], - [FailureMechanismName], - [AssessmentSectionId], - [AssessmentSectionName], + [FailureMechanismId], + [FailureMechanismName], + [AssessmentSectionId], + [AssessmentSectionName], [msg], [level] ) AS ( SELECT DISTINCT - [FailureMechanismId], - [FailureMechanismName], - [AssessmentSectionId], - [AssessmentSectionName], - NULL, + [FailureMechanismId], + [FailureMechanismName], + [AssessmentSectionId], + [AssessmentSectionName], + NULL, 1 FROM TempChanges UNION -SELECT - [FailureMechanismId], - NULL, - [AssessmentSectionId], - [AssessmentSectionName], - [msg], +SELECT + [FailureMechanismId], + NULL, + [AssessmentSectionId], + [AssessmentSectionName], + [msg], 2 FROM TempChanges WHERE TempChanges.[FailureMechanismId] IS [FailureMechanismId] ORDER BY [FailureMechanismId], [AssessmentSectionId] ), AssessmentSectionFailureMechanismMessages ( - [AssessmentSectionId], - [AssessmentSectionName], + [AssessmentSectionId], + [AssessmentSectionName], [IsAssessmentSectionHeader], - [FailureMechanismId], - [FailureMechanismName], - [msg], + [FailureMechanismId], + [FailureMechanismName], + [msg], [level], [Order] ) AS ( - SELECT DISTINCT - [AssessmentSectionId], - [AssessmentSectionName], + SELECT DISTINCT + [AssessmentSectionId], + [AssessmentSectionName], 1, - NULL, - NULL, - NULL, + NULL, + NULL, + NULL, 1, 0 - FROM - ( - SELECT + FROM + ( + SELECT [AssessmentSectionId], [AssessmentSectionName] FROM TempAssessmentSectionChanges @@ -1982,8 +1980,8 @@ UNION - SELECT - * + SELECT + * FROM ( SELECT @@ -2000,12 +1998,12 @@ UNION SELECT - [AssessmentSectionId], - NULL, + [AssessmentSectionId], + NULL, 0 AS [IsAssessmentSectionHeader], - fmm.[FailureMechanismId] AS [FailureMechanismId], - fmm.[FailureMechanismName], - [msg], + fmm.[FailureMechanismId] AS [FailureMechanismId], + fmm.[FailureMechanismName], + [msg], fmm.[level] AS [level], 1 AS [Order] FROM FailureMechanismMessages AS fmm @@ -2017,17 +2015,17 @@ "17.1", "17.2", CASE - WHEN [AssessmentSectionName] IS NOT NULL + WHEN [AssessmentSectionName] IS NOT NULL THEN - CASE WHEN [IsAssessmentSectionHeader] IS 1 - THEN "* Traject: '" || [AssessmentSectionName] || "'" + CASE WHEN [IsAssessmentSectionHeader] IS 1 + THEN "* Traject: '" || [AssessmentSectionName] || "'" ELSE " + " || [msg] END ELSE CASE - WHEN [FailureMechanismName] IS NOT NULL - THEN " + Toetsspoor: '" || [FailureMechanismName] || "'" + WHEN [FailureMechanismName] IS NOT NULL + THEN " + Toetsspoor: '" || [FailureMechanismName] || "'" ELSE " - " || [msg] END