执行查询时发生ORA-00923错误

Question

为了查看输出，我使用了下面的查询，但是我得到了下面的错误消息。你能帮我一下吗？

查询：

SELECT REGEXP_SUBSTR(pr.Asset, '(.+):.+@',1,1,'i',1) as pr.instancename ,
upper(REGEXP_SUBSTR(pr.Asset, '.+@(.+)s-',1,1,'i',1)) as pr.Servername,
Asset,asset_type,check_category,check_desc,custom_attributes,check_id,db_attributes,occurances,organization,result_status,risk
FROM table1 pr
left join table2 ser on pr.Servername = ser.server_component_name

输出错误：

ORA-00923: FROM keyword not found where expected
00923. 00000 -  "FROM keyword not found where expected"
*Cause:    
*Action:
Error at Line: 1 Column: 59

有人能帮帮我吗。

致以敬意，

巴拉特·维卡斯

Answer 1

有几件事不对劲。

• 如果对计算值使用别名，则不要将表别名添加到其中。这不是桌子上的一列。
• 不能将表直接连接到计算值的别名。但是将它封装在子查询中，您就可以使用它了。

示例：

SELECT *
FROM (
 select 
   REGEXP_SUBSTR(Asset, '(.+):.+@',1,1,'i',1) as InstanceName,
   upper(REGEXP_SUBSTR(Asset, '.+@(.+)s-',1,1,'i',1)) as ServerName,
   Asset, asset_type, check_category, check_desc,custom_attributes, 
   check_id, db_attributes, occurances, organization, result_status, 
   risk
 from table1
) pr
left join table2 ser on (pr.ServerName = ser.server_component_name);

免责声明:仅在记事本中测试

一些简化的例子：

-- BAD : an alias using an alias
select UPPER(f.bar) as f.UberBar 
from Foo f

-- BAD : Foo doesn't have an UberBar column
select UPPER(bar) as UberBar 
from Foo 
where UberBar = 'BAR'

-- OK : wrapped in a subquery and using the alias in the outer query
select * from (
   select UPPER(bar) as UberBar 
   from Foo
) as Q 
where Q.UberBar = 'BAR'

-- OK : repeat the calculation of the value in the ON of the JOIN, or the WHERE clause
select UPPER(bar) as UberBar 
from Foo
where UPPER(bar) = 'BAR'