Sql Alias Column Name Needed in Case When Statement

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

SQL Alias Column Name Needed In CASE WHEN Statement

Nest the CASE

SELECT
CASE 
    WHEN CASE
           WHEN p.Program IN ('program1') THEN (date_needed + 10)
           WHEN p.Program IN ('program2') THEN (date_needed + 20)
         END < [NewDate] THEN 'Program On Time' 
    ELSE 'Program Late'
END AS [Modified New Date]

Or use a sub-query, or CTE...

WITH CTE AS(
SELECT
CASE
    WHEN p.Program IN ('program1') THEN (date_needed + 10)
    WHEN p.Program IN ('program2') THEN (date_needed + 20)
END AS [NewDate], date_needed
FROM YourTable)

SELECT
CASE 
    WHEN date_needed < [NewDate] THEN 'Program On Time' 
    ELSE 'Program Late'
END AS [Modified New Date]
FROM CTE

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.

sql Alias Column Name for Use in CASE Statement

The main issue is you cannot reference an alias in the same query. Also you have parentheses and an extra SELECT keyword, making what looks like a subquery where I don't believe you intended one (you do not have a FROM clause).

You can either copy the whole thing again (fixing your query in the process):

SELECT Site, DataSource,
    CASE 
       WHEN DataSource = 'RFQ' THEN 'ChangeOperator' 
       ELSE 'SameOperator' 
    END AS OperatorScenario,

   CASE 
       WHEN 
           CASE 
               WHEN DataSource = 'RFQ' THEN 'ChangeOperator' 
               ELSE 'SameOperator' 
           END = 'ChangeOperator' THEN '1' 
       ELSE '022' 
  END AS OperatorScenario2
from tablename

Or, simplifying:

SELECT Site, DataSource,
    CASE 
       WHEN DataSource = 'RFQ' THEN 'ChangeOperator' 
       ELSE 'SameOperator' 
    END AS OperatorScenario,

   CASE 
       WHEN DataSource = 'RFQ' THEN '1' 
       ELSE '022' 
   END AS OperatorScenario2
from tablename

Or use a CTE (again fixing your query):

WITH cte AS
(
    SELECT Site, DataSource,
    CASE 
       WHEN DataSource = 'RFQ' THEN 'ChangeOperator' 
       ELSE 'SameOperator' 
    END AS OperatorScenario,
    FROM tablename
)
SELECT Site, DataSource, OperatorScenario,
    CASE 
        WHEN OperatorScenario = 'ChangeOperator' THEN '1' 
        ELSE '022' 
    END AS OperatorScenario2
FROM cte

SQL - Alias in CASE statements

You cant use those aliases in the same level as you created them, becuase they are not existing yet.. wrap your query with another select like this:

SELECT * ,
       CASE
            WHEN [RegFinish]<[SuppFinish2] THEN '1'
            ELSE '0'
       END AS [TEST]
FROM (
    SELECT
        [ID],
        CASE
            WHEN [RegFinish] IS NULL THEN ''
            ELSE [RegFinish]
        END AS [RegFinish],
        CASE
            WHEN [SuppFinish] IS NULL THEN ''
            ELSE [SuppFinish]
        END AS [SuppFinish2],
    FROM TABLE)

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

Adding column alias values used in case statement

Just add the sum:

SELECT
    SUM(CASE WHEN FRESHNESS = 'ROTTEN' THEN ORANGES ELSE 0 END) AS Bad_Oranges
    , SUM(CASE WHEN FRESHNESS = 'ROTTEN' THEN APPLES ELSE 0 END) AS Bad_Apples 
    , SUM(CASE WHEN FRESHNESS = 'ROTTEN' THEN ORANGES + APPLES ELSE 0 END) AS Bad_Total
FROM Fruit

Bases on your new requirements it would be:

