Spool (子查询/With子句问题)

Question

你能帮帮我吗？

当我在蟾蜍中执行下面的.sql文件时，它会给出预期的结果。

 with 
    a as(select extract(year from sysdate)var_year,to_char(sysdate,'mm-dd')var_day from dual),
    b as(select case when var_day between '10-01'and '12-31'
    then to_date(var_year||'-10-01','yyyy-mm-dd')
    else to_date(var_year-1||'-10-01','yyyy-mm-dd')end d1,
    case when var_day between'10-01'and'12-31'
    then to_date(var_year+1||'-09-30','yyyy-mm-dd')
    else to_date(var_year||'-09-30','yyyy-mm-dd')end d2
    from a)
    select * from b,SCHEMA.TABLE1
    where SCHEMA.TABLE1.DATE_FRAIS between b.d1 and b.d2;

但是，当我尝试使用添加假脱机函数的.cmd作业启动它时，控制台将打开，但什么也不会发生，控制台将保持打开状态。.csv文件是生成的，但里面没有任何内容。

请发现下面的脚本不起作用(假脱机成功，但没有内容)：

SET FEEDBACK OFF
set heading on
SET PAGESIZE 0
SET LINESIZE 8000
set pagesize 50000
SET COLSEP ";"
COLUMN dcol new_value mydate noprint
select to_char(sysdate,'YYYY_MM_DD') dcol from dual;
SPOOL test.csv;
with 
a as(select extract(year from sysdate)var_year,to_char(sysdate,'mm-dd')var_day from dual),
b as(select case when var_day between '10-01'and '12-31'
then to_date(var_year||'-10-01','yyyy-mm-dd')
else to_date(var_year-1||'-10-01','yyyy-mm-dd')end d1,
case when var_day between'10-01'and'12-31'
then to_date(var_year+1||'-09-30','yyyy-mm-dd')
else to_date(var_year||'-09-30','yyyy-mm-dd')end d2
from a)
select * from b,SCHEMA.TABLE1
where SCHEMA.TABLE1.DATE_FRAIS between b.d1 and b.d2;
SPOOL OFF

然而，当我通过一个添加假脱机函数的.SQL作业启动一个“简单”的.cmd文件时，它可以工作(可能是因为我删除了"CASE“?).The .csv文件，并且里面有内容。

请在下面找到脚本工作的示例(假脱机内容继承)：

    SET FEEDBACK OFF
set heading on
SET PAGESIZE 0
SET LINESIZE 8000
set pagesize 50000
SET COLSEP ";"
COLUMN dcol new_value mydate noprint
select to_char(sysdate,'YYYY_MM_DD') dcol from dual;
SPOOL test.csv;
with
a as (select extract(year from sysdate) var_year1, extract(year from sysdate) var_year2, to_char(sysdate) var_day from dual)
select * from a;
SPOOL OFF

Answer 1

我怀疑(a)您已经以sqlplus -s user/pass @script的身份运行它，并且在脚本的末尾没有一个exit，这将导致命令窗口保持打开；(b)您的表中没有今年日期范围的任何已提交数据。如果在蟾蜍会话中添加数据并在那里运行查询，您就会看到这种效果，但是没有提交这些更改--在这种情况下，新插入的数据对任何其他会话都是不可见的，因此SQL*Plus查询不会看到它。而且，由于您有反馈，您甚至都不会看到“没有选择行”。

@boneist的简化比我的更简洁、更简单，但我将在这里展示CTE和non，以及between和>=/<的变体。

顺便说一句，您可以将日期计算简化得相当简单，比如：

with b as (
  select add_months(trunc(sysdate, 'YYYY'),
      case when extract(month from sysdate) < 10 then -3 else 9 end) d1,
    last_day(add_months(trunc(sysdate, 'YYYY'),
      case when extract(month from sysdate) < 10 then 8 else 20 end)) d2
  from dual
)
select *
from b
join table1 on table1.date_frais between b.d1 and b.d2;

您可以看到CTE将为这个演示的不同日期生成的开始日期和结束日期。我想这就是你想要的，如果我有解释当前查询的话。(和下面是@boneist简化的相同查询)。

或者，如果您不想显示日期范围以及来自table1的实际数据，请将计算移到筛选器中：

select * -- but still better to list the columns
from table1
where date_frais >= add_months(trunc(sysdate, 'YYYY'),
      case when extract(month from sysdate) < 10 then -3 else 9 end)
and date_frais < add_months(trunc(sysdate, 'YYYY'),
      case when extract(month from sysdate) < 10 then 9 else 21 end);

我还将这个版本从between更改为使用>=和<，并将结束日期推迟一天；这将包括在最后一个月的最后一天的任何值，其中有午夜后的时间(我看到@boneist也有评论)。如果你的约会都是午夜的话，那么between就可以了，但是我仍然喜欢这个明确的模式，而且它也使月份调整计算变得更加明显了。

Answer 2

简单地说，您的查询需要很长时间来检索所有的结果，而您还没有足够长的时间保存它吗？您可能会在蟾蜍中看到结果，但是您是否尝试过到结果集的末尾，而不是仅仅获得前500行(或者有多少次您将蟾蜍设置为每次获取)。

我怀疑这个问题是否与WITH条款有关。在任何情况下，都不需要这样做；您可以像这样简单地操作sysdate，以获得结果：

select *
from   SCHEMA.TABLE1 t1
where  t1.DATE_FRAIS between add_months(trunc(add_months(sysdate, 3), 'yyyy'), -3) 
                     and     add_months(trunc(add_months(sysdate, 3), 'yyyy'), 9) -1;

注:我希望你的DATE_FRAIS专栏没有时间元素，否则你会错过每年9月30日午夜之后的任何东西。