Re-Use Aliased Field in SQL Select Statement

How to reuse column alias in another column within same select statement?

In SQL Server, you can use APPLY to define column aliases in the FROM clause -- a good place, in my opinion:

select pd.id v.value_1,
       (Case when value_1 in ('test1', 'test2') Then '0'
             else value_1
        End) as value_2
from pd cross apply
     (values ( case . . . )
     ) v(value_1);

Notes:

Don't use identifier names that need to be escaped, hence value_1 instead of value 1.
value_1 appears to be string. The case expression for value_2 should return a string, so '0' instead of 0.
I define table aliases using as after the expression. This is the SQL standard, but there is nothing per se wrong with using =, which is SQL Server specific syntax.

Re-use aliased field in SQL SELECT statement

A workaroud would be to use a sub-query:

SELECT
  FieldA,
  FieldA + 20 AS FieldB
FROM (
  SELECT 
    (CASE WHEN ...) AS FieldA
  FROM Tbl
) t

To improve readability you could also use a CTE:

WITH t AS (
  SELECT 
    (CASE WHEN ...) AS FieldA
  FROM Tbl
)
SELECT
  FieldA,
  FieldA + 20 AS FieldB
FROM
  t

Reuse of alias in a query

In order to access to fname, you need to put your query inside a sub-query as below:

SELECT K.fname, K.lname, K.fname as NewFname 
FROM(    
      SELECT 'Santosh' as fname, 'Jadi' as lname
) as K

Why is mySQL able to resolve these column aliases when normally one can't reuse an alias?

This looks like a parsing bug to me (in other databases, you would get an error on the first two queries).

I can guess at what is happening. MySQL is parsing the subquery (select c1). It doesn't find c1 in the subquery, so it starts looking for references in outer queries.

According to the rules of SQL, it should only be looking at columns in the from clause. However, MySQL also seems to be looking at column aliases.

Although I would call this a bug, MySQL seems to consider it a feature. (Yet another example of MySQL considering a "bug" a "feature".) Consider this query:

SELECT CONCAT((SELECT c1), 2), CONCAT(a, b) AS c1
FROM (SELECT 'a' a, 'b' b, 'c' c UNION ALL 
      SELECT '1', '2', '3') t1;

And the error it generates:

Reference 'c1' not supported (forward reference in item list)

The error suggests that MySQL is intentionally parsing "backwards" references.

For the record, I would never depend on this functionality. It is not only a violation of the ANSI standard and unique behavior among databases. It is confusing. Consider this little modification:

SELECT CONCAT(a, b) AS c1, CONCAT((SELECT c1), 2)
FROM (SELECT 'a' a, 'b' b, 'c' c UNION ALL 
      SELECT '1', '2', '3') t1 CROSS JOIN
     (SELECT 'abcdef' as c1) x;

Which c1 does the query resolve to? I'll let you figure that out. And this doesn't even take into account that c1 could be a variable.

SQL Server reuse aliases in subqueries

I think that following script can explain you everything.

SELECT 1,1,GETDATE()
INSERT INTO @t ( Id, UserId, TranDate )
SELECT 2,1,GETDATE()
INSERT INTO @t ( Id, UserId, TranDate )
SELECT 3,1,GETDATE()

SELECT tx.Id/*main alias*/,
       tx1.Id /*First subquery alias*/,
       tx2.Id /*Second subquery alias*/,
       (SELECT Id FROM @t txs /*alias only in this one subquery/must be different from main if you want use main alias in it...*/ 
        WHERE txs.Id = tx.Id+2 /*here is used main value = subquery value+2*/) AS Id
FROM @t tx /*main*/
JOIN (SELECT * 
      FROM @t tx 
      WHERE tx.Id =  1 /*this one using subquery values + you are not able to use here main value*/ 
      ) tx1 --alias of subquery
      ON tx.Id = tx1.Id /*here is used main value = subquery value*/
CROSS APPLY (SELECT TOP 1 * 
             FROM @t txc /*This one must be different from main if you want use it to comparison with main*/
             WHERE txc.Id > tx.Id /*this one using subquery value > main value*/ 
             ) tx2 --alias of subquery
WHERE tx.Id = 1 AND /*Subquery alias canot reference on First subquery value*/
      tx1.Id = 1 AND/*Subquery alias*/
      tx2.Id = 2 /*Subquery alias*/

It means that yea, it could be reused, but only if you dont want compare main / sub, because if you reuse it and for example you try to do folowing statement in subquery tx.Id > tx.Id It causes that only values in subquery will be compared. In our example it causes that you dont get anything because you comaring values in same row...

Reusing alias for an expression in postgresql

As you observe, what you want to do is not possible. Instead, you can use a lateral join to calculate values in the FROM clause:

select v.full_name, Length(v.full_name) as len
from customer c cross join lateral
     (values (first_name || ' ' || last_name)
     ) v(full_name)
order by len;