No wonder you want to turn that query into a stored procedure. That's about the most complicated query I've seen!
Rick > -----Original Message----- > From: erik tom [mailto:[EMAIL PROTECTED] > Sent: Tuesday, March 25, 2008 12:36 PM > To: CF-Talk > Subject: turn the coldfusion query into sql stored procedure or view > > I have a cooldfusion query that i want to turn into the stored procedure or > view . How can I > do that > > > <cfquery name="getReport" datasource="cfelsunrise" dbtype="ODBC"> > Select e.employeeid, e.last_name as empLastName, e.first_name > as empFirstName, > e.middle_name as empMiddleName, > tr.TrainingID, tr.courseID, tr.StdTimeFrame, > .et.completiondate, > er.last_name as erLastName, er.first_name as > erFirstName, > er.middle_name as erMiddleName, er.EmployeeID as erEmployeeiD, > ea.last_name as eaLastName, ea.first_name as > eaFirstName, > ea.middle_name as eaMiddleName, ea.EmployeeID as eaEmployeeiD, > ea.middle_name as eaMiddleName, l.name, > l.locationID, l.state, > s.stateid, a.areaID, r.regionID, e.hireDate > from employee e > join employeejobhistory h on > h.employeeid = e.employeeid > inner join jobassignment ja on (ja.sequence > = h.sequence and > ja.emplrcd = 0 and ja.enddate is NULL) > inner join jobcode_list j on > j.jobcode = h.jobcode > inner join location l on > l.locationid = ja.locationid > Inner join States s > on l.state = s.StateCode > inner join JobTrainLink jtl on > (jtl.JobCode = h.JobCode and > jtl.DeptID = h.DeptID and jtl.TrainingID is not NULL) > inner Join TrainingReqmt tr ON > (tr.TrainingID = jtl.TrainingID > and tr.courseID in(#crslist#)) > left outer Join TrainReqState trs ON > (trs.TrainReqID = tr.TrainReqID and > trs.StateID = s.StateID) > left outer join employeeTraining et on (e.employeeid = > et.employeeid and > et.courseID = tr.CourseID and et.Errflag is NULL) > left outer join area a on > a.areaid = l.areaid > left outer join employee ea on > ea.employeeid = a.managerEmpID > left outer join region r on > r.regionid = a.regionid > left outer join employee er on > er.employeeid = r.managerEmpID > where e.terminationdate is NULL > and DATEDIFF(dy, #cutoffdate#, e.HireDate) >= 0 > and l.excludeflag != 'Y' > <cfif selregionalVP gt 0>and r.ManagerEmpID = > #selregionalVP#</cfif> > <cfif selareaManager gt 0>and a.ManagerEmpID = #selareaManager#</cfif> > <cfif community gt 0>and l.LocationID = > #community#</cfif> > order by er.last_name, er.first_name, ea.last_name, > ea.first_name, l.name, > l.locationID, > e.last_name, e.first_name, > e.middle_name, e.employeeID, > tr.courseID, et.completiondate > </cfquery> > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:302023 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
