tsql - How to use CASE alias in WHERE CLAUSE?

Question

I'm trying to put the "val" into where clause, but it returns error:

    Select ff.FormID, ff.FieldID, ff.FieldName, ff.Title, 
    ff.DefaultValue, fv.Value, 
    val = case fv.Value when null then cast(ff.DefaultValue as nvarchar) else fv.Value end,
    ff.DataType from
    (SELECT FormID, FieldID, FieldName, Title, DataType, DefaultValue FROM FormFields where FormID = '766A38D8-8058-42C6-AC46-A18C00D3C1DC' and DEL = 0) as ff
    left join
    (select FormID, FieldID, Value from FormValues where FormID = '766A38D8-8058-42C6-AC46-A18C00D3C1DC' and ItemID = 'FD63CCA2-C95F-4AB4-B84B-A220017027E7' and DEL = 0) as fv
    on ff.FormID = fv.FormID and ff.FieldID = fv.FieldID
where val is not null


Msg 207, Level 16, State 1, Line 9
Invalid column name 'val'.

Any kind help is appreciated :)

Answer 1

It is not allowed to use aliases in where clause (in sql server), because the order of logical execution of the query is as follows:

1. FROM
2. ON
3. JOIN
4. WHERE
5. GROUP BY
6. WITH CUBE or WITH ROLLUP
7. HAVING
8. SELECT
9. DISTINCT
10. ORDER BY
11. TOP

As you can see WHERE clause is being executed before SELECT, that's why you cannot refer to aliases from SELECT clause

Try:

Select ff.FormID, ff.FieldID, ff.FieldName, ff.Title, 
ff.DefaultValue, fv.Value, 
val = case fv.Value when null then cast(ff.DefaultValue as nvarchar) else fv.Value end,
ff.DataType from
(SELECT FormID, FieldID, FieldName, Title, DataType, DefaultValue FROM FormFields where FormID = '766A38D8-8058-42C6-AC46-A18C00D3C1DC' and DEL = 0) as ff
left join
(select FormID, FieldID, Value from FormValues where FormID = '766A38D8-8058-42C6-AC46-A18C00D3C1DC' and ItemID = 'FD63CCA2-C95F-4AB4-B84B-A220017027E7' and DEL = 0) as fv
on ff.FormID = fv.FormID and ff.FieldID = fv.FieldID
where case fv.Value when null then cast(ff.DefaultValue as nvarchar) else fv.Value end is not null