I am editing a new SPROC to filter FAA bird strike data based on user inputs. I am attempting to modify one of my existing scripts for this n ew SPROC so that bird strike incidents where multiple vehicle locations were hit are filtered out. This is what I tried adding before the GROUPBY and ORDERBY clauses (note that this is an excerpt from a 467-line script):
where
((select
cast([STR_ENG1] as int) +
cast([STR_ENG2] as int) +
cast([STR_ENG3] as int) +
cast([STR_ENG4] as int) +
cast([STR_FUSE] as int) +
cast([STR_LG] as int) +
cast([STR_LGHTS] as int) +
cast([STR_NOSE] as int) +
cast([STR_OTHER] as int) +
cast([STR_PROP] as int) +
cast([STR_RAD] as int) +
cast([STR_TAIL] as int) +
cast([STR_WINDSHLD] as int) +
cast([STR_WING_ROT] as int)
from vw_ALL_RECORDS) = 1)
Each category is a location on the incident vehicle (originally a bit data type); a 1 on any category indicates that a bird struck that part of the incident vehicle. Therefore, if the sum of all of the categories is greater than 1, then multiple locations were hit and that incident can be filtered out.
And this is the error my edit keeps returning:
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
Is there another way to apply this restriction? This is the only way I can think of.