在WHERE子句中使用别名

问题描述

我正在执行以下查询，并对所有列使用别名.我用命名了别名.因为这是必需的.现在，我想直接在where子句中引用别名，我的操作如下所示:

I am executing the below query and using alias name for all columns. I have named alias with a . since that is a requirement. now, I want to refer to the alias name directly in the where clause and i do is as given below:

SELECT pt.prod_desc AS"PROD_DESC",
 (
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)         AS"PROD_LEVEL",
prod_id      AS "PROD_ID",
isactive     AS "IsActive",
updt_usr_sid AS "UPDT_USR_SID",
updt_ts      AS "UPDT_TS",
(
CASE
WHEN pt.prod_level='5'
THEN parent_prod_id
ELSE NULL
  END) AS ".SUB_PROD_ID",
(
 CASE
  WHEN pt.prod_level='5'
  THEN
  (SELECT prod_desc FROM dims_prod_type A WHERE A.prod_id= pt.parent_prod_id
  )
  ELSE 'N/A'
 END ) AS ".SUB_PROD_DESC",
 (
CASE
WHEN pt.prod_level='4'
THEN parent_prod_id
WHEN pt.prod_level='5'
THEN
  (SELECT parent_prod_id
  FROM dims_prod_type A
  WHERE A.prod_id= pt.parent_prod_id
   )
 ELSE NULL
   END) AS ".PRNT_PROD_ID",
(
 CASE
 WHEN pt.prod_level='4'
THEN
   (SELECT prod_desc FROM dims_prod_type A WHERE A.prod_id=pt.parent_prod_id
   )
  WHEN pt.prod_level='5'
THEN
   (SELECT prod_desc
   FROM dims_prod_type A
  WHERE A.prod_id IN
    (SELECT B.parent_prod_id
    FROM dims_prod_type B
    WHERE b.prod_id=pt.parent_prod_id
    )
  )
 ELSE 'N/A'
 END)AS ".PRNT_PROD_DESC"
FROM dims_prod_type pt
WHERE pt.".PRNT_PROD_ID" like 'A%';

但是，当我执行此操作时，出现以下错误:

However when i am executing this I get the following error:

    SQL Error: ORA-00904: "PT".".PRNT_PROD_ID": invalid identifier
     00904. 00000 -  "%s: invalid identifier"

我知道SQL首先执行where子句，因此这就是错误的原因.但是我该如何解决呢?有什么建议吗?

I know that SQL executes the where clause first and hence that is the reason of the error. But how do i fix this? Any suggestions please?

推荐答案

您已经知道您不能在where子句中使用别名，但这仅适用于相同级别的SQL.您可以将查询包装在外部查询中:

You already know you can't use the alias in the where clause, but that only applies in the same level of SQL. You can wrap your query in an outer query:

SELECT *
FROM (
  SELECT pt.prod_desc AS"PROD_DESC",
  ...
   END)AS ".PRNT_PROD_DESC"
  FROM dims_prod_type pt
)
WHERE ".PRNT_PROD_ID" like 'A%';

唯一的选择是重复在where子句中生成该列的整个case，这对于如此复杂的操作是不愉快的.

The only alternative would be to repeat the whole case that generates that column in the where clause, which would be unpleasant with something so complicated.

这篇关于在WHERE子句中使用别名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！