一直报ORA-00911: 无效字符，但是自己没找出来

Question

    
 这是我写的这个SQL，一直报ORA-00911: 无效字符，开始发现一个逗号没用英文半角，修改后，还是报这个错误。不知道哪里还出错了
   
CREATE GLOBAL TEMPORARY TABLE temp_max_数据 ON COMMIT PRESERVE ROWS as        
select name ,city_name ,to_date(to_char(scan_start_time,'yyyy-MM-dd'),'yyyy-MM-dd') scan_start_time,s_hour  
from        
(select name,city_name,scan_start_time ,s_hour,数据, 
row_number() over(partition by name,city_name,scan_start_time  order by name,city_name,scan_start_time,数据 desc) rn        
from (        
select d.name,        
b.city_name,        
to_date(to_char(scan_start_time,'yyyy-MM-dd'),'yyyy-MM-dd') scan_start_time,        
a.s_hour,        max(nvl(a.ul_rlc_flow,0) + nvl(a.dl_rlc_flow, 0)) 数据        
from tpa_sts_rnc_agg a, region_city b, rnc d        
where a.int_id = d.int_id        
and d.city_id = b.city_id        
and a.sum_level = 0                    
and a.scan_start_time>=to_date('2011-05-01 0:00:00','yyyy-mm-dd hh24-mi-ss')           
and a.scan_start_time <=to_date('2011-05-03 23:59:59','yyyy-mm-dd hh24-mi-ss')                     
group by d.name, b.city_name, a.scan_start_time, a.s_hour        ))        
where rn = 1;      
      
CREATE GLOBAL TEMPORARY TABLE temp_max_语音 ON COMMIT PRESERVE ROWS as        
select  name ,city_name ,to_date(to_char(scan_start_time,'yyyy-MM-dd'),'yyyy-MM-dd')  scan_start_time ,s_hour  
from        
(select name,city_name,scan_start_time ,s_hour , 语音 , 
row_number() over(partition by name,city_name,scan_start_time  order by name,city_name,scan_start_time,语音 desc) rn        
from 
(select d.name,        
b.city_name,        
to_date(to_char(scan_start_time,'yyyy-MM-dd'),'yyyy-MM-dd') scan_start_time,        
a.s_hour,        
max(nvl(a.TRAFFIC_CS, 0)) as 语音        
from tpa_sts_rnc_agg a, region_city b, rnc d        
where a.int_id = d.int_id        
and d.city_id = b.city_id        
and a.sum_level = 0                    
and a.scan_start_time>=to_date('2011-05-01 0:00:00','yyyy-mm-dd hh24-mi-ss')           
and a.scan_start_time <=to_date('2011-05-03 23:59:59','yyyy-mm-dd hh24-mi-ss')                     
group by d.name, b.city_name, a.scan_start_time, a.s_hour          ) )        
where rn = 1;          
select a.name, a.city_name, a.scan_start_time, b.s_hour, a.s_hour        
from temp_max_数据 a, temp_max_语音 b        
where a.name= b.name        
and a.city_name = b.city_name        
and a.scan_start_time=b.scan_start_time

