How do i...solve this

Question

1.00/5 (2 votes)

See more:

SQL-Server-2014

i wrote this sub query but it give me this message:Msg 512, Level 16, State 1, Line 1 Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

What I have tried:

( select (d.ProductID) from v_accounts c,t_DormantAccounts d where (c.ProductID like ('sav9%') or c.ProductID like ('cur9%'))
and c.AccountID=d.AccountID and c.OurBranchID=d.OurBranchID ) as OriginalBranchID,

Posted 11-Sep-18 20:06pm

Member 13981360

Updated 11-Sep-18 23:12pm

Add a Solution

Comments

Member 13978939 12-Sep-18 6:09am

Can you please provide your sample data with expected output data

2 solutions

Solution 2

(select top 1 (d.ProductID) from v_accounts c,t_DormantAccounts d where (c.ProductID like ('sav9%') or c.ProductID like ('cur9%'))
and c.AccountID=d.AccountID and c.OurBranchID=d.OurBranchID ) as OriginalBranchID

Posted 11-Sep-18 23:12pm

User-10689749

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Accepted Answer · 2018-09-11T20:29:00

Look at your query:

SQL

SELECT (d.ProductID) FROM v_accounts c, t_DormantAccounts d
   WHERE (c.ProductID LIKE ('sav9%') 
          OR c.ProductID LIKE ('cur9%'))
     AND c.AccountID=d.AccountID 
     AND c.OurBranchID=d.OurBranchID

Run that in isolation (SSMS is good for that) and it will return more than one row.
SQL can't work with more than one result where you are using that as a subquery - so you need to either alter that to return a single value:

SQL

SELECT TOP 1 (d.ProductID) FROM ...
   ORDER BY ...

Or work out a query that will only ever return a single value.

The only other alternative is to look at the whole query that is a part of and work out what you are trying to achieve better, possibly using JOIN.

But we can't do any of that for you: we don't have any access to your DB, the "outer query", or any idea what you are expecting to get as results.