When running on sas it gives ERROR: CLI open cursor error: [Oracle][ODBC][Ora]ORA-01722: invalid number

huangapple
go评论105阅读模式
英文:

When running on sas it gives ERROR: CLI open cursor error: [Oracle][ODBC][Ora]ORA-01722: invalid number

问题

  1. proc sql;
  2. connect to odbc(user='' pwd='' datasrc='');
  3. create table Hums_Table(compress=yes) as 
  4. select * from connection to odbc
  5. (
  6. 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 
  7. inner join ADMIN.t_plan_type PL on replace(prop.cont_plan_id,'~',NULL)=PL.plan_code
  8. inner join ADMIN.t_underwriting_decision deci on prop.decision_id_fk=deci.o_id
  9. inner join admin.t_city CT on prop.cust_corr_city_code_fk=ct.city_name
  10. left join admin.T_tele_docsapp_data doc on replace(prop.cust_app_no,'~',NULL)=doc.application_no
  11. where Prop.create_date between '01-Mar-2023' and '31-Mar-2023'
  12. );
  13. disconnect from odbc;
  14. quit;

在运行SAS时出现错误：CLI打开游标错误：[Oracle][ODBC][Ora]ORA-01722：无效的数字。

英文: 
  1.  proc sql;
  2.  connect to odbc(user='' pwd='' datasrc='');
  3.  create table Hums_Table(compress=yes) as 
  4.  select * from connection to odbc
  5.  (
  6.  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 
  7.  inner join ADMIN.t_plan_type PL on replace(prop.cont_plan_id,'~',NULL)=PL.plan_code
  8.  inner join ADMIN.t_underwriting_decision deci on prop.decision_id_fk=deci.o_id
  9.  inner join admin.t_city CT on prop.cust_corr_city_code_fk=ct.city_name
  10.  left join admin.T_tele_docsapp_data doc on replace(prop.cust_app_no,'~',NULL)=doc.application_no
  11.  where Prop.create_date between '01-Mar-2023' and '31-Mar-2023'
  12.  
  13.  );
  14.  disconnect from odbc;
  15.  quit;

When running on sas it gives ERROR: CLI open cursor error: [Oracle][ODBC][Ora]ORA-01722: invalid number

答案1

得分: 1

Oracle会尝试在执行比较之前对数据类型进行对齐。例如，考虑表T，其中有一列你期望是数字类型的：

  1. SQL> create table t ( my_semi_numeric_value varchar2(10));
  3. Table created.
  5. SQL>
  6. SQL> insert into t values ('10');
  8. 1 row created.
  10. SQL> insert into t values ('20');
  12. 1 row created.
  14. SQL>
  15. SQL> set autotrace traceonly explain
  16. SQL> select *
  17.   2  from t
  18.   3  where my_semi_numeric_value > 0;
  20. Execution Plan
  21. ----------------------------------------------------------
  22. Plan hash value: 1601196873
  24. --------------------------------------------------------------------------
  25. | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
  26. --------------------------------------------------------------------------
  27. |   0 | SELECT STATEMENT  |      |     2 |    14 |     3   (0)| 00:00:01 |
  28. |*  1 |  TABLE ACCESS FULL| T    |     2 |    14 |     3   (0)| 00:00:01 |
  29. --------------------------------------------------------------------------
  31. Predicate Information (identified by operation id):
  32. ---------------------------------------------------
  34.    1 - filter(TO_NUMBER("MY_SEMI_NUMERIC_VALUE")>0)
  36. Note
  37. -----
  38.    - dynamic statistics used: dynamic sampling (level=2)
  40. SQL> set autotrace off

你可以看到在执行计划中，我们在列周围默默添加了TO_NUMBER函数，因为你在比较时使用了数字（> 0）。因此，当前查询将正常运行：

  1. SQL> select *
  2.   2  from t
  3.   3  where my_semi_numeric_value > 0;
  5. MY_SEMI_NU
  6. ----------
  7. 10
  8. 20

但如果有人在该列中插入了一些错误的数据，例如：

  1. SQL> insert into t
  2.   2  values ('~10');
  4. 1 row created.

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

  1. SQL> select *
  2.   2  from t
  3.   3  where my_semi_numeric_value > 0;
  4. where my_semi_numeric_value > 0
  5.       *
  6. ERROR at line 3:
  7. ORA-01722: invalid number

根据你的数据库版本，你可以自行处理这个问题：

  1. SQL> select *
  2.   2  from t
  3.   3  where to_number(my_semi_numeric_value default -1 on conversion error) > 0 ;
  5. MY_SEMI_NU
  6. ----------
  7. 10
  8. 20

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

英文:

Oracle will try to get the data types aligned before doing a comparison. For example, consider table T with a column that you expect to be numeric

  1. SQL> create table t ( my_semi_numeric_value varchar2(10));
  3. Table created.
  5. SQL>
  6. SQL> insert into t values ('10');
  8. 1 row created.
  10. SQL> insert into t values ('20');
  12. 1 row created.
  14. SQL>
  15. SQL> set autotrace traceonly explain
  16. SQL> select *
  17.   2  from t
  18.   3  where my_semi_numeric_value > 0;
  20. Execution Plan
  21. ----------------------------------------------------------
  22. Plan hash value: 1601196873
  24. --------------------------------------------------------------------------
  25. | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
  26. --------------------------------------------------------------------------
  27. |   0 | SELECT STATEMENT  |      |     2 |    14 |     3   (0)| 00:00:01 |
  28. |*  1 |  TABLE ACCESS FULL| T    |     2 |    14 |     3   (0)| 00:00:01 |
  29. --------------------------------------------------------------------------
  31. Predicate Information (identified by operation id):
  32. ---------------------------------------------------
  34.    1 - filter(TO_NUMBER("MY_SEMI_NUMERIC_VALUE")>0)
  36. Note
  37. -----
  38.    - dynamic statistics used: dynamic sampling (level=2)
  40. SQL> set autotrace off

You can see in the execution plan, we silently added a TO_NUMBER around the column because you were comparing with a numeric (> 0). The query will hence run fine as it currently stands

  1. SQL> select *
  2.   2  from t
  3.   3  where my_semi_numeric_value > 0;
  5. MY_SEMI_NU
  6. ----------
  7. 10
  8. 20

but what if someone puts some bad data in that column, eg

  1. SQL> insert into t
  2.   2  values ('~10');
  4. 1 row created.

Now when I run that query, the TO_NUMBER function will fail and hence so will your query

  1. SQL> select *
  2.   2  from t
  3.   3  where my_semi_numeric_value > 0;
  4. where my_semi_numeric_value > 0
  5.       *
  6. ERROR at line 3:
  7. ORA-01722: invalid number

Depending on your database version, you can handle this yourself

  1. SQL> select *
  2.   2  from t
  3.   3  where to_number(my_semi_numeric_value default -1 on conversion error) > 0 ;
  5. MY_SEMI_NU
  6. ----------
  7. 10
  8. 20

but as others have mentioned, you need to check the datatypes of each column that you are using in the query, including your joins

huangapple
  • 本文由 发表于 2023年5月24日 18:54:39
  • 转载请务必保留本文链接：https://go.coder-hub.com/76322746.html
  • database
  • oracle
  • sas
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定