Inceptor Parse error [Error 1110] line 102,24 SQL问题

 今天遇到一个SQL跑不通的问题：

去掉cast as

去掉round

 

  最初以为是Inceptor不兼容ORACLE语句Cast as 导致的，做的以下测试

 

 

 

发现都能跑通，说明Cast as语句在Inceptor中能正常使用。

然后仔细检查Sql语句

最初语句报错信息如下：

只执行前半部分，去掉left join能正常运行，验证了Cast as没错

加上left join后又报错：

最后发现，131行少写一个英文逗号，加上后问题解决。

原始SQL

with `__all_dim__` as (
  select
    *
  from (
      select
        from_unixtime(
          unix_timestamp(`__bts__`) -1,
          'yyyy-MM-dd HH:mm:ss'
        ) as `__bts__`
      from (
          select
            concat_ws(' ', `d`.`date`, `t`.`time_of_day`) as `__bts__`
          from `ecmp`.`dim_date` as `d`
          left join `ecmp`.`dim_time_of_day` as `t` on 1 = 1
          where
            `d`.`date` >= '2019-12-24'
            and `d`.`date` <= '2019-12-25'
        ) as `__bts___tp1`
      where
        `__bts__` > '2019-12-24 00:00:00'
        and `__bts__` <= '2019-12-25 00:00:00'
        and second(`__bts__`) = 0
        and minute(`__bts__`) = 0
        and hour(`__bts__`) = 0
        and pmod(day(`__bts__`), 1) = 0
    ) as `__time_model__`
  cross join (
      select
        `dd_60139`.`ci_pk` as `ci_pk`,
        `dd_60139`.`ci_id` as `ci_id`,
        `dd_60139`.`ci_code` as `ci_code`,
        `dd_60139`.`ci_name` as `ci_name`,
        `dd_60139`.`bs_pk` as `bs_pk`,
        `dd_60139`.`aps_pk` as `aps_pk`,
        `dd_60139`.`tenant_pk` as `tenant_pk`,
        `dd_60139`.`ts_pk` as `ts_pk`,
        `dd_60139`.`oc_pk` as `oc_pk`,
        `dd_60139`.`bs_id` as `bs_id`,
        `dd_60139`.`aps_id` as `aps_id`,
        `dd_60139`.`tenant_id` as `tenant_id`,
        `dd_60139`.`oc_id` as `oc_id`,
        `dd_60139`.`ts_id` as `ts_id`,
        `dd_60139`.`tenant_name` as `tenant_name`,
        `dd_60139`.`oc_name` as `oc_name`,
        `dd_60139`.`aps_dname` as `aps_dname`,
        `dd_60139`.`bs_name` as `bs_name`,
        `dd_60139`.`ts_name` as `ts_name`
      from `ecmp`.`dim_ci` as `dd_60139`
    ) as `ci_pk`
  cross join (
      select
        '运维A角' as incident_acceptor_role
      from system.dual
      union all
      select
        '运维B角' as incident_acceptor_role
      from system.dual
      union all
      select
        '运维1.5线' as incident_acceptor_role
      from system.dual
      union all
      select
        '其他' as incident_acceptor_role
      from system.dual
    ) as `incident_acceptor_role`
)
,`t` as (
  select
    `ci_pk`,
    `accept_incident_count`,
    `aps_pk`,
    `incident_acceptor_role`,
    rank() over(
      partition by `aps_pk`,
      `incident_acceptor_role`
      order by
        `accept_incident_count` DESC
    ) as `accept_incident_count_rank`,
    rank() over(
      partition by `aps_pk`,
      `incident_acceptor_role`
      order by
        `accept_incident_count` ASC
    ) as `__inverse_rank__`
  from (
      select
        `aps_pk`,
        `incident_acceptor_role`,
        `ci_pk`,
        accept_incident_count as accept_incident_count
      from `ecmp`.dws_ci_b00001t01_nd01_003
      where
        accept_incident_count is not null
        and `__bts__` >= '2019-12-24 00:00:00'
        AND `__bts__` <= '2019-12-24 23:59:59'
    ) as `t0`
)
--insert into `ecmp`.`dws_ci_b00001t04_nd01_018`(`__bts__`,`__cts__`,`accept_incident_count`,`accept_incident_count_rank`,`accept_incident_count_win_rate`,`aps_dname`,`aps_id`,`aps_pk`,`bs_id`,`bs_name`,`bs_pk`,`ci_code`,`ci_id`,`ci_name`,`ci_pk`,`incident_acceptor_role`,`oc_id`,`oc_name`,`oc_pk`,`tenant_id`,`tenant_name`,`tenant_pk`,`ts_id`,`ts_name`,`ts_pk`)

