What the heck..?? Why are you trying to write a stored procedure in a sql map?
Larry On 1/30/06, Brian Kierstead <[EMAIL PROTECTED]> wrote: > Can I use temp tables in my queries in sql maps? I think that ibatis is > confused by the # used to mark them as temp tables - is there are work > around for this? > > Thanks for any help, > Brian > > Here is an example of what I'm trying to do: > > > <select id="GetCandidatesByJobMatchCriteria" > resultMap="CandidateListResult" parameterClass="Hashtable"> > SET NOCOUNT ON > > -- get the number of keywords for the job > DECLARE @count int > SELECT @count = count(keywordId) from tblJobKeywords WHERE > jobid=#jobId# > > -- get the list of top level keywords for the job > SELECT > jk.KeywordId > INTO > #tmpTopLevelJobKeywords > FROM > tblJobKeywords jk > INNER JOIN > tblKeywords k > ON jk.KeywordId = k.Keyword_Id > > INNER JOIN > tblCategories g > ON k.Keyword_CategoryId = g.Category_Id > AND g.TopLevelMatch = 1 > > WHERE > jk.JobID = #jobId# > > -- get the list of profiles which have any matching keywords > SELECT > cp.CandidateId, cp.Id, cp.EmploymentSoughtId, > round(count(jk.jobId)*100/@count,2) as PercentRank > INTO > #tmpMatchingProfiles > > FROM > tblJobKeywords jk > > inner join > tblCandidateKeywords ck > on jk.keywordid = ck.keywordid > > inner join > tblCandidateProfiles cp > on cp.Id = ck.CandidateProfileId > WHERE > JobID = #jobId# > > GROUP BY > cp.CandidateId, cp.id, cp.EmploymentSoughtId > > > -- determine which of the matching profiles have a top level > match > SELECT > c.Id, c.Email, c.Fullname, c.City, c.Phone, c.Active, > c.Authorized > ,et.Name as EmploymentSoughtName, m.PercentRank > FROM( > SELECT DISTINCT > mp.CandidateId, mp.PercentRank, mp.EmploymentSoughtId > FROM > #tmpMatchingProfiles mp > > INNER JOIN > tblCandidateKeywords ck > ON ck.CandidateProfileId = mp.Id > AND ck.KeywordId in (SELECT * FROM #tmpTopLevelJobKeywords)) > as m > > inner join > tblCandidates c > on m.CandidateId = c.Id > > <dynamic prepend="AND"> > <isNotNull property="registrationDate" > > > DateDiff(dd,c.RegistrationDate,#registrationDate#)<=0 > </isNotNull> > </dynamic> > > INNER JOIN > tblEmploymentTypes et > ON m.EmploymentSoughtId = et.Id > > <dynamic> > <isNull property="allAgencies"> > INNER JOIN > tblJobs j > ON j.Id = #jobId# > INNER JOIN > tblEmployers e > ON j.EmployerId = e.Id > INNER JOIN > tblCounsellors csJob > ON csJob.Id = e.CounsellorId > INNER JOIN > tblCounsellors csCandidate > ON c.CounsellorId = csCandidate.Id > > WHERE > csJob.AgencyId = csCandidate.AgencyId > </isNull> > </dynamic> > > ORDER BY > m.PercentRank > > drop table #tmpTopLevelJobKeywords > drop table #tmpMatchingProfiles > </select> >
