dbo.QER_PSQRiskIndexGetUpdate

1   create   procedure QER_PSQRiskIndexGetUpdate( @uid_dialogcolumn varchar(38) , @SQLProcedure nvarchar(max) output ) as begin  declare @targettable
2 nvarchar(32) declare @TargetColumn nvarchar(32) declare @UID_TargetTable varchar(38)  declare @IsValidClause bit = 0 select top 1 @targettable = t.TableName
3 , @TargetColumn = c.ColumnName , @UID_TargetTable = c.UID_DialogTable from dbo.DialogColumn c with (readpast) join dbo.DialogTable t with (readpast) on
4 c.UID_DialogTable = t.UID_DialogTable where c.UID_DialogColumn = @UID_DialogColumn declare @IsToReduce bit declare @ColumnReduceBase nvarchar(32) select
5 @isToReduce = 0 select @ColumnReduceBase = N'' if @TargetColumn = 'RiskIndexReduced' begin select @isToReduce = 1 select top 1 @ColumnReduceBase = columnname
6 from dbo.DialogColumn c where columnname in ('RiskIndex', 'RiskIndexCalculated') and c.UID_DialogTable = @UID_TargetTable end declare @erg nvarchar(max
7) declare @body1 nvarchar(max) declare @body2 nvarchar(max) declare @HilfsViewName Nvarchar(64) =  left(concat('TST_FTemporaryElement', reverse(dbo.QBM_FGICodeName
8('V', NEWID()) )), 30) if @isToReduce = 1 begin  select @body1 =N'
9insert into #DeltaQERRiskIndex (XObjectKey, NewValue)
10      select z.XObjectKey, 
11
12--update %TargetTable%
13--	set %TargetColumn% = case 
14--							when '
15 + @ColumnReduceBase + ' - y.SourceValue < 0.0 then 0.0
16--							when ' + @ColumnReduceBase + ' - y.SourceValue > 1.0 then 1.0
17--							else ' + @ColumnReduceBase
18 + ' - y.SourceValue 
19--						 end
20
21						case 
22							when ' + @ColumnReduceBase + ' - y.SourceValue < 0.0 then 0.0
23							when ' + @ColumnReduceBase
24 + ' - y.SourceValue > 1.0 then 1.0
25							else ' + @ColumnReduceBase + ' - y.SourceValue 
26						 end as Newvalue
27	from %TargetTable% z with (readpast) /* hier bei 438165 Zugriff auf Elemente aus QBMDBQueueCurrent */ 
28		join 
29	(
30	select x.ObjectKeyTarget , MAX(x.SourceValue) as SourceValue
31						 '
32 end else begin  select @body1 =N'
33insert into #DeltaQERRiskIndex (XObjectKey, NewValue)
34      select z.XObjectKey, y.SourceValue
35
36--update %TargetTable%
37--	set %TargetColumn% = y.SourceValue
38	from %TargetTable% z with (readpast) /* hier bei 438165 Zugriff auf Elemente aus QBMDBQueueCurrent */ 
39		join 
40	(
41	select x.ObjectKeyTarget , case 
42							   when sum(x.SourceValue) < 0.0 then 0.0
43							   when sum(x.SourceValue) > 1.0 then 1.0
44							   else sum(x.SourceValue)
45						   end as SourceValue 
46	'
47 end select @body1 = @body1 + N'
48	 from (
49			-- common fallback clause
50			select XObjectKey as ObjectKeyTarget, 0.0 as SourceValue
51				from %TargetTable%  with (readpast) /* hier bei 438165 Zugriff auf Elemente aus QBMDBQueueCurrent */ 
52			-- / common fallback clause
53'
54  if @isToReduce = 1 begin  select @body2 =N'
55			) as x
56		group by x.ObjectKeyTarget
57	) as y on z.XObjectKey = y.ObjectKeyTarget
58  where %TargetColumn% <> case 
59							when '
60 + @ColumnReduceBase + ' - y.SourceValue < 0.0 then 0.0
61							when ' + @ColumnReduceBase + ' - y.SourceValue > 1.0 then 1.0
62							else ' + @ColumnReduceBase
63 + ' - y.SourceValue 
64						 end
65		' end else begin  select @body2 =N'
66			) as x
67		group by x.ObjectKeyTarget
68	) as y on z.XObjectKey = y.ObjectKeyTarget
69  where z.%TargetColumn% <> y.SourceValue
70		'
71 end declare @KernelPattern nvarchar(max) select @KernelPattern = N'
72 			union all 
73 		/*
74		%DisplayValue% 		
75		*/
76		  select v.ObjectKeyTarget, %TypeOfCalculation%(v.SourceValue) * (%Gewicht%) / (%GewichtSumme%) as SourceValue
77		   from (
78--				  select '''' as ObjectKeyTarget, 0.0 as SourceValue	
79--					union all '
80  + N'