select
  `__all_dim__`.`ci_pk` as `ci_pk`,
  `__all_dim__`.`incident_acceptor_role` as `incident_acceptor_role`,
  CAST(round(nvl(`accept_incident_count`, 0), 0) as INT) as `accept_incident_count`,
  CAST(round(`accept_incident_count_rank`, 0) as INT) as `accept_incident_count_rank`,
  CAST(round(`accept_incident_count_win_rate`, 1) as DOUBLE) as `accept_incident_count_win_rate`,
  `__all_dim__`.`ci_id` as `ci_id`,
  `__all_dim__`.`ci_code` as `ci_code`,
  `__all_dim__`.`ci_name` as `ci_name`,
  `__all_dim__`.`bs_pk` as `bs_pk`,
  `__all_dim__`.`aps_pk` as `aps_pk`,
  `__all_dim__`.`tenant_pk` as `tenant_pk`,
  `__all_dim__`.`ts_pk` as `ts_pk`,
  `__all_dim__`.`oc_pk` as `oc_pk`,
  `__all_dim__`.`bs_id` as `bs_id`,
  `__all_dim__`.`aps_id` as `aps_id`,
  `__all_dim__`.`tenant_id` as `tenant_id`,
  `__all_dim__`.`oc_id` as `oc_id`,
  `__all_dim__`.`ts_id` as `ts_id`,
  `__all_dim__`.`tenant_name` as `tenant_name`,
  `__all_dim__`.`oc_name` as `oc_name`,
  `__all_dim__`.`aps_dname` as `aps_dname`,
  `__all_dim__`.`bs_name` as `bs_name`,
  `__all_dim__`.`ts_name` as `ts_name`,
  CAST(SYSDATE as STRING) as `__cts__`,
  `__all_dim__`.`__bts__` as `__bts__`
from `__all_dim__`
left join (
    select
      '2019-12-24 23:59:59' as `__bts__`,
      `accept_incident_count`,
      `accept_incident_count_rank`,
      `accept_incident_count_win_rate`,//这一行
      CAST(coalesce(`tp1`.`aps_pk`) as STRING) as `aps_pk`,
      CAST(coalesce(`tp1`.`ci_pk`) as STRING) as `ci_pk`,
      CAST(coalesce(`tp1`.`incident_acceptor_role`) as STRING) as `incident_acceptor_role`
    from (
        select
          `t`.`ci_pk`,
          `t`.`aps_pk`,
          `t`.`incident_acceptor_role`,
          `t`.`accept_incident_count`,
          `t`.`accept_incident_count_rank`,
          if(`c`.ct = 1,null,(`__inverse_rank__` -1) /(`c`.ct -1) * 100) as `accept_incident_count_win_rate`
        from `t`
        left join (
            select
              `aps_pk`,
              `incident_acceptor_role`,
              count(*) as `ct`
            from `t`
            group by
              `aps_pk`,
              `incident_acceptor_role`
          ) as `c`
          on 1 = 1
          and `t`.`aps_pk` = `c`.`aps_pk`
          and `t`.`incident_acceptor_role` = `c`.`incident_acceptor_role`
    ) as `tp1`
  ) as `__dws__`
  on `__all_dim__`.`__bts__` = `__dws__`.`__bts__`
  and `__all_dim__`.`ci_pk` = `__dws__`.`ci_pk`
  and `__all_dim__`.`incident_acceptor_role` = `__dws__`.`incident_acceptor_role`