SELECT 
    SUM(CASE WHEN TYPE = 'FEES' THEN AMOUNT ELSE 0 END) AS Fees 
    , SUM(CASE WHEN TYPE = 'PENALTY' THEN AMOUNT ELSE 0 END) AS Penalty 
    , SUM(CASE WHEN TYPE = 'INTEREST' THEN AMOUNT ELSE 0 END) AS Interest 
    , SUM(CASE WHEN TYPE IN ('INTEREST','PENALTY','FEES') THEN AMOUNT ELSE 0 END) AS Total
FROM MyTable

Using CASE statement with Alias to create another column

You need to nest this in another CASE.

CASE WHEN
        CASE 
            WHEN dbo.ItemSpecs.ConfigProptext LIKE 'Run%'
                THEN (CEILING(dbo.WorkOrderDetails.QtyOrdered / 4)) 
            WHEN dbo.ItemSpecs.ConfigProptext LIKE 'Pure' 
                THEN (CEILING(dbo.WorkOrderDetails.QtyOrdered / 4)) 
            WHEN dbo.ItemSpecs.ConfigProptext LIKE 'Team%' 
                THEN (CEILING(dbo.WorkOrderDetails.QtyOrdered / 4)) 
            ELSE dbo.WorkOrderDetails.QtyOrdered 
        END 
      < 5 
      THEN 'Car'
      ELSE 'VAN'
END as NewCol

Or, another method would be to use a CTE.

;with cte as(
select *,
        CASE 
            WHEN dbo.ItemSpecs.ConfigProptext LIKE 'Run%'
                THEN (CEILING(dbo.WorkOrderDetails.QtyOrdered / 4)) 
            WHEN dbo.ItemSpecs.ConfigProptext LIKE 'Pure' 
                THEN (CEILING(dbo.WorkOrderDetails.QtyOrdered / 4)) 
            WHEN dbo.ItemSpecs.ConfigProptext LIKE 'Team%' 
                THEN (CEILING(dbo.WorkOrderDetails.QtyOrdered / 4)) 
            ELSE dbo.WorkOrderDetails.QtyOrdered 
        END as NumberofItems
from ItemSpecs)

select *,
    case when NumberofItems < 5 then 'car' else 'van' end as NewCol
from cte

Or inline as a derived table

select
   col1,
   col2,
   case when NumberofItems < 5 then 'car' else 'van' end
from
    (select *,
            CASE 
                WHEN dbo.ItemSpecs.ConfigProptext LIKE 'Run%'
                    THEN (CEILING(dbo.WorkOrderDetails.QtyOrdered / 4)) 
                WHEN dbo.ItemSpecs.ConfigProptext LIKE 'Pure' 
                    THEN (CEILING(dbo.WorkOrderDetails.QtyOrdered / 4)) 
                WHEN dbo.ItemSpecs.ConfigProptext LIKE 'Team%' 
                    THEN (CEILING(dbo.WorkOrderDetails.QtyOrdered / 4)) 
                ELSE dbo.WorkOrderDetails.QtyOrdered 
            END as NumberofItems
    from ItemSpecs) x

MySQL CASE statement on a column alias

Expressions in the select-list cannot reference aliases defined in the same select-list. There's technically no defined order of evaluation for the expressions, so no expression can depend on another expression in the same list.

You can either repeat the expression that the alias is based on:

CASE WHEN DAYOFWEEK(IF (
  csm_pat_exe_date.pedate_date IS NULL, 
  ses_date, 
  csm_pat_exe_date.pedate_date)) = 1 THEN 'Sun'
...

but you would have to repeat the same IF() expression in each WHEN clause.

Or else you can define the alias in a subquery and then the outer query can reference the alias

SELECT CASE WHEN DAYOFWEEK(t.sessionDate) = 1 THEN 'Sun' 
      WHEN DAYOFWEEK(t.sessionDate) = 2 THEN 'Mon'
  ... END AS weekday
FROM (
  SELECT IF (
    csm_pat_exe_date.pedate_date IS NULL, 
    ses_date, 
    csm_pat_exe_date.pedate_date) AS sessionDate
  ...
) AS t