我有一个大火鸟sql查询,它正在生成“关系/过程/视图的上下文太多。允许的最大值是255”-错误
我看过它,但看不到如何减少它,因此不再产生上述错误。
查询返回一年中每周的计数,如果没有,则返回0
周它应该包含一年中所有52/53周的正确计数或0。
当我只使用少于255个关系/过程/视图时,查询工作正常,但我需要找到减少查询的方法,以便在所有52/53周内都能得到相同的结果。
Select "Week"||''||"Year" as "Week", "Courses"
From (
select ' 02 Jan ' as "Week", (select count(Distinct crs.ID)from comp_Invoice IV
inner join Course CRS on IV.COD_COURSE=CRS.id
where extract(week from BOOKING_DATE) = 1
and BOOKING_DATE between :StartDate and :EndDate
and crs.c_type = 'OS'
and crs.name not like '%Cancel%'
and crs.name not like 'CERT'
and crs.name not like '%cancel%'
and crs.name not like '%CANCELl%'
and crs.name not like '%CANCELL%'
and crs.version not like 'CONSTRUC%'
and crs.version Like :CoursesTitle ) as "Courses",
extract(year from IV.BOOKING_DATE) as "Year",
01 as "Sorter"
from comp_Invoice IV
inner join Course CRS on IV.COD_COURSE=CRS.id
where
BOOKING_DATE between :StartDate and :EndDate
and crs.c_type = 'OS'
and crs.name not like '%Cancel%'
and crs.name not like 'CERT'
and crs.name not like '%cancel%'
and crs.name not like '%CANCELl%'
and crs.name not like '%CANCELL%'
and crs.version not like 'CONSTRUC%'
and crs.version Like :CoursesTitle
group by 3
UNION
select ' 09 Jan ' as "Week", (select count(Distinct crs.ID)from comp_Invoice IV
inner join Course CRS on IV.COD_COURSE=CRS.id
where extract(week from BOOKING_DATE) = 2
and BOOKING_DATE between :StartDate and :EndDate
and crs.c_type = 'OS'
and crs.name not like '%Cancel%'
and crs.name not like 'CERT'
and crs.name not like '%cancel%'
and crs.name not like '%CANCELl%'
and crs.name not like '%CANCELL%'
and crs.version not like 'CONSTRUC%'
and crs.version Like :CoursesTitle ) as "Courses",
extract(year from IV.BOOKING_DATE) as "Year",
02 as "Sorter"
from comp_Invoice IV
inner join Course CRS on IV.COD_COURSE=CRS.id
where
BOOKING_DATE between :StartDate and :EndDate
and crs.c_type = 'OS'
and crs.name not like '%Cancel%'
and crs.name not like 'CERT'
and crs.name not like '%cancel%'
and crs.name not like '%CANCELl%'
and crs.name not like '%CANCELL%'
and crs.version not like 'CONSTRUC%'
and crs.version Like :CoursesTitle
group by 3
UNION
select ' 16 Jan ' as "Week", (select count(Distinct crs.ID)from comp_Invoice IV
inner join Course CRS on IV.COD_COURSE=CRS.id
where extract(week from BOOKING_DATE) = 3
and BOOKING_DATE between :StartDate and :EndDate
and crs.c_type = 'OS'
and crs.name not like '%Cancel%'
and crs.name not like 'CERT'
and crs.name not like '%cancel%'
and crs.name not like '%CANCELl%'
and crs.name not like '%CANCELL%'
and crs.version not like 'CONSTRUC%'
and crs.version Like :CoursesTitle ) as "Courses",
extract(year from IV.BOOKING_DATE) as "Year",
03 as "Sorter"
from comp_Invoice IV
inner join Course CRS on IV.COD_COURSE=CRS.id
where
BOOKING_DATE between :StartDate and :EndDate
and crs.c_type = 'OS'
and crs.name not like '%Cancel%'
and crs.name not like 'CERT'
and crs.name not like '%cancel%'
and crs.name not like '%CANCELl%'
and crs.name not like '%CANCELL%'
and crs.version not like 'CONSTRUC%'
and crs.version Like :CoursesTitle
group by 3
--** Same code and union is repeated 52 times one per week of the year**--
)
Group by 1,2,3,4
Order By 3,4 ASC
如果有人对我如何减少查询并获得同样的结果有什么好主意,请发表