Re: Does ibatis support temp tables in sql maps?

[Brian Kierstead]

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 <[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#)&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>