Subquery return more than 1 value this is not permitted when the subquery follows = , >=, >, <, <=

Question

0.00/5 (No votes)

See more:

when i insert employee data this error occur
sub-query return more than 1 value this is not permitted when the subquery follows = , >=,>,<,<=

What I have tried:

insert INTO Transaction_Employee(Memployee_id,Emp_bio_id,des_id,Department_id,Unit_id,etype_id,payment_id,Leave_id,joining_date,resign_date,leave_start_date,Bank_id)
Values
(
@memployee,@emp_bio_id,(select distinct des_id From Setup_designation where des_name=@desig),(select distinct Department_id from Setup_Department where Depart_name=@deprt),
(select distinct Unit_id from Setup_Unit where unit_name=@unit),(select distinct etype_id from Setup_Enployeetype where ename=@emp_type),
(select distinct payment_id from Setup_Payment_Type where payment_name=@payment),(select distinct Leave_id from Setup_Leave where leave_name=@leavename),
@joining_date,@resign_date,@leave_startdate,(select distinct Bank_id from Setup_Bank where bank_name=@bank_name)
)

Posted 22-Aug-17 21:57pm

Ali Khan

Updated 28-Aug-17 2:32am

Add a Solution

Comments

FranzBe 23-Aug-17 6:53am

you need to check all your subquerys and ensure that they have a result of 1 value each. you are inserting one record in the transaction_employee table. this record contains one 'des_id', so the '(select distinct des_id From Setup_designation where des_name=@desig)' subquery must not result in more than 1 value (and the other subquerys must not either)

Ali Khan 23-Aug-17 7:36am

so what is the right answer?

FranzBe 23-Aug-17 7:44am

I can not look into your database, so I can not tell you. Check your subqueries one by one until you find the one that results in more than one value. I you find it, you need to change 'something'.

Ali Khan 23-Aug-17 7:52am

so if i right select TOP1 ?

FranzBe 23-Aug-17 8:07am

I can't tell you from the little context you did show. I don't want to put more effort in the answer than you put into the question. From my point of view (which may be wrong) you have some error in your logic. e.g. take '(select distinct Bank_id from Setup_Bank where bank_name=@bank_name)' 1) the 'distinct' should not be needed here, you are searching for the id of a bank with a given name. There should be exactly one result. 2) If this query results in more than one row, something is wrong with your database-model, perhaps you need to enforce uniqueness with a unique constraint.

2 solutions

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)

ZurdoDev · Answer 1 · 2017-08-23T03:26:00

As suggested in comments, you need to fix your code so that subqueries do not return more than one record.

For clarity's sake and to make debugging easier, you should declare variables and fill in the variables with lookups first and then use the variables in your insert statement. It will make things a lot easier.

itsmypassion · Answer 2 · 2017-08-28T02:32:00

Solution 2

Use TOP 1 clause in subqueries.

Posted 28-Aug-17 2:32am

itsmypassion