gonna' try make quick... query below.
select prioritydefid, milestonedefid, milestonename, contactname, iif(prioritydefid = 1, (select bonusdaysfh milestone_def (( milestonedefid = iif(milestonedefid = 5, 5, iif(milestonedefid = 6, 6, iif(milestonedefid = 7, 7))) )) ), iif(prioritydefid = 2, (select bonusdaysfm milestone_def (( milestonedefid = iif(milestonedefid = 5, 5, iif(milestonedefid = 6, 6, iif(milestonedefid = 7, 7))) )) ), iif(prioritydefid = 3, (select bonusdaysfl milestone_def (( milestonedefid = iif(milestonedefid = 5, 5, iif(milestonedefid = 6, 6, iif(milestonedefid = 7, 7))) )) ) ))) bonusdaysallotted, startdate, enddate getperformance (((milestonedefid) = 5 or (milestonedefid) = 6 or (milestonedefid) = 7)); i trying value of milestonedefid , reuse in subquery determine bonusdays column return. subquery wants return 3 rows results of passing each value of 5, 6 , 7. each row returned getperformance query, want take milestonedefid row , go subquery , pass milestonedefid return correct number of bonusdays.
i use union in query.
select a.prioritydefid, a.milestonedefid, a.milestonename, a.contactname, b.bonusdaysfh bonusdaysallotted, a.startdate, a.enddate getperformance a, milestone_def b ((a.milestonedefid=5) or (a.milestonedefid=6) or (a.milestonedefid=7)) , b.milestonedefid=a.milestonedefid , a.prioritydefid=1 union select a.prioritydefid, a.milestonedefid, a.milestonename, a.contactname, b.bonusdaysfm bonusdaysallotted, a.startdate, a.enddate getperformance a, milestone_def b ((a.milestonedefid=5) or (a.milestonedefid=6) or (a.milestonedefid=7)) , b.milestonedefid=a.milestonedefid , a.prioritydefid=2 union select a.prioritydefid, a.milestonedefid, a.milestonename, a.contactname, b.bonusdaysfl bonusdaysallotted, a.startdate, a.enddate getperformance a, milestone_def b ((a.milestonedefid=5) or (a.milestonedefid=6) or (a.milestonedefid=7)) , b.milestonedefid=a.milestonedefid , a.prioritydefid=3 sadly, make 3 queries, believe lack of iif's improve performance.
Comments
Post a Comment