Q: SQL Server: subquery returned more than 1 value. This is not permitted when the subquery I have a SQL statement that looks for a certain word between a keyword and a blank space. The query also tells me whether or not the keyword is present in the data. When I run the query, I get the error message about the subquery returning more than 1 value and I understand the error. But I'm not sure how to fix the error.
So I tried the following.
If I run the subquery like this, I do not get any error.
select t.IncidentId, LEFT(tt.CadRemarks, charindex(' ', tt.CadRemarks) - 1) 
as callType
from IncidentMisc t cross apply 
 ( values (substring(CadRemarks, charindex('dispatch level: ', CadRemarks) + 
16, len(CadRemarks))) 
 ) tt(CadRemarks)
 where t.CadRemarks like '%dispatch level: %'

But when I run this, I get the error.
select incidentid,
case 
when CadRemarks like '%dispatch level:%'
then (
select LEFT(tt.CadRemarks, charindex(' ', tt.CadRemarks) - 1) 
as callType
from IncidentMisc t cross apply 
 ( values (substring(CadRemarks, charindex('dispatch level: ', CadRemarks) + 
16, len(CadRemarks))) 
 ) tt(CadRemarks)
 --where t.CadRemarks like '%dispatch level: %'
 )
else ''
end as CallType
from IncidentMisc

Desired Output example.
incidentid      callType
-------------------------
_170039075
_170039076
_170039077
_1700501046      52B01
_1700501048      17B01
_1700501049      67C01

It looks like I am going to have to take a different approach. This question can be closed.

A: SELECT incidentid,
       CASE
           WHEN CadRemarks LIKE '%dispatch level:%'
           THEN
(
    SELECT top 1 (LEFT(tt.CadRemarks, CHARINDEX(' ', tt.CadRemarks) - 1))
    FROM IncidentMisc t
         CROSS APPLY(VALUES(SUBSTRING(CadRemarks, CHARINDEX('dispatch level: ', CadRemarks) + 16, LEN(CadRemarks)))) tt(CadRemarks)
)
           ELSE ''
       END AS CallType
FROM IncidentMisc;

this should work, but if you clarify more I can check whether this suits all your scenarios.
