当在sas上运行时，它给出错误：CLI打开游标错误：[Oracle][ODBC][Ora]ORA-01722：无效号码

yqkkidmi 于 2023-05-28 发布在 Oracle

关注(0)|答案(1)|浏览(287)

proc sql;
 connect to odbc(user='' pwd='' datasrc='');
 create table Hums_Table(compress=yes) as 
 select * from connection to odbc
 (
 select prop.cust_pivotal_id, prop.cust_pivotal_app_id, prop.cust_renew_flag, prop.is_cust_split, prop.cont_agt_brk_channel, prop.cont_plan_id, prop.decision_id_fk, deci.decision, prop.cust_corr_pin_code, prop.cust_corr_city_code_fk, pl.sum_assured, pl.product_name, Ct.City_name from ADMIN.t_proposal_info Prop 
 inner join ADMIN.t_plan_type PL on replace(prop.cont_plan_id,'~',NULL)=PL.plan_code
 inner join ADMIN.t_underwriting_decision deci on prop.decision_id_fk=deci.o_id
 inner join admin.t_city CT on prop.cust_corr_city_code_fk=ct.city_name
 left join admin.T_tele_docsapp_data doc on replace(prop.cust_app_no,'~',NULL)=doc.application_no
 where Prop.create_date between '01-Mar-2023' and '31-Mar-2023'
 
 );
 disconnect from odbc;
 quit;

oracle

来源：https://stackoverflow.com/questions/76322746/when-running-on-sas-it-gives-error-cli-open-cursor-error-oracleodbcoraor

1条答案

按热度按时间

qnakjoqk1#

Oracle将在进行比较之前尝试对齐数据类型。例如，考虑表T，其中有一列您 * 期望 * 为数值

SQL> create table t ( my_semi_numeric_value varchar2(10));

Table created.

SQL>
SQL> insert into t values ('10');

1 row created.

SQL> insert into t values ('20');

1 row created.

SQL>
SQL> set autotrace traceonly explain
SQL> select *
  2  from t
  3  where my_semi_numeric_value > 0;

Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     2 |    14 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T    |     2 |    14 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(TO_NUMBER("MY_SEMI_NUMERIC_VALUE")>0)

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)

SQL> set autotrace off

您可以在执行计划中看到，我们在列周围默默地添加了一个TO_NUMBER，因为您正在与一个数字（> 0）进行比较。因此，查询将按照当前的状态正常运行

SQL> select *
  2  from t
  3  where my_semi_numeric_value > 0;

MY_SEMI_NU
----------
10
20

但是如果有人把一些错误的数据放进那一列，比如

SQL> insert into t
  2  values ('~10');

1 row created.

现在，当我运行该查询时，TO_NUMBER函数将失败，因此您的查询也将失败

SQL> select *
  2  from t
  3  where my_semi_numeric_value > 0;
where my_semi_numeric_value > 0
      *
ERROR at line 3:
ORA-01722: invalid number

根据您的数据库版本，您可以自己处理这个问题

SQL> select *
  2  from t
  3  where to_number(my_semi_numeric_value default -1 on conversion error) > 0 ;

MY_SEMI_NU
----------
10
20

但正如其他人所提到的，您需要检查在查询中使用的每个列的数据类型，包括连接

赞(0）回复(0）举报 2023-05-28

我来回答

当在sas上运行时，它给出错误：CLI打开游标错误：[Oracle][ODBC][Ora]ORA-01722：无效号码

1条答案

相关问题

热门标签

最新问答