ibatis-user-cs mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Brian Kierstead <br...@fluidmedia.com>
Subject Re: Does ibatis support temp tables in sql maps?
Date Mon, 30 Jan 2006 21:41:35 GMT

I guess.. why not?  I can put this in the DB and use it that way, but 
its the only one that  would go there so it would be nice to keep them 
all in one place.  The first select (into @count) works without any 
problems, but when I added in temp tables it stopped working.  I can 
rewrite it to lose one of the temp tables, but the other I'm not sure I 
can get rid of easily.

Thanks,
Brian


Larry Meadors wrote:

>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