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 :)
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:
- FROM
- ON
- JOIN
- WHERE
- GROUP BY
- WITH CUBE or WITH ROLLUP
- HAVING
- SELECT
- DISTINCT
- ORDER BY
- 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