Post reply

Join temporary table with another table

  • July 24, 2003 at 12:36 pm

    #101592

    Can someone please help me how to join temporary table with another table. I have one table in which I am doing all the joins and in temp. table I have the total count. I want to join two tables on people_id. This is what I am trying to achieve e.g. left join #temptbl t1 on p.people_id = t1.people_id.

    Getting an error message:Invalid object name '#temptabl'.

    Thanks

  • July 24, 2003 at 12:44 pm

    #466907

    Are you try to refer temp table created by stored procedure or another session?

    Check the sysobjects in TEMPDB to see whether this temp table exists.

  • July 24, 2003 at 1:12 pm

    #466918

    I am refering temp table from store procedure.

    I got 0 records back when I ran the query

    for sysobjects.

    Below is the temp table I am creating:

    CREATE PROCEDURE sp_temp_NumofEvents AS

    Create table #NumofEvents

    (

    people_id numeric,

    site_name varchar(100),

    NumofEvents numeric

    )

    Insert into #NumofEvents

    select distinct p.people_id,

    st.site_name,

    (select distinct count(event.event_date))as NumofEvents from event

    left outer join authorizations auth ON auth.authorizations_id =event.authorizations_fk

    left outer join service svc ON auth.authorizations_id = svc.authorizations_fk

    left outer join call cl ON cl.call_id = svc.call_fk

    left outer join episode ep ON ep.episode_id = svc.episode_fk

    left outer join cases c ON c.cases_id = ep.cases_fk

    left outer Join people p On p.people_id = c.people_fk

    left outer join v_event_type et ON et.v_et_id = event.event_type_fk

    left outer join site st ON st.site_id = p.site_fk

    where

    st.site_name like 'pfizer%' and ep.closure_date between '04-01-2003'

    and '06-30-2003' and c.status_fk='2' and p.deactivate is null and st.deactivate is null and

    c.deactivate is null and svc.deactivate is null

    and ep.deactivate is null and et.v_et_id in (6,7)

    /*and cltyp.name= 'Clinical' */

    group by

    p.people_id,

    st.site_name

    order by st.site_name, p.people_id

    Select * from #NumofEvents

  • July 24, 2003 at 1:15 pm

    #466920

    I re-ran the temp table and now I am getting 1 record back.

    Thanks!

  • July 25, 2003 at 10:26 am

    #467117

    I've finally figured it out. Thanks for the help!

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply