将查询从mysql转换为oracle时出错

Question

我在我的webapp应用程序中有四个mysql查询，我正试图将这些查询转换为oracle查询。但是，当我尝试运行新查询时，日期时间字符串会中断。有人能帮我找出我做错了什么吗？

PostreSQL查询：-

插入到o_stat_daily中(业务路径、大小、日期、值) (选择businesspath，int8(子字符串(从位置(‘：’在businesspath中)+1表示在businesspath中的位置(‘]’)-位置(‘：’在业务路径中)- 1))，date_trunc('day'，creationdate)为d，从o_loggingtable中计数(*)为c，其中action谓词=‘启动’和actionobject=‘节点’和businesspath != '‘组按业务路径，d)； 插入o_stat_weekly (业务路径、大小、周、值) (选择businesspath，int8(子字符串(从位置(‘：’‘在businesspath中)+1表示在businesspath中的位置(’]‘)-位置(’：‘在业务路径中)-1)，to_char(creationdate，'IYYY') \ d，’IW‘，从o_loggingtable中计数(*)为c，其中action谓词=‘启动’和actionobject=‘节点’和businesspath != '‘组按业务路径，d)； 插入到o_stat_dayofweek中(业务路径、大小、日期、值) (选择businesspath，int8(子字符串(从位置(‘：’在businesspath中)+1表示在businesspath中的位置(‘]’)-位置(‘：’在业务路径中)-1)，int8(to_char(创建日期，‘D’)为d，从o_loggingtable中计数(*)为c，其中action谓词=‘启动’和actionobject=‘节点’和businesspath != '‘组按业务路径，d)； 插入o_stat_hourofday (业务路径、残差、小时、值) (选择businesspath，int8(子字符串(从位置(‘：’在businesspath中)+1表示在businesspath中的位置(‘]’)-位置(‘：’在业务路径中)-1)，int8(to_char(创建日期，‘HH24’)为d，从o_loggingtable中计数(*)为c，其中action谓词=‘启动’和actionobject=‘节点’和businesspath != '‘组按业务路径，d)；

Oracle查询：-

插入到o_stat_daily中(业务路径、大小、日期、值) (选择业务路径，转换(substr( businesspath，locate(':'，businesspath) + 1，locate(‘]，businesspath) -找到(’：‘，businesspath) - 1)，int)，转换(creationdate，date) d，从o_loggingtable中计数(*)c，其中action谓词=‘启动’和actionobject=‘节点’和businesspath != '‘组按业务路径，d)； 插入o_stat_weekly (业务路径、大小、周、值) (选择业务路径，转换(substr( businesspath，locate(':'，businesspath) + 1，locate(‘]，businesspath) - locate(':'，businesspath) - 1)，int)，年份(创建日期)+ '-'+repeat('0',2-length(convert((dayofyear(creationdate)-dayofweek(creationdate))/7，varchar(7))))+convert((dayofyear(creationdate)-dayofweek(creationdate))/7，varchar(7)) d，从o_loggingtable计数(*)c，其中action谓词=‘启动’和actionobject=‘节点’和businesspath != '‘组由businesspath，d组成； 插入到o_stat_dayofweek中(业务路径、大小、日期、值) (选择业务路径，转换(substr( businesspath，locate(':'，businesspath) + 1，locate(‘]，businesspath)从o_loggingtable中计数(*)c，其中action谓词=‘启动’和actionobject=‘节点’和businesspath != '‘组按业务路径，d)； 插入o_stat_hourofday (业务路径、残差、小时、值) (选择业务路径，转换(substr( businesspath，locate(':'，businesspath) + 1，locate(‘]，businesspath) -查找(’：‘，businesspath) - 1)，int)，小时(创世日期) d，从o_loggingtable中计数(*)c，其中action谓词=‘启动’和actionobject=‘节点’和businesspath != '‘组按业务路径，d)；

Answer 1

与其重写您的所有查询，我只接受了最后一个查询。

oracle查询应该如下所示：

INSERT INTO o_stat_hourofday (
   businesspath,
   resid,
   hour,
   VALUE
)
SELECT businesspath,
       TO_NUMBER (
          SUBSTR (
             businesspath,
             INSTR(businesspath, ':') + 1,
             INSTR(businesspath, ']') - INSTR(businesspath, ':') - 1
          )
       ),
       TO_NUMBER(TO_CHAR(creationdate, 'HH24')) d,
       COUNT (*) c
  FROM o_loggingtable
 WHERE actionverb = 'launch'
   AND actionobject = 'node'
   AND businesspath IS NOT NULL
 GROUP BY businesspath,
          TO_NUMBER (
             SUBSTR (
                businesspath,
                INSTR(businesspath, ':') + 1,
                INSTR(businesspath, ']') - INSTR(businesspath, ':') - 1
             )
          ), 
          TO_NUMBER(TO_CHAR(creationdate, 'HH24'));

FYI，Oracle不识别HOUR函数，oracle中的CONVERT将一个字符集转换为另一个字符集，而不是将字符串转换为数字。LOCATE也不是Oracle函数，您需要使用INSTR来查找字符串中的字符。

阅读字符 (包括日期格式等)、数和INSTR。

希望这对你有帮助！

Answer 2

我还对剩下的三个查询进行了测试。他们都在工作。谢谢你的帮助奥利。

INSERT INTO o_stat_daily (
   businesspath,
   resid,
   DAY,
   VALUE
)
SELECT businesspath,
       TO_NUMBER (
          SUBSTR (
             businesspath,
             INSTR(businesspath, ':') + 1,
             INSTR(businesspath, ']') - INSTR(businesspath, ':') - 1
          )
       ),
       to_date(creationdate)
       d,
       COUNT (*) c
  FROM o_loggingtable
 WHERE actionverb = 'launch'
   AND actionobject = 'node'
   AND businesspath IS NOT NULL
 GROUP BY businesspath,
          TO_NUMBER (
             SUBSTR (
                businesspath,
                INSTR(businesspath, ':') + 1,
                INSTR(businesspath, ']') - INSTR(businesspath, ':') - 1
             )
          ), 
          to_date(creationdate);



INSERT INTO o_stat_weekly (
   businesspath,
   resid,
   WEEK,
   VALUE
)
SELECT businesspath,
       TO_NUMBER (
          SUBSTR (
             businesspath,
             INSTR(businesspath, ':') + 1,
             INSTR(businesspath, ']') - INSTR(businesspath, ':') - 1
          )
       ),
       to_char(creationdate, 'IYYY') || '-' || to_char(creationdate, 'IW') 
       d,
       COUNT (*) c
  FROM o_loggingtable
 WHERE actionverb = 'launch'
   AND actionobject = 'node'
   AND businesspath IS NOT NULL
 GROUP BY businesspath,
          TO_NUMBER (
             SUBSTR (
                businesspath,
                INSTR(businesspath, ':') + 1,
                INSTR(businesspath, ']') - INSTR(businesspath, ':') - 1
             )
          ), 
          to_char(creationdate, 'IYYY') || '-' || to_char(creationdate, 'IW') ;

INSERT INTO o_stat_dayofweek (
   businesspath,
   resid,
   day,
   VALUE
)
SELECT businesspath,
       TO_NUMBER (
          SUBSTR (
             businesspath,
             INSTR(businesspath, ':') + 1,
             INSTR(businesspath, ']') - INSTR(businesspath, ':') - 1
          )
       ),
       TO_NUMBER(TO_CHAR(creationdate, 'D')) d,
       COUNT (*) c
  FROM o_loggingtable
 WHERE actionverb = 'launch'
   AND actionobject = 'node'
   AND businesspath IS NOT NULL
 GROUP BY businesspath,
          TO_NUMBER (
             SUBSTR (
                businesspath,
                INSTR(businesspath, ':') + 1,
                INSTR(businesspath, ']') - INSTR(businesspath, ':') - 1
             )
          ), 
          TO_NUMBER(TO_CHAR(creationdate, 'D'));