Answer 1

   这是我配在XML中的语句 ，无效标识符是在哪里啊，崩溃  CREATE GLOBAL TEMPORARY TABLE temp_max_数据 ON COMMIT PRESERVE ROWS as
      select name ,city_name ,to_date(to_char(scan_start_time,'yyyy-MM-dd'),'yyyy-MM-dd') scan_start_time,s_hour  from
      (select name,city_name,scan_start_time ,s_hour,数据, row_number() over(partition by name,city_name,scan_start_time  order by name,city_name,scan_start_time,数据 desc) rn
      from (
      select d.name,
      b.city_name,
      to_date(to_char(scan_start_time,'yyyy-MM-dd'),'yyyy-MM-dd') scan_start_time,
      a.s_hour,
      max(nvl(a.ul_rlc_flow,0) + nvl(a.dl_rlc_flow, 0)) 数据
      from tpa_sts_rnc_agg a, region_city b, rnc d
      where a.int_id = d.int_id
      and d.city_id = b.city_id
      and a.sum_level = 0
      <isNotNull property="TimeInputDTO.TimeFamily">
        <!--时间点为空,则是连续时间 -->
        <isNotNull property="TimeInputDTO.TimeFamily.HourList">
          <iterate open="and to_char(a.scan_start_time,'yyyy-mm-dd') in (" close=")" property="TimeInputDTO.TimeFamily.HourList" conjunction=",">
            and exists $TimeInputDTO.TimeFamily.HourList[]$
          </iterate>
        </isNotNull>
      </isNotNull>
      <![CDATA[
         and a.scan_start_time>=to_date('$TimeInputDTO.TimeFamily.DatetimeFrom$','yyyy-mm-dd hh24-mi-ss')
         and a.scan_start_time <=to_date('$TimeInputDTO.TimeFamily.DatetimeTo$','yyyy-mm-dd hh24-mi-ss')
          ]]>
      <!--离散时间点 -->
      <isNotNull prepend="AND" property="TimeInputDTO.TimeSeriesList">
        <iterate open="(" close=")" property="TimeInputDTO.TimeSeriesList" conjunction=" or ">
          <![CDATA[
          (a.scan_start_time>=to_date('$TimeInputDTO.TimeSeriesList[]$','yyyy-mm-dd hh24-mi-ss')
          and a.scan_start_time<=to_date('$TimeInputDTO.TimeSeriesList[]$','yyyy-mm-dd hh24-mi-ss')+1/24-+1/24/60/60
          ]]>
        </iterate>
      </isNotNull>
      group by d.name, b.city_name, a.scan_start_time, a.s_hour
      ))
      where rn = 1;      CREATE GLOBAL TEMPORARY TABLE temp_max_语音 ON COMMIT PRESERVE ROWS as
      select  name ,city_name ,to_date(to_char(scan_start_time,'yyyy-MM-dd'),'yyyy-MM-dd')  scan_start_time ,s_hour  from
      (
      select name,city_name,scan_start_time ,s_hour , 语音 , row_number() over(partition by name,city_name,scan_start_time  order by name,city_name,scan_start_time,语音 desc) rn
      from (
      select d.name,
      b.city_name,
      to_date(to_char(scan_start_time,'yyyy-MM-dd'),'yyyy-MM-dd') scan_start_time,
      a.s_hour,
      max(nvl(a.TRAFFIC_CS, 0)) as 语音
      from tpa_sts_rnc_agg a, region_city b, rnc d
      where a.int_id = d.int_id
      and d.city_id = b.city_id
      and a.sum_level = 0
      <isNotNull property="TimeInputDTO.TimeFamily">
        <!--时间点为空,则是连续时间 -->
        <isNotNull property="TimeInputDTO.TimeFamily.HourList">
          <iterate open="and to_char(a.scan_start_time,'yyyy-mm-dd') in (" close=")" property="TimeInputDTO.TimeFamily.HourList" conjunction=",">
            and exists $TimeInputDTO.TimeFamily.HourList[]$
          </iterate>
        </isNotNull>
      </isNotNull>
      <![CDATA[
         and a.scan_start_time>=to_date('$TimeInputDTO.TimeFamily.DatetimeFrom$','yyyy-mm-dd hh24-mi-ss')
         and a.scan_start_time <=to_date('$TimeInputDTO.TimeFamily.DatetimeTo$','yyyy-mm-dd hh24-mi-ss')
          ]]>
      <!--离散时间点 -->
      <isNotNull prepend="AND" property="TimeInputDTO.TimeSeriesList">
        <iterate open="(" close=")" property="TimeInputDTO.TimeSeriesList" conjunction=" or ">
          <![CDATA[
          (a.scan_start_time>=to_date('$TimeInputDTO.TimeSeriesList[]$','yyyy-mm-dd hh24-mi-ss')
          and a.scan_start_time<=to_date('$TimeInputDTO.TimeSeriesList[]$','yyyy-mm-dd hh24-mi-ss')+1/24-+1/24/60/60
          ]]>
        </iterate>
      </isNotNull>
      group by d.name, b.city_name, a.scan_start_time, a.s_hour      ) )
      where rn = 1;      select a.name, a.city_name, a.scan_start_time, b.s_hour, a.s_hour
      from temp_max_数据 a, temp_max_语音 b
      where a.name= b.name
      and a.city_name = b.city_name
      and a.scan_start_time=b.scan_start_time

Answer 2

一直很纠结，在数据里执行就没错，但是放到xml中后，就会在报错日志里看到，出错

Answer 3

TRAFFIC_CS 这个字段是什么类型？

Answer 4

发现报错的地方是在第二个 create 表的时候，和上一个 create表 最后 where rn=1; 之间说 无效标识符 就这 那该怎么连？不能用分好？where rn = 1;      
      
CREATE GLOBAL TEMPORARY TABLE temp_max_语音 ON COMMIT PRESERVE ROWS as  

Answer 5

'$TimeInputDTO.TimeFamily.DatetimeFrom$'这不是ibatis吗，我没有环境。但是你确定能把$放在<![CDATA[  ]]>里面？那可不止是用来识别< >的你把它写成类似<![CDATA[  (a.scan_start_time>=  ]]> 
    to_date('$TimeInputDTO.TimeSeriesList[]$','yyyy-mm-dd hh24-mi-ss')
<![CDATA[ and a.scan_start_time<= ]]>    to_date('$TimeInputDTO.TimeSeriesList[]$','yyyy-mm-dd hh24-mi-ss')+1/24-+1/24/60/60再试试吧