Getting subquery returned more than 1 record error

Toad for SQL Server
1

using Toad For SQL Server 5.0.2.612

I have a query that I am running that has a number of subqueries (functions) that return fields however when I run my query I am getting an error that is something like (this is paraphrasing, I dont have the exact text in front of me

subquery returned more than 1 record, can't have this when you use "=", "<"...

how do you troubleshoot this? also it lists the “line” number of the error, is that the record where the error occurs?

by matter of elimination (meaning commenting out the function that I think would cause this) I figured out which function is causing the issue.

I plan on taking the PrimaryKeyID of the “line” listed in the error and running the subquery/function to see what’s going on.

Does this sound correct?

thanks

MC

2

Hi MyronCope,

It doesn’t sound like a toad’s issue =)
I would recommend you to inspect your sub-query since error message tells you that returned set contains more than one record and it completely screwed up a logic of your query.

3

MC, I get this al the time, when I improperly code the subquery. So I see this
as a SQL coding question. Therefore, help my understand what the subquery is
suppose to return and what it really returns. Meaning run the subquery and see
if it returns more than one row. Then look at the outer query and determine if
it structured to handle multiple rows returned. It may be just and understanding
of the error message and a little debugging of your code might resolve the
problem.

If you like please send the query for further discussion.

Hank Freeman

Senior SQL Server DBA/Data & Systems Architect

hfreeman@msn.com

678-414-0090 my cell Primary

hank.freeman50 via Skype
image001.gif

4

MC, I get this al the time, when I improperly code the subquery. So I see this
as a SQL coding question. Therefore, help my understand what the subquery is
suppose to return and what it really returns. Meaning run the subquery and see
if it returns more than one row. Then look at the outer query and determine if
it structured to handle multiple rows returned. It may be just and understanding
of the error message and a little debugging of your code might resolve the
problem.

If you like please send the query for further discussion.

Hank Freeman

Senior SQL Server DBA/Data & Systems Architect

hfreeman@msn.com

678-414-0090 my cell Primary

hank.freeman50 via Skype
image002.jpeg