Firebird sql - слишком много контекста отношений / процедур / представлений. Максимально допустимое значение 255 - ошибка

Question

Firebird sql - слишком много контекста отношений / процедур / представлений. Максимально допустимое значение 255 - ошибка

У меня большой запрос Firebird 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

Если у кого-то есть какие-нибудь блестящие идеи о том, как я могу уменьшить запрос и получить тот же результат, пожалуйста, опубликуйте его. С уважением, Dpp

1

sql firebird qsqlquery

Источник

user1190346 29 июл '14 в 22:46

2 ответа

Решение

Вы можете использовать объединение с 0 значениями, например так:

select
 'Week # ' || extract(week from GTT.DT) ||
       ' (' || extract(day from GTT.DT) ||
       ' ' || case extract(month from GTT.DT)
                when 1 then 'Jan'
                when 2 then 'Feb'
                when 3 then 'Mar'
                when 4 then 'Apr'
                when 5 then 'May'
                when 6 then 'Jun'
                when 7 then 'Jul'
                when 8 then 'Aug'
                when 9 then 'Sep'
                when 10 then 'Okt'
                when 11 then 'Nov'
                when 12 then 'Dec'
              end || ')' as "Week",
  extract(year from GTT.DT) as "Year", 0 as "Courses", 
  extract(week from GTT.DT) as "Sorter"
from
  (
   with recursive DATES as
   (select
      cast(:STARTDATE as date) as DT
    from
      RDB$DATABASE

    union all

    select
      dateadd(day, 1, DT)
    from
      DATES
    where
      DT < cast(:ENDDATE as date)
   )
select
  DT
from
  DATES
) GTT

0

Источник

user3898733 03 авг '14 в 07:27

Другие вопросы по тегам sql firebird qsqlquery

user1190346 30 июл '14 в 03:38 2014-07-30 03:38 · Accepted Answer · 2014-07-30 03:38

Спасибо за совет. Я разобрался, вот новый запрос.

`Select "Week", "Year", SUM("Courses") as "Courses", "Sorter"
FROM (
select 
case extract(week from BOOKING_DATE) 
when 1 then 'Week # 1 (2 Jan) ' 
when 2 then 'Week # 2 (09 Jan) ' 
when 3 then 'Week # 3 (16 Jan) ' 
when 4 then 'Week # 4 (23 Jan)'
when 5 then 'Week # 5 (30 Jan)' 
when 6 then 'Week # 6 (06 Feb)' 
when 7 then 'Week # 7 (13 Feb)' 
when 8 then 'Week # 8 (20 Feb)' 
when 9 then 'Week # 9 (27 Feb)' 
when 10 then 'Week # 10 (06 March)' 
when 11 then 'Week # 11 (13 March)' 
when 12 then 'Week # 12 (20 March)'
when 13 then 'Week # 13 (27 March)'
when 14 then 'Week # 14 (03 April)'
when 15 then 'Week # 15 (10 April)'
when 16 then 'Week # 16 (17 April)'
when 17 then 'Week # 17 (24 April)'
when 18 then 'Week # 18 (01 May)'
when 19 then 'Week # 19 (08 May)'
when 20 then 'Week # 20 (15 May)'
when 21 then 'Week # 21 (22 May)'
when 22 then 'Week # 22 (29 May)'
when 23 then 'Week # 23 (05 Jun)'
when 24 then 'Week # 24 (12 Jun)'
when 25 then 'Week # 25 (19 Jun)'
when 26 then 'Week # 26 (26 Jun)'
when 27 then 'Week # 27 (03 July)'
when 28 then 'Week # 28 (10 July)'
when 29 then 'Week # 29 (17 July)'
when 30 then 'Week # 30 (24 July)'
when 31 then 'Week # 31 (31 July)'
when 32 then 'Week # 32 (07 Aug)'
when 33 then 'Week # 33 (14 Aug)'
when 34 then 'Week # 34 (21 Aug)'
when 35 then 'Week # 35 (28 Aug)'
when 36 then 'Week # 36 (04 Sep)'
when 37 then 'Week # 37 (11 Sep)'
when 38 then 'Week # 38 (18 Sep)'
when 39 then 'Week # 39 (25 Sep)'
when 40 then 'Week # 40 (02 Oct)'
when 41 then 'Week # 41 (09 Oct)'
when 42 then 'Week # 42 (16 Oct)'
when 43 then 'Week # 43 (23 Oct)'
when 44 then 'Week # 44 (30 Oct)'
when 45 then 'Week # 45 (06 Nov)'
when 46 then 'Week # 46 (13 Nov)'
when 47 then 'Week # 47 (20 Nov)'
when 48 then 'Week # 48 (27 Nov)'
when 49 then 'Week # 49 (04 Dec)'
when 50 then 'Week # 50 (11 Dec)'
when 51 then 'Week # 51 (18 Dec)'
when 52 then 'Week # 52 (25 Dec)'

end  as "Week" , 
extract(year from BOOKING_DATE) as "Year" ,
coalesce(count(Distinct crs.ID),0) as "Courses", extract(Week from BOOKING_DATE) 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 :CourseTitle
Group by 1,2,4

UNION 

select 
case extract(week from BOOKING_DATE) 
when 1 then 'Week # 1 (2 Jan) ' 
when 2 then 'Week # 2 (09 Jan) ' 
when 3 then 'Week # 3 (16 Jan) ' 
when 4 then 'Week # 4 (23 Jan)' 
when 5 then 'Week # 5 (30 Jan)' 
when 6 then 'Week # 6 (06 Feb)' 
when 7 then 'Week # 7 (13 Feb)' 
when 8 then 'Week # 8 (20 Feb)' 
when 9 then 'Week # 9 (27 Feb)' 
when 10 then 'Week # 10 (06 March)' 
when 11 then 'Week # 11 (13 March)' 
when 12 then 'Week # 12 (20 March)'
when 13 then 'Week # 13 (27 March)'
when 14 then 'Week # 14 (03 April)'
when 15 then 'Week # 15 (10 April)'
when 16 then 'Week # 16 (17 April)'
when 17 then 'Week # 17 (24 April)'
when 18 then 'Week # 18 (01 May)'
when 19 then 'Week # 19 (08 May)'
when 20 then 'Week # 20 (15 May)'
when 21 then 'Week # 21 (22 May)'
when 22 then 'Week # 22 (29 May)'
when 23 then 'Week # 23 (05 Jun)'
when 24 then 'Week # 24 (12 Jun)'
when 25 then 'Week # 25 (19 Jun)'
when 26 then 'Week # 26 (26 Jun)'
when 27 then 'Week # 27 (03 July)'
when 28 then 'Week # 28 (10 July)'
when 29 then 'Week # 29 (17 July)'
when 30 then 'Week # 30 (24 July)'
when 31 then 'Week # 31 (31 July)'
when 32 then 'Week # 32 (07 Aug)'
when 33 then 'Week # 33 (14 Aug)'
when 34 then 'Week # 34 (21 Aug)'
when 35 then 'Week # 35 (28 Aug)'
when 36 then 'Week # 36 (04 Sep)'
when 37 then 'Week # 37 (11 Sep)'
when 38 then 'Week # 38 (18 Sep)'
when 39 then 'Week # 39 (25 Sep)'
when 40 then 'Week # 40 (02 Oct)'
when 41 then 'Week # 41 (09 Oct)'
when 42 then 'Week # 42 (16 Oct)'
when 43 then 'Week # 43 (23 Oct)'
when 44 then 'Week # 44 (30 Oct)'
when 45 then 'Week # 45 (06 Nov)'
when 46 then 'Week # 46 (13 Nov)'
when 47 then 'Week # 47 (20 Nov)'
when 48 then 'Week # 48 (27 Nov)'
when 49 then 'Week # 49 (04 Dec)'
when 50 then 'Week # 50 (11 Dec)'
when 51 then 'Week # 51 (18 Dec)'
when 52 then 'Week # 52 (25 Dec)'
end as "Week",
extract(year from BOOKING_DATE) as "Year" ,
0 as "Courses",
extract(Week from BOOKING_DATE) as "Sorter"
from comp_Invoice 
where 
BOOKING_DATE between :StartDate and :EndDate 

)
GROUP BY 1,2,4
Order by 2,4 ASC'