时间戳

首页 > 解决方案 > ORA-01722: 子查询上的数字无效

sql - ORA-01722: 子查询上的数字无效

问题描述

嗨，我正在尝试执行我的查询，不幸的是，我遇到了 ORA-01722 错误：无效号码

SELECT L.NEVENTLOGIDN, LPAD (nuserid, 6, '0') nuserid, u.susername, 
  TO_CHAR (TO_DATE ('1970-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS') + ( (ndatetime) / (60 * 60 * 24)), 'YYYY-MM-DD HH24:MI:SS') 
  date_time, l.nreaderidn, r.sname, 
  CASE WHEN l.nreaderidn IN (SELECT GETREADERSBYFUNC('OUT', 'LOCKER') devices FROM dual ) THEN 'O' 
  WHEN l.nreaderidn IN (SELECT GETREADERSBYFUNC('IN', 'LOCKER') devices FROM dual ) THEN 'I' END logtype 
  FROM TB_EVENT_LOG l, TB_READER r, TB_USER u 
  WHERE 
  l.nreaderidn IN ( SELECT GETREADERSBYDESC('LOCKER') devices FROM dual) 
  AND NDATETIME >= ((TO_DATE ('2020-01-27' || ' 12:00:00 AM', 'YYYY-MM-DD HH:MI:SS AM') ) - TO_DATE ('1970-01-01 12:00:00 AM', 'YYYY-MM-DD HH:MI:SS AM')) * 24 * 60 * 60 
  AND ndatetime <= ((TO_DATE ('2020-01-28' || ' 12:00:00 PM', 'YYYY-MM-DD HH:MI:SS PM') ) - TO_DATE ('1970-01-01 12:00:00 AM', 'YYYY-MM-DD HH:MI:SS AM')) * 24 * 60 * 60 
  AND l.nuserid = u.suserid 
  AND l.nreaderidn = r.nreaderidn 
  ORDER BY 2, 4

我认为错误的原因如下。

1

CASE WHEN l.nreaderidn IN (SELECT GETREADERSBYFUNC('OUT', 'LOCKER') devices FROM dual ) THEN 'O'

GetReaderbyfunc 的样本数据

'544381428','544381436','544381433','544381424','544381043'

2

  WHEN l.nreaderidn IN (SELECT GETREADERSBYFUNC('IN', 'LOCKER') devices FROM dual ) THEN 'I' END logtype

3

WHERE 
  l.nreaderidn IN ( SELECT GETREADERSBYDESC('LOCKER') devices FROM dual) 


SELECT GETREADERSBYFUNC('OUT', 'LOCKER') devices FROM dual

结果如下

'544381428','544381436','544381433','544381424','544381043'

因为 nreaderidn 是一个数字类型，但是当我把它的结果放在下面时它正在工作

SELECT GETREADERSBYDESC('LOCKER') devices FROM dual

结果如下

'544381050','544381441','544381428','544381436','544381431','544381064','544381433','544381435','544381424','544381043'



WHERE 
  l.nreaderidn IN ( '544381428','544381436','544381433','544381424','544381043')

功能

getreadersbyfunc (p_func VARCHAR2, p_desc VARCHAR2)
 RETURN VARCHAR2
IS
   retVal VARCHAR2(1024);
BEGIN

   for cur_rec in (SELECT nreaderidn FROM tb_reader where sdescription = p_desc and upper(sname) like '%' || upper(p_func) || '%') 
   loop
        if retVal is NULL then
            retVal := '''' || cur_rec.nreaderidn || '''';
        else
            retVal := retVal || ',''' || cur_rec.nreaderidn || '''';
        end if;
   end loop;

   return retVal;


   EXCEPTION
   WHEN NO_DATA_FOUND THEN
       NULL;
   WHEN OTHERS THEN
       RAISE;


getreadersbydesc (p_description VARCHAR2)
   RETURN VARCHAR2
IS
   retVal VARCHAR2(1024);
BEGIN

   for cur_rec in (SELECT nreaderidn FROM tb_reader where sdescription = p_description) 
   loop
        if retVal is NULL then
            retVal := '''' || cur_rec.nreaderidn || '''';
        else
            retVal := retVal || ',''' || cur_rec.nreaderidn || '''';
        end if;
   end loop;

   return retVal;


   EXCEPTION
   WHEN NO_DATA_FOUND THEN
       NULL;
   WHEN OTHERS THEN
       RAISE;
END;

我不允许更改nreaderidn和修改 oracle 函数的数据类型。有没有办法解决这个问题？先感谢您

标签： sqloracleoracle11g

解决方案

您的函数返回一个逗号分隔的数字列表，需要拆分。试试看

例如：

SELECT 
   regexp_substr(GETREADERSBYFUNC('OUT', 'LOCKER'), '[^,]+', 1, level)  
FROM dual 
  connect by 
  regexp_substr(GETREADERSBYFUNC('OUT', 'LOCKER'), '[^,]+', 1, level) is not null

您的查询将如下所示：

SELECT L.NEVENTLOGIDN, LPAD (nuserid, 6, '0') nuserid, u.susername, 
  TO_CHAR (TO_DATE ('1970-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS') + ( (ndatetime) / (60 * 60 * 24)), 'YYYY-MM-DD HH24:MI:SS') 
  date_time, l.nreaderidn, r.sname, 
  CASE WHEN l.nreaderidn IN (SELECT 
regexp_substr(GETREADERSBYFUNC('OUT', 'LOCKER'), '[^,]+', 1, level) FROM dual 
  connect by regexp_substr(GETREADERSBYFUNC('OUT', 'LOCKER'), '[^,]+', 1, level) is not null ) THEN 'O' 
  WHEN l.nreaderidn IN (SELECT 
   regexp_substr(GETREADERSBYFUNC('IN', 'LOCKER'), '[^,]+', 1, level) FROM dual 
  connect by  regexp_substr(GETREADERSBYFUNC('IN', 'LOCKER'), '[^,]+', 1, level) is not null) THEN 'I' END logtype 
  FROM TB_EVENT_LOG l, TB_READER r, TB_USER u 
  WHERE 
  l.nreaderidn IN ( SELECT 
   regexp_substr(GETREADERSBYFUNC('LOCKER'), '[^,]+', 1, level)  FROM dual 
  connect by  regexp_substr(GETREADERSBYFUNC('LOCKER'), '[^,]+', 1, level) is not null) 
  AND NDATETIME >= ((TO_DATE ('2020-01-27' || ' 12:00:00 AM', 'YYYY-MM-DD HH:MI:SS AM') ) - TO_DATE ('1970-01-01 12:00:00 AM', 'YYYY-MM-DD HH:MI:SS AM')) * 24 * 60 * 60 
  AND ndatetime <= ((TO_DATE ('2020-01-28' || ' 12:00:00 PM', 'YYYY-MM-DD HH:MI:SS PM') ) - TO_DATE ('1970-01-01 12:00:00 AM', 'YYYY-MM-DD HH:MI:SS AM')) * 24 * 60 * 60 
  AND l.nuserid = u.suserid 
  AND l.nreaderidn = r.nreaderidn 
  ORDER BY 2, 4

推荐阅读