我有个SQL语句,子查询里如果加上转换函数就报错,但是单独把子查询拿出来执行,就没问题:
1、执行整个SQL报错,说无效数字:
SQL> SELECT DEPTID FROM T_DEPT WHERE FLAG = 1
2START WITH DEPTID IN (SELECT to_number(c.KEY) FROM T_CONST c WHERE c.CODE = 'baseinfo_report' AND c.NOTE = 'COMPANY_ID')
3CONNECT BY PRIOR DEPTID = PARENT;
START WITH DEPTID IN (SELECT to_number(c.KEY) FROM T_CONST c WHERE c.CODE = 'baseinfo_report' AND c.NOTE = 'COMPANY_ID')
*
ERROR at line 2:
ORA-01722: 无效数字
2、单独执行子查询,to_number函数正常,返回值正常
SQL> SELECT to_number(c.KEY) FROM T_CONST c WHERE c.CODE = 'baseinfo_report' AND c.NOTE = 'COMPANY_ID';
TO_NUMBER(C.KEY)
----------------
417118
479437
767
3、如果不转换子查询中的字段,转换主SQL的字段,则可以:
SQL> SELECT DEPTID FROM T_SYS_DEPT WHERE FLAG = 1
2START WITH to_char(DEPTID) IN (SELECT c.KEY FROM T_SYS_CONST c WHERE c.CODE = 'baseinfo_report' AND c.NOTE = 'COMPANY_ID')
3CONNECT BY PRIOR DEPTID = PARENT;
DEPTID
----------
767
843
1088
1089
1090
1091
1092
1093
440505
417118
440449
DEPTID
----------
440450
474295
474297
474301
483211
483212
483213
479437
19 rows selected.
明明子查询返回的值都是可以转成number型的,为什么在第一个SQL中就报错呢?
SQL> SELECT to_number(c.KEY),length(c.key) FROM T_SYS_CONST c WHERE c.CODE = 'baseinfo_report' AND c.NOTE = 'COMPANY_ID';
TO_NUMBER(C.KEY) LENGTH(C.KEY)
---------------- -------------