遇到无效的号码错误调用下面Oracle存储过程

Question

SELECT Ticket, ETRs, "Last ETR","Last ETR Time Change","STAR Restore Time","Restore Time - Last ETR" 
     FROM(
       SELECT e.xsystemjob Ticket, 
         a.eventkey Event, 
         (select count(generatedtime) from obvwh.ops_ertchangelog_fact where eventkey = a.eventkey) ETRs, 
         to_char(a.ERT, 'MM/DD/YYYY HH24:MI:SS') "Last ETR", --GENERATEDTIME, 
         to_char(generatedtime, 'MM/DD/YYYY HH24:MI:SS') as "Last ETR Time Change", 
         to_char(e.restdate,'MM/DD/YYYY HH24:MI:SS') as "STAR Restore Time", 
         round(((e.restdate - a.generatedtime) * 1440),0) as "Restore Time - Last ETR" 
       FROM obvwh.ops_ertchangelog_fact a 
       join obvwh.ops_event_dim e 
       on a.eventkey = e.eventkey 
       where a.generatedtime = (select max(generatedtime) from obvwh.ops_ertchangelog_fact where eventkey = a.eventkey) 
      ) 
      WHERE Substr(Ticket,0,1) = region 
      AND to_char("Last ETR", 'MM/DD/YYYY') between to_char(start_date,'MM/DD/YYYY') and to_char(end_date,'MM/DD/YYYY'); 

Answer 1

从你内部查询，Last ETR是一个字符串，表示在格式MM/DD/YYYY HH24:MI:SS列值。你试图将该字符串转换为一个字符串，传递一个单一的格式掩码，这就是抛出错误。

你可以看到同样的事情用一个简单的演示：

select to_char('07/18/2016 12:13:14', 'MM/DD/YYYY') from dual; 

Error report - 
SQL Error: ORA-01722: invalid number 

您可以在字符串中显式转换为日期，然后再返回：

select to_char(to_date('07/18/2016 12:13:14', 'MM/DD/YYYY HH24:MI:SS'), 'MM/DD/YYYY') from dual; 

TO_CHAR(TO 
---------- 
07/18/2016 

...但在上下文如果您的比较范围可能跨越一年结束，那么您的比较无论如何都没有意义 - 您使用的格式掩码不允许进行简单的比较。假设start_date和end_date的日期（用自己的时间设置为午夜），你可以这样做：

AND to_date("Last ETR", 'MM/DD/YYYY HH24:MI:SS') between start_date and end_date; 

或者更简单，使用原生ERT值（这大概是已经是一个日期），并将其转换为字符串 - 如果这实际上是正确的做法 - 在最外面的选择列表中。

我不太确定为什么你在这里有一个内联视图，或者你为什么使用子查询来计数，因为你已经在查询ops_ertchangelog_fact - 也许你想在这里使用分析函数。