I have written a query for select some results from different tables with a union all. After the from you can use the where. In that where I want to use a column that I 'made' in the query. How can I do this?
example
SELECT
[column1] AS 'K1',
ISNULL([column2], 'else') + ' ' + ISNULL([column3], 'else') AS 'K2'
FROM
[table]
FULL JOIN
[table] ON A.Name = C.AccountIdName
WHERE
A.Name IS NOT NULL
AND K1 IS NOT NULL
Is that possible to take the K1 and set in into the where? Or is there an other option?
UPDATE: New example
SELECT
[column1] AS 'K1',
ISNULL([column2], 'else') + ' ' + ISNULL([column3], 'else') AS 'K2'
FROM
[table]
FULL JOIN
[table] ON A.Name = C.AccountIdName
WHERE
A.Name IS NOT NULL
**AND K1 IS NOT NULL
AND K3 IS NOT NULL**
UNION ALL
SELECT
[column1] AS 'K1',
ISNULL([column2], 'else') + ' ' + ISNULL([column3], 'else') AS 'K2'
FROM
[table]
FULL JOIN
[table] ON A.Name = C.AccountIdName
WHERE
A.Name IS NOT NULL
**AND K2 IS NOT NULL
AND K4 IS NOT NULL**
As you can see I want in the first select K1 en K3 and in the second I want K2 end K4
I hope you can solve this problem too, this was a good solution.
SELECT * FROM
(
SELECT [column1] as 'K1' , ISNULL([column2], 'else') + ' ' + ISNULL([column3], 'else') as 'K2'
FROM [table] full join [table] on A.Name = C.AccountIdName where A.Name IS NOT NULL
)
res WHERE res.K1 IS NOT NULL
You can use it, but as a result of your subquery
SELECT * FROM
(
SELECT [column1] as 'K1' , ISNULL([column2], 'else') + ' ' + ISNULL([column3], 'else') as 'K2'
FROM [table] full join [table] on A.Name = C.AccountIdName where A.Name IS NOT NULL
)
res WHERE res.K1 IS NOT NULL
UPDATED
SELECT * FROM
(
SELECT [column1] as 'K1' , ISNULL([column2], 'else') + ' ' + ISNULL([column3], 'else') as 'K2'
FROM [table] full join [table] on A.Name = C.AccountIdName where A.Name IS NOT NULL
)
res1 WHERE res1.K1 IS NOT NULL AND res1.K3 IS NOT NULL
UNION ALL
SELECT * FROM
(
SELECT [column1] as 'K1' , ISNULL([column2], 'else') + ' ' + ISNULL([column3], 'else') as 'K2'
FROM [table] full join [table] on A.Name = C.AccountIdName where A.Name IS NOT NULL
)
res2 WHERE res2.K2 IS NOT NULL AND res2.K4 IS NOT NULL //BUT DOES IT HAS K4???