在Oracle中的select语句中使用select

安奇

我正在尝试执行以下查询:

SELECT pt.prod_desc"Description",
(
 CASE
  WHEN pt.prod_level='2'
THEN 'Product'
WHEN pt.prod_level='4'
THEN 'Sub-Product'
WHEN pt.prod_level='5'
THEN 'Service'
ELSE 'N/A'
END)"Level", prod_id"CKC ID", isactive"Active", updt_usr_sid"Last Updated By", 
updt_ts"LAst Updated Date",
(CASE WHEN pt.prod_level='5' THEN parent_prod_id ELSE null END)"Parent Sub-Product CKC ID",
(CASE WHEN Level='Service' THEN (Select prod_desc from dims_prod_type where prod_id= parent_prod_id) ELSE 'N/A' END)
FROM dims_prod_type pt
  ORDER BY prod_desc;

我收到以下错误:错误报告:

SQL Error: ORA-01788: CONNECT BY clause required in this query block
01788. 00000 -  "CONNECT BY clause required in this query block"
*Cause:    
*Action:

我将查询修改为以下内容:

 SELECT pt.prod_desc"Description",
 (
 CASE
  WHEN pt.prod_level='2'
THEN 'Product'
WHEN pt.prod_level='4'
THEN 'Sub-Product'
WHEN pt.prod_level='5'
THEN 'Service'
ELSE 'N/A'
END)"Level", prod_id"CKC ID", isactive"Active", updt_usr_sid"Last Updated By", 
updt_ts"LAst Updated Date",
(CASE WHEN pt.prod_level='5' THEN parent_prod_id ELSE null END)"Parent Sub-Product CKC   ID",
(CASE WHEN Level='Service' THEN (Select prod_desc from dims_prod_type where connect by prior prod_id=parent_prod_id) ELSE 'N/A' END)
FROM dims_prod_type pt
  ORDER BY prod_desc;

仍然出现相同的错误。

文森佐·马焦

您在这里犯了两个错误:

  1. 您使用levelOracle保留关键字调用了列
  2. level是在使用case / when创建的上下文中引用列:该列在该范围中不作为独立列存在,并且不能在where / group by子句中引用,只能在order by子句中引用

本文收集自互联网,转载请注明来源。

如有侵权,请联系 [email protected] 删除。

编辑于
0

我来说两句

0 条评论
登录 后参与评论

相关文章