Error
'The multi-part identifier "ar_update.Id" could not be bound.'
as line denoted in sql script.
I also tried other columns for the errant join to try to prompt different errors and gives same error results.
Why/How to qualify the 'inner' processing set back to the 'update' set?
update ar_update
set ar_update.[GLEntry_Amount] = ar_sum.[GLEntry_Amount_sum]
from [dbo].[ASL_Result_VGO] as ar_update
join (select ar.[Id] as [Id]
,ar.[Company Id] as [Company Id]
,ar.[Schedule Name] as [Schedule Name]
,ar.[Line No_] as [Line No_]
,ar.[Row No_] as [Row No_]
,ar.[Posting Date] as [Posting Date]
,ar.[Business Centre Code] as [Business Centre Code]
,ar.[Site Locations Code] as [Site Locations Code]
,GLEntry_Amount_sum =
(select sum(are.[GLEntry_Amount])
from [dbo].[ASL_Result_VGO] as are
where are.[Company Id] = ar.[Company Id] and
are.[Schedule Name] = ar.[Schedule Name] and
are.[Posting Date] = ar.[Posting Date] and
are.[Business Centre Code] = ar.[Business Centre Code] and
are.[Site Locations Code] = ar.[Site Locations Code] and
are.[Row No_] in ( select * from dbo.udf_range_expand_VGO(ar.[Totaling]) )
)
from [dbo].[ASL_Result_VGO] as ar
where ar.[Id] = ar_update.[Id] -- The multi-part identifier "ar_update.Id" could not be bound.
) ar_sum on ar_update.[Id] = ar_sum.[Id]
where ar_update.[Totaling Type] = 2 and -- formulas
len(trim(ar_update.[Totaling])) > 0 and
charindex('..', ar_update.[Totaling]) > 0 -- P ranges
this happened because on where ar.[Id] = ar_update.[Id] because ar_update.[Id] is not a value it is a multivalued type, it's like you were comparing a list with an integer, try this
update ar_update
set ar_update.[GLEntry_Amount] = ar_sum.[GLEntry_Amount_sum]
from [dbo].[ASL_Result_VGO] as ar_update
join (select ar.[Id] as [Id]
,ar.[Company Id] as [Company Id]
,ar.[Schedule Name] as [Schedule Name]
,ar.[Line No_] as [Line No_]
,ar.[Row No_] as [Row No_]
,ar.[Posting Date] as [Posting Date]
,ar.[Business Centre Code] as [Business Centre Code]
,ar.[Site Locations Code] as [Site Locations Code]
,GLEntry_Amount_sum = (select sum(are.[GLEntry_Amount])
from [dbo].[ASL_Result_VGO] as are
where are.[Company Id] = ar.[Company Id] and
are.[Schedule Name] = ar.[Schedule Name] and
are.[Posting Date] = ar.[Posting Date] and
are.[Business Centre Code] = ar.[Business Centre Code] and
are.[Site Locations Code] = ar.[Site Locations Code] and
are.[Row No_] in ( select * from dbo.udf_range_expand_VGO(ar.[Totaling]) )
)
from [dbo].[ASL_Result_VGO] as ar
) ar_sum on ar_update.[Id] = ar_sum.[Id]
where ar_update.[Totaling Type] = 2 and -- formulas
len(trim(ar_update.[Totaling])) > 0 and
charindex('..', ar_update.[Totaling]) > 0