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

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

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
);

SQL: Alias Column Name for Use in CASE Statement

I think that MySql and MsSql won't allow this because they will try to find all columns in the CASE clause as columns of the tables in the WHERE clause.

I don't know what DBMS you are talking about, but I guess you could do something like this in any DBMS:

SELECT *, CASE WHEN a = 'test' THEN 'yes' END as value FROM (
   SELECT col1 as a FROM table
) q

Alias for multiple case statements in oracle

If you mean this, then yes - see the FOR TEMP ... line and aliases being used.

SQL> SELECT *
  2    FROM (SELECT CASE
  3                    WHEN deptno = 10 THEN 'FIRST'
  4                    WHEN deptno = 20 THEN 'SECOND'
  5                    WHEN deptno = 30 THEN 'FOURTH'
  6                    ELSE '40'
  7                 END AS temp,
  8                 dname
  9            FROM dept)
 10         PIVOT (COUNT (dname) FOR temp IN ('FIRST' as A, 'SECOND' as B, 'THIRD' as C));

         A          B          C
---------- ---------- ----------
         1          1          0

SQL>

Alias column name for Use in CASE Statement

You cannot GROUP BY the alias,

Try

SELECT a.ID as AID, a.Amt as AAmt
FROM
     (SELECT
          ID,                       
          CASE
              WHEN Col1 = 0
                 THEN SUM (Col2 + Col3)
                 ELSE 0
          END AS Amt
      FROM  table1
      GROUP BY ID, Col1) AS a

If you have a look at SQL Query Order of Operations you will note the the order of operations are

1.FROM clause
2.WHERE clause
3.GROUP BY clause
4.HAVING clause
5.SELECT clause
6.ORDER BY clause

This means that the GROUP BY is processed before the SELECT, which is where you defined the alias.

This also explains why you can order by an alias.

Oracle SQL: Using same alias In CASE statement to select in one case one column, in the other two columns and another three with the same alias

Looking at your requirement, hope below is what you are expecting.

SELECT CASE
         WHEN att.list_value = 'Sub_Paragraph' THEN
          att.column_with_subpar_value
       END AS sub_paragraph,
       CASE
         WHEN att.list_value in ('Sub_Paragraph', 'Paragraph') THEN
          att.column_with_par_value
       END AS paragraph,
       CASE
         WHEN att.list_value in ('Sub_Paragraph', 'Paragraph', 'Article') THEN
          att.column_with_article_value
       END AS article
  FROM att
 WHERE id = 1;

Can i use 'alias' AS in CASE Statement PostgreSQL

You could use a CTE

WITH CTE as (
SELECT 
    id
    , quantity
    , unit_price,
    (antity * unit_price) as price
FROM OrderDetails
)
SELECT 
    id
    , quantity
    , unit_price 
    , price    
CASE
    WHEN price > 5000 THEN price * 0.2
    WHEN price > 3000 THEN price * 0.15 
    ELSE null 
END 

AS discount

FROM OrderDetails;

Using a table alias in a case statement in a where clause

This has nothing to do with the case statement. The issue is the scoping rules for correlated subqueries. They only nest one level deep. Here is another question with this problem.

For your example, you don't need nested subqueries. You can just do:

where (CASE when emp.department = 'C'
            then (select count(*) 
                  from tbl_emp_department ted
                  where ted.department = emp.department
                 )
            when emp.department = 'C'
            then (select count(*)
                  from tbl_emp_department ted
                  where ted.department = emp.department and ted.id = 7
                 )
       END)  > 0

However, because your query doesn't really make sense (the same conditions for the two when clauses, I suspect that your actual query may be more complicated. You may have to find another approach to do what you want, using explicit joins and aggregations or using analytic functions.

Alias Column name in mutiple case statement

You can't use column aliases in the same SELECT clause. You have two choices:

Use a subquery:

SELECT Alias1,
       CASE
            WHEN Alias1 = FieldA1 THEN FieldA0
            WHEN Alias1 = FieldB1 THEN FieldA1
            ELSE NULL
       END AS Alias2 
FROM (
    SELECT CASE 
                WHEN FieldA = 'TestA' THEN FieldA1
                WHEN FieldB = 'TestB' THEN FieldB1
                ELSE NULL
           END AS Alias1,
           FieldA1
           FieldB1
    ...)

or you can repeat the logic that you used in the first CASE:

SELECT CASE 
            WHEN FieldA = 'TestA' THEN FieldA1
            WHEN FieldB = 'TestB' THEN FieldB1
            ELSE NULL
       END AS Alias1,
       CASE 
            WHEN FieldA = 'TestA' THEN FieldA0
            WHEN FieldB = 'TestB' THEN FieldB0
            ELSE NULL
       END AS Alias2

SQL: Using an alias in order case statement

either put it in a CTE and then order by...

; with cteQuery as (Select *, column1+column2 as alias1 from complaints)
SELECT * from cteQuery Order by 
Case 
   When @param1 = 'filedate' then filedate
   When @param1 = 'calc' then alias1
 End

or just duplicate what the alias actually refers to...

Select *, column1+column2 as alias1
 From complaints
 Order by 
 Case 
   When @param1 = 'filedate' then filedate
   When @param1 = 'calc' then column1+column2
 End

How to use Alias in Where clause?

You can't reference the column alias in the WHERE clause - your options are:

replicate the CASE statement in the WHERE clause

use a subquery:

PROCEDURE P_LOAD_EXPIRED_ACCOUNT(pDayDiff NUMBER,
                                 ExpiredCur OUT MEGAGREEN_CUR)
IS
BEGIN

   OPEN ExpiredCur FOR
   SELECT x.account_name,
          x.service_type, 
          x.expired_date
     FROM (SELECT s.account_name,
                  s.service_type, 
                  CASE 
                     WHEN s.service_type = 1 THEN ADD_MONTHS(ACTIVATED_DATE,3)
                     WHEN s.service_type = 2 THEN ADD_MONTHS(ACTIVATED_DATE,6)
                     WHEN s.service_type = 3 THEN ADD_MONTHS(ACTIVATED_DATE,12)        
                  END AS EXPIRED_DATE
             FROM SUBSCRIBERS s) x
    WHERE x.expired_date - CURRENT_DATE < pDayDiff;

END;

Oracle 9i+

WITH summary AS (
  SELECT s.account_name,
         s.service_type, 
         CASE 
            WHEN s.service_type = 1 THEN ADD_MONTHS(ACTIVATED_DATE,3)
            WHEN s.service_type = 2 THEN ADD_MONTHS(ACTIVATED_DATE,6)
            WHEN s.service_type = 3 THEN ADD_MONTHS(ACTIVATED_DATE,12)        
         END AS EXPIRED_DATE
    FROM SUBSCRIBERS s)
   SELECT x.account_name,
          x.service_type, 
          x.expired_date
     FROM summary x
    WHERE x.expired_date - CURRENT_DATE < pDayDiff;

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