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>
|
- Does ibatis support temp tables in sql maps? Brian Kierstead
-
