question

EmileeWeir-2379 avatar image
0 Votes"
EmileeWeir-2379 asked EchoLiu-msft commented

Error When Using WHERE Clause in SPROC Script

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.

sql-server-transact-sql
· 4
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.


Check if this works:

 where cast([STR_ENG1] as int) + [STR_ENG2] + [STR_ENG3] + etc... = 1
0 Votes 0 ·

No, each category is returning "INVALID COLUMN NAME" errors. That was why I tried writing it as a SELECT statement.

0 Votes 0 ·

Then maybe you should adjust the SELECT subquery to return a single row (adding something like WHERE id = current_id). Perhaps a JOIN can be used too. Or show more details about your query and tables.


0 Votes 0 ·

Do you have any update?
Please also remember to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread.

Echo

0 Votes 0 ·
ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

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.


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

EchoLiu-msft avatar image
0 Votes"
EchoLiu-msft answered

Hi @EmileeWeir-2379

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.


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.