Using Alias In When Portion of a Case Statement in Oracle SQL

by

No, you can’t refer to the alias elsewhere in the same level of select, other than in the order by clause, because of when Oracle assigns it internally.

From the documentation (emphasis added):

You can use a column alias, c_alias, to label the immediately
preceding expression in the select list so that the column is
displayed with a new heading. The alias effectively renames the select
list item for the duration of the query. The alias can be used in the
ORDER BY clause, but not other clauses in the query.

You would need to use an inner query, something like:

select "Id",
    case "Id"
        when 3
        then 'foo'
        else 'bar'
    end AS "Results"
from (
    select TABLEA.SomeIDNumber AS "Id",
    from TABLEA
);

More Related Contents:

  1. Using an Alias in a WHERE clause
  2. Are a CASE statement and a DECODE equivalent?
  3. ORA-00972 identifier is too long alias column name
  4. Return number from Oracle Select statement after parsing date
  5. Why doesn’t Oracle SQL allow us to use column aliases in GROUP BY clauses?
  6. Search All Fields In All Tables For A Specific Value (Oracle)
  7. Select First Row of Every Group in sql [duplicate]
  8. Oracle: how to UPSERT (update or insert into a table?)
  9. Get top results for each group (in Oracle)
  10. Comparing Dates in Oracle SQL
  11. Oracle SQL query: Retrieve latest values per group based on time [duplicate]
  12. ORA-12560: TNS:protocol adaptor error
  13. GROUP BY / aggregate function confusion in SQL
  14. Oracle “(+)” Operator
  15. How do you create a temporary table in an Oracle database?
  16. What exactly do quotation marks around the table name do?
  17. How can I get column names from a table in Oracle?
  18. How to store only time; not date and time?
  19. How does GROUP BY work?
  20. What’s the difference between RANK() and DENSE_RANK() functions in oracle?
  21. How to get the last row of an Oracle table
  22. Select without a FROM clause in Oracle
  23. Is there any way to flush output from PL/SQL in Oracle?
  24. SELECT from table with Varying IN list in WHERE clause
  25. How to insert a string which contains an “&”
  26. Subtracting Dates in Oracle – Number or Interval Datatype?
  27. Retrieve Oracle last inserted IDENTITY
  28. Using bind variables with dynamic SELECT INTO clause in PL/SQL
  29. Why are aggregate functions not allowed in where clause
  30. Insert into from CTE

Leave a Comment