如何解决错误：ORACLE准备错误：ORA-00919

7gyucuyw 于 2023-06-22 发布在 Oracle

关注(0)|答案(4)|浏览(244)

连接到Oracle后遇到问题。下面是我的部分代码：

%let rokmies= 202305;

proc sql noprint;
connect to oracle(user=&rdo_usr. password=&rdo_pwd.
path="(DESCRIPTION=(ADDRESS = (PROTOCOL = TCP)( HOST = 122.137.78.156)(PORT = 1831))(CONNECT_DATA = (SID=RDO)))"
); 

create table metody as
select 
  METHOD, COST
from connection to Oracle 
(
SELECT 
  rech_sp_method as METHOD,
  rm_cost as COST,
  ROW_NUMBER() OVER (PARTITION BY rech_sp_method ORDER BY  rdo_enddate desc,rdo_namestamp desc, rdo_status ) as rn
FROM ptk_kom.sp_recharge_method
)
where rn =1
;

disconnect from oracle;
quit;

proc sql noprint;
connect to oracle(user=&rdo_usr. password=&rdo_pwd.
path="(DESCRIPTION=(ADDRESS = (PROTOCOL = TCP)( HOST = 122.137.78.156)(PORT = 1831))(CONNECT_DATA = (SID=RDO)))"
); 

create table plik_recharges as
select * from connection to Oracle 
(
select ACCOUNT, COSTS, METHOD as day
from ptk_pop.recharge_usage 
where (balance_types = '6' or balance_types like '6,%' or balance_types like '%,6,%' or balance_types like '%,6' 
or balance_types = '2421' or balance_types like '2421,%' or balance_types like '%,2421,%' or balance_types like '%,2421')
and substr(rdo_namestamp, 1, 6) = &rokmies.

);

我的整个错误是：

ERROR: ORACLE prepare error: ORA-00919: niepoprawna funkcja. SQL statement: select ACCOUNT,
       COSTS, METHOD as day from ptk_pop.recharge_usage where (balance_types 
       = '6' or balance_types like '6,%' or balance_types like '%,6,%' or balance_types like '%,6' or balance_types = '2421' or 
       balance_types like '2421,%' or balance_types like '%,2421,%' or balance_types like '%,2421') and substr(rdo_namestamp, 1, 6) 
       = &rokmies..

20230;，有人能帮忙解决吗？

我检查了是否所有的balance_types都必须具有所有这些条件，但不幸的是它没有给予任何东西。

oracle

来源：https://stackoverflow.com/questions/76420846/how-to-solve-error-oracle-prepare-error-ora-00919

4条答案

按热度按时间

xwbd5t1u1#

日是保留字。如果您想要一个名为day的列，请将其放在双引号内。

...
select ACCOUNT, COSTS, METHOD as "day"
...

赞(0）回复(0）举报 2023-06-22

blpfk2vs2#

首先，select * from connection to Oracle (<sql code>)查询是对Oracle SQL的语法上有效的SAS调用。请参阅文档：https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/acreldb/n05b4mygsvt845n1vnr6r5kchbjf.htm
其次，据我所知，substr()函数返回字符，而对于Oracle，202305看起来像一个整数。尝试添加以下内容：%tslit(&rokmies.)将解析为'202305'。
我还将检查METHOD是否不是某种qracle关键字，尝试将其从METHOD更改为sp_METHOD。
P.S. Powodzenia.

赞(0）回复(0）举报 2023-06-22

wz8daaqr3#

正如@astentx在评论中提到的，你有无效的语法，使用这个查询代替：

create table plik_recharges as
select ACCOUNT, COSTS, METHOD as day
from recharge_usage 
where (balance_types = '6' or balance_types like '6,%' or balance_types like '%,6,%' or balance_types like '%,6' 
or balance_types = '2421' or balance_types like '2421,%' or balance_types like '%,2421,%' or balance_types like '%,2421')
and substr(rdo_namestamp, 1, 6) = &rokmies

Demo here

赞(0）回复(0）举报 2023-06-22

chy5wohz4#

您的查询看起来语法无效，您可能需要从其中删除connection to Oracle（除非SAS预处理器要求并将其删除;但在Oracle处理SQL时肯定无效）：

create table metody as
select METHOD, COST
from   (
  SELECT rech_sp_method as METHOD,
         rm_cost as COST,
         ROW_NUMBER() OVER (PARTITION BY rech_sp_method ORDER BY  rdo_enddate desc,rdo_namestamp desc, rdo_status ) as rn
  FROM   ptk_kom.sp_recharge_method
)
where rn = 1;

以及：

create table plik_recharges as
select *
from   (
  select ACCOUNT,
         COSTS,
         METHOD as day
  from   ptk_pop.recharge_usage 
  where  (  ',' || balance_types || ',' LIKE '%,6,%'
         or ',' || balance_types || ',' LIKE '%,2421,%'
         )
  and    substr(rdo_namestamp, 1, 6) = &rokmies
);

(and不要在最后的);之前留下空行，因为您的预处理器似乎将空行作为语句的结尾，因为最后的右括号没有出现在错误消息中。）

赞(0）回复(0）举报 2023-06-22

我来回答

如何解决错误：ORACLE准备错误：ORA-00919

20230;，有人能帮忙解决吗？

4条答案

相关问题

热门标签

最新问答