October 23, 2002 at 9:30 am
I want to combine these 2 updates, if I am successful I will combine many other updates...
I tried lots of queries, none seem to work, any help please ?? What am I doing wrong.
Update mwebWorkCalcs
set mwebWorkCalcs.WorkCalcs_Actual_Hrs=(select isnull(sum(TASK.WorkCalcs_Actual_Hrs),0.0)
from MWEBWORK, MWEBWORKCALCS TASK
where TASK.WORKCALCS_WORK_ID = WORK_ID AND TASK.WORKCALCS_WORK_ENTITY_TYPE=4
AND (case mwebWorkCalcs.workCalcs_Work_Level when 3 then Work_Par3 when 4 then Work_Par4 when 5 then Work_Par5 when 6 then Work_Par6 when 7 then
Work_Par7 when 8 then Work_Par8 end)=mwebWorkCalcs.WorkCalcs_Work_ID)
WHERE WorkCalcs_Work_Entity_Type=3
Update mwebWorkCalcs
set WorkCalcs_Expenses= (select isnull(sum(TASK.WorkCalcs_Expenses),0.0)
from MWEBWORK, MWEBWORKCALCS TASK
where TASK.WORKCALCS_WORK_ID = WORK_ID AND TASK.WORKCALCS_WORK_ENTITY_TYPE=4
AND (case mwebWorkCalcs.workCalcs_Work_Level when 3 then Work_Par3 when 4 then Work_Par4 when 5 then Work_Par5 when 6 then Work_Par6 when 7 then
Work_Par7 when 8 then Work_Par8 end)=mwebWorkCalcs.WorkCalcs_Work_ID)
WHERE WorkCalcs_Work_Entity_Type=3
Here is how wrote a query to combine it
Update mwebWorkCalcs
set WorkCalcs_Actual_Hrs=SumTasks.Actual_Hrs,WorkCalcs_Expenses= SumTasks.Expenses
From (select TASK.WORKCALCS_WORK_ID, isnull(sum(TASK.WorkCalcs_Actual_Hrs),0.0) as Actual_Hrs,
isnull(sum(TASK.WorkCalcs_Expenses),0.0) as Expenses
from MWEBWORKCALCS TASK
where TASK.WORKCALCS_WORK_ENTITY_TYPE=4
Group by TASK.WORKCALCS_WORK_ID)
AS SumTasks, mwebWorkCalcs, mwebwork
WHERE WorkCalcs_Work_Entity_Type=3 and SumTasks.WORKCALCS_WORK_ID = WORK_ID
AND (case mwebWorkCalcs.workCalcs_Work_Level when 3 then Work_Par3 when 4 then Work_Par4 when 5 then Work_Par5
when 6 then Work_Par6 when 7 then Work_Par7 when 8 then Work_Par8 end)=mwebWorkCalcs.WorkCalcs_Work_ID
this does not give me correct values.
thanks
Sonali
October 23, 2002 at 9:40 am
this one does not work too
Update mwebWorkCalcs
set mwebWorkCalcs.WorkCalcs_Actual_Hrs=SumTasks.Actual_Hrs,WorkCalcs_Expenses= SumTasks.Expenses
From (select isnull(sum(TASK.WorkCalcs_Actual_Hrs),0.0) as Actual_Hrs,
isnull(sum(TASK.WorkCalcs_Expenses),0.0) as Expenses
from MWEBWORK, MWEBWORKCALCS TASK, mwebWorkCalcs Proj
where TASK.WORKCALCS_WORK_ID = WORK_ID AND TASK.WORKCALCS_WORK_ENTITY_TYPE=4
AND (case Proj.workCalcs_Work_Level when 3 then Work_Par3 when 4 then Work_Par4 when 5 then Work_Par5 when 6 then Work_Par6 when 7 then
Work_Par7 when 8 then Work_Par8 end)=Proj.WorkCalcs_Work_ID
and Proj.WorkCalcs_Work_Entity_Type=3) AS SumTasks
WHERE WorkCalcs_Work_Entity_Type=3
October 23, 2002 at 9:48 am
This should do it, alos changed to use INNER JOIN SQL standard.
Update mwebWorkCalcs
set mwebWorkCalcs.WorkCalcs_Actual_Hrs=(select isnull(sum(TASK.WorkCalcs_Actual_Hrs),0.0)
from
MWEBWOR
INNER JOIN
MWEBWORKCALCS TASK
ON
TASK.WORKCALCS_WORK_ID = WORK_ID AND TASK.WORKCALCS_WORK_ENTITY_TYPE=4
WHERE
(case mwebWorkCalcs.workCalcs_Work_Level
when 3 then Work_Par3
when 4 then Work_Par4
when 5 then Work_Par5
when 6 then Work_Par6
when 7 then Work_Par7
when 8 then Work_Par8
end)=mwebWorkCalcs.WorkCalcs_Work_ID)
from
MWEBWOR
INNER JOIN
MWEBWORKCALCS TASK
ON
TASK.WORKCALCS_WORK_ID = WORK_ID AND TASK.WORKCALCS_WORK_ENTITY_TYPE=4
WHERE
(case mwebWorkCalcs.workCalcs_Work_Level
when 3 then Work_Par3
when 4 then Work_Par4
when 5 then Work_Par5
when 6 then Work_Par6
when 7 then Work_Par7
when 8 then Work_Par8
end)=mwebWorkCalcs.WorkCalcs_Work_ID)
WHERE WorkCalcs_Work_Entity_Type=3
Unfortunately with the WHERE WorkCalcs_Work_Entity_Type=3 kinda muddies things up. Especially since your inner queries are looking for 4 on the same field. I am sure there is probably another way but I don't see right off. Will post if it comes to me.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
October 23, 2002 at 12:29 pm
no this doesn't work either, what I want to is combine 2 updates, they have same where clause... those work individually, but when I combine those, they don't
I want to do this
Update mwebWorkCalcs
set WorkCalcs_Actual_Hrs=SumTasks.Actual_Hrs,WorkCalcs_Expenses= SumTasks.Expenses
From (select isnull(sum(TASK.WorkCalcs_Actual_Hrs),0.0) as Actual_Hrs,
isnull(sum(TASK.WorkCalcs_Expenses),0.0) as Expenses
....
is this possible ?
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply