代码之家  ›  专栏  ›  技术社区  ›  Derek

Firebird sql-关系/过程/视图的上下文太多。允许的最大值为255-错误

  •  1
  • Derek  · 技术社区  · 11 年前

    我有一个大火鸟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
    

    如果有人对我如何减少查询并获得同样的结果有什么好主意,请发表

    2 回复  |  直到 11 年前
        1
  •  1
  •   Derek    11 年前

    谢谢你的提示。我在这里找到了新的查询。

    `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'
    
        2
  •  0
  •   CsZsombor    10 年前

    可以使用具有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