Using alias name in WHERE clause

Question

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"

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?

Not relevant to the question really, but why is it a requirement to have a `.` in the alias name? In your previous question it was mentioned that it makes it necessary to quote the alias name everywhere and exactly match the case used. I can't think of any reason it would be useful. Just curious... — Alex Poole, Feb 05 '14 at 08:15

score 2 · Accepted Answer · answered Feb 05 '14 at 08:12

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%';

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.

score 1 · Answer 2 · answered Feb 05 '14 at 08:11

1

Put the query in a subquery, then you can refer to the aliases in your where clause, e.g.:

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

answered Feb 05 '14 at 08:11

Jeffrey Kemp

59,135
14
106
158

score 0 · Answer 3 · answered Feb 05 '14 at 07:50

0

Replace "PT".".PRNT_PROD_ID" by "PT"."PRNT_PROD_ID" or for a better understanding by pt.prnt_prod_id

answered Feb 05 '14 at 07:50

Cyryl1972

594
3
6

This will not work since the where clause will be evaluated first and by that time reference to pt.prnt_prod_id will not exist. – Ankit Feb 05 '14 at 07:54

Using alias name in WHERE clause

3 Answers3

Related