ibatis-user-cs mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Larry Meadors <lmead...@apache.org>
Subject Re: Does ibatis support temp tables in sql maps?
Date Mon, 30 Jan 2006 21:00:44 GMT
What the heck..??

Why are you trying to write a stored procedure in a sql map?

Larry


On 1/30/06, Brian Kierstead <brian@fluidmedia.com> 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#)&lt;=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>
>

Mime
View raw message