Ephedra Ephedra - 4 years ago 102
SQL Question

WHERE Clause does not accept just defined column

This Code does not work:

SELECT
(
SELECT [T_Licence].[isInstalled]
FROM [T_Licence]
WHERE [T_Licence].[System] = [T_System].[ID]
AND [T_Licence].[Software] = 750
) AS [IsInstalled] ,*
FROM [T_System]
WHERE [IsInstalled] = 1


I have to do it this way, but this makes the whole code so complicated. I really dont want that:

SELECT
(
SELECT [T_Licence].[isInstalled]
FROM [wf_subj_all].[T_Licence]
WHERE [T_Licence].[System] = [T_System].[ID]
AND [T_Licence].[Software] = 750
) AS [IsInstalled] ,*
FROM [wf_subj_it].[T_System]
WHERE
(
SELECT
(
SELECT [T_Licence].[isInstalled]
FROM [wf_subj_all].[T_Licence]
WHERE [T_Licence].[System] = [T_System].[ID]
AND [T_Licence].[Software] = 750
)
) = 1


Is there any way to do it like shown in the first code snippet?
So that the code stays somehow readeble.

thx very much

Answer Source

Try this one -

SELECT *
FROM wf_subj_it.T_System s
CROSS APPLY (
    SELECT /*TOP(1)*/ t.isInstalled   
    FROM wf_subj_all.T_Licence t
    WHERE t.[System]  = s.ID   
    AND t.Software = 750
) t
WHERE t.isInstalled = 1
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download