Error When Using WHERE Clause in SPROC Script

Question

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.

Answer 1

Hi @Emilee Weir ，

Welcome to microsoft TSQL Q&A forum!

Could you please share us your table structure (CREATE TABLE …) and some sample data(INSERT INTO …)
along with your expected result? So that we’ll get a right direction and make some test.You can provide a minimal example for our reference.

If the table vw_ALL_RECORDS is included after the from clause,maybe you can also try:

where  (cast([STR_ENG1] as int) + cast([STR_ENG2] as int) +   
       cast([STR_ENG3] as int) +   
       ...   
       cast([STR_WING_ROT] as int)= 1  

Regards
Echo

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

The query in your WHERE clause returns as many rows there are in vw_all_records.. Then you try to compare that to a single scalar value. You can't do that, and therefore you get the error.

Now, what you should have written instead, I can't say, because I don't see the rest of your query and I don't your tables.

Maybe it should be:

 where
     ((select
         cast([STR_ENG1] as int) + 
         ...
         cast([STR_WING_ROT] as int)
     from vw_ALL_RECORDS ar
     ar.keycol = somethingelse.keycol) = 1)

so that you get a correlated subquery, which evaluated for every row in the rest of the query.