Reuse query value in the same query

Question

Gonna' try to make this quick... Query below.

SELECT PriorityDefID, MilestoneDefID, MilestoneName, ContactName, 
IIF(PriorityDefID = 1, (SELECT BonusDaysFH FROM milestone_def WHERE (( MilestoneDefID = IIF(MilestoneDefID = 5, 5, IIF(MilestoneDefID = 6, 6, IIF(MilestoneDefID = 7, 7))) )) ), 
IIF(PriorityDefID = 2, (SELECT BonusDaysFM FROM milestone_def WHERE (( MilestoneDefID = IIF(MilestoneDefID = 5, 5, IIF(MilestoneDefID = 6, 6, IIF(MilestoneDefID = 7, 7))) )) ),
IIF(PriorityDefID = 3, (SELECT BonusDaysFL FROM milestone_def WHERE (( MilestoneDefID = IIF(MilestoneDefID = 5, 5, IIF(MilestoneDefID = 6, 6, IIF(MilestoneDefID = 7, 7))) )) ) ))) AS BonusDaysAllotted,
StartDate, EndDate
FROM GetPerformance
WHERE (((MilestoneDefID) = 5 Or (MilestoneDefID) = 6 Or (MilestoneDefID) = 7));

I am ultimately trying to get the value of the MilestoneDefID and reuse it in the subquery to determine which BonusDays column to return. The subquery wants to return three rows with the results of passing each value of 5, 6 and 7. For each row returned from the GetPerformance query, I want it to take the MilestoneDefID from that row and then go into the subquery and pass that MilestoneDefID to return the correct number of BonusDays.

Vesper · Accepted Answer · 2013-07-19 15:27:01Z

I say use union in this query.

select a.PriorityDefID, a.MilestoneDefID, a.MilestoneName, a.ContactName,
    b.BonusDaysFH as BonusDaysAllotted, a.StartDate, a.EndDate
from GetPerformance a, milestone_def b
where ((a.MilestoneDefID=5) or (a.MilestoneDefID=6) or (a.MilestoneDefID=7))
    and b.MilestoneDefID=a.MilestoneDefID
    and a.PriorityDefID=1
union
select a.PriorityDefID, a.MilestoneDefID, a.MilestoneName, a.ContactName,
    b.BonusDaysFM as BonusDaysAllotted, a.StartDate, a.EndDate
from GetPerformance a, milestone_def b
where ((a.MilestoneDefID=5) or (a.MilestoneDefID=6) or (a.MilestoneDefID=7))
    and b.MilestoneDefID=a.MilestoneDefID
    and a.PriorityDefID=2
union
select a.PriorityDefID, a.MilestoneDefID, a.MilestoneName, a.ContactName,
    b.BonusDaysFL as BonusDaysAllotted, a.StartDate, a.EndDate
from GetPerformance a, milestone_def b
where ((a.MilestoneDefID=5) or (a.MilestoneDefID=6) or (a.MilestoneDefID=7))
    and b.MilestoneDefID=a.MilestoneDefID
    and a.PriorityDefID=3

Sadly, this will make three queries, but I believe the lack of Iif's will improve performance.

Collectives™ on Stack Overflow

Reuse query value in the same query

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related