view creation using temporary table

[bjlittler]

I am trying to create a view using a global temporary table Odyssey.HMSg_ReportGeneral :

create or replace view Odyssey.av_hms_TmpTbl_rpt_ClsGndr4dtsSub1
as select nvl(p.Gender,'Either') gender, bldg.Building Bldg, bldg.Site_Name Parent1, bldg.Room_Name name,
bldg.Building_Name Parent2, bldg.facility_sk_fk facility_sk, nvl(pc.Class,'Unspecified') Class, p.patron_sk,
count(t1.Patron_SK_FK) NumOccupants, nvl(pc.listing_order,0) ListOrd
from Odyssey.HMSg_ReportGeneral g,Odyssey.av_hms_rpt_ThisAssignmentLevel fa3
join Odyssey.av_hms_rpt_FacilityStructure bldg on(bldg.facility_sk_fk = fa3.facility_sk_FK and
(bldg.effectivedate is null or(bldg.effectivedate <= g.Date2 and(bldg.enddate is null or
bldg.enddate >= g.Date2)))) left outer join Odyssey.av_hms_rpt_NumOccupantsNoCount t1
on(t1.Facility_SK = bldg.facility_sk_fk and
(t1.elementstart is null or(t1.elementstart <= g.date2 and(t1.elementend is null or
t1.elementend >= g.date2)))) left outer join Odyssey.csdp_patron p
on(p.patron_SK = t1.patron_SK_FK) left outer join Odyssey.av_hms_rpt_PatronClass pc
on(pc.Patron_SK_FK = t1.Patron_SK_FK and(pc.effectivedate is null or (pc.effectivedate <=
g.date2 and(pc.enddate is null or pc.enddate >= g.date2))))
where fa3.effectivedate is null or(fa3.effectivedate <= g.Date1 and(fa3.enddate is null or fa3.enddate >= g.Date1))
group by bldg.Site_Name, bldg.Building_Name,bldg.Building, pc.Class, pc.listing_order, p.patron_sk, p.Gender,
bldg.facility_sk_fk, bldg.Room_Name
;
I get an error :
ORA-00904: "G"."DATE2": invalid identifier
If I removed all references to the global temporary table,
the view compiles fine - references to any of the columns in the temporary table give the ORA-00904. We are using Oracle 9iR2 Standard Edition on Win2003 Standard server
Any suggestions ?
(FYI we are converting a database from Sybase to Oracle and have almost 20 views like this)
Thanks for the help

[slimdave]

Not directly answering your question here, but are you sure that you need the temporary table? Excessive use of temporary tables is a common feature in sybase/SQL server -to-Oracle conversions, because the legacy db is not as good at complex multi-table queries as Oracle.

A better solution might be to encapsulate the query with which you populate the temporary table as a view, and then reference that in the other view definitions.