Reputation: 1850
I have this in a stored procedure:
select * into #temp_UNION from
(
SELECT 6 AS InteractionType, * FROM history.GetHistoryRewardPointsForIncentiveOption (588,1,6)
UNION all
SELECT 8 AS InteractionType, * FROM history.GetHistoryRewardPointsForIncentiveOption (558,1,8)
) a
which gives:
interaction type historyid incentiveprogramid points
6 1 1 50
6 1 4 50
6 1 5 50
8 1 3 100
8 1 4 100
then i have:
select tu.InteractionType,ipc.Name,tu.Points from #temp_UNION tu
inner join Incentive.IncentiveProgramCultures ipc
on tu.IncentiveProgramId = ipc.IncentivePrograms_IncentiveProgramId
inner join Zinc.Users zu
on zu.Cultures_DefaultCultureId = ipc.IncentiveProgramCultureId
where zu.UserId = 588
6 India - Q2 Incentive 50
8 India - Q2 Incentive 100
now i need to make up HintText which is a field in my #CategoriesTable(previously defined) by using the name i got from above and the points
UPDATE #CategoriesTable
SET HintText = CASE WHEN HasAssessment = 1
THEN 'Program ' + tu.name + ' will earn you ' + tu.points
ELSE 'With No Assessment'
END
but i get an error on tu.Name: multi part identifier could not be bound? how can i achieve? should i make use of another temp table with the 2 rows in it?
here new code:
select * into #temp_UNION from
(
SELECT 6 AS InteractionType, * FROM history.GetHistoryRewardPointsForIncentiveOption (588,1,6)
UNION all
SELECT 8 AS InteractionType, * FROM history.GetHistoryRewardPointsForIncentiveOption (558,1,8)
) a
select tu.InteractionType,ipc.Name,tu.Points,zu.UserId into #temp1 from #temp_UNION tu
inner join Incentive.IncentiveProgramCultures ipc
on tu.IncentiveProgramId = ipc.IncentivePrograms_IncentiveProgramId
inner join Zinc.Users zu
on zu.Cultures_DefaultCultureId = ipc.IncentiveProgramCultureId
where zu.UserId = 588
Select * from #temp1 t
UPDATE #CategoriesTable
SET HintText = CASE WHEN HasAssessment = 1
THEN 'Program ' + t.Name + ' and points = ' + t.Points
ELSE 'With No Assessment'
END
FROM #temp1 t
WHERE t.userId = #CategoriesTable.UserId
DROP TABLE #temp_UNION
DROP TABLE #temp1
SELECT *
FROM #CategoriesTable
im not getting the #CategoriesTable?
Upvotes: 0
Views: 6241
Reputation: 465
You must specify the "tu" alias in the update clause
UPDATE #CategoriesTable
SET HintText = CASE WHEN HasAssessment = 1
THEN 'Program ' + tu.name + ' will earn you ' + tu.points
ELSE 'With No Assessment'
END
FROM #temp_UNION tu
WHERE tu.? = #CategoriesTable.? --JOIN condition
Upvotes: 1