Subquery returns more than 1 row

[Jassim Rahma]

Hi, Why I am getting the above error for this stored procedure?

CREATE DEFINER=`root`@`192.168.1.%` PROCEDURE `sp_open_file`(IN param_file_no int, IN param_clinic_id int, IN param_doctor_id int, IN param_account_category int, IN param_account_id int)
BEGIN
DECLARE param_patient_account_number BIGINT;
DECLARE param_price_list_id int;
DECLARE param_account_number BIGINT;

SET param\_patient\_account\_number = (SELECT account\_number FROM patients WHERE file\_no = param\_file\_no);

IF (param\_account\_category  = 1) THEN
    SET param\_account\_number = param\_patient\_account\_number;
ELSE
    SET param\_account\_number = (SELECT account\_number FROM accounts WHERE account\_id = param\_account\_id);
END IF;

SET param\_price\_list\_id = (SELECT price\_list\_id FROM accounts WHERE account\_number = param\_account\_number);

INSERT INTO visits (file\_no, clinic\_id, doctor\_id, account\_category, account\_number, price\_list\_id) VALUES (param\_file\_no, param\_clinic\_id, param\_doctor\_id, param\_account\_category , param\_account\_number, param\_price\_list\_id);
UPDATE patients SET file\_is\_open = TRUE;

END

Technology News @ www.JassimRahma.com

[Lost User]

..because one of the three subqueries returns more than one row.

(SELECT account_number FROM patients WHERE file_no = param_file_no);
(SELECT account_number FROM accounts WHERE account_id = param_account_id);
(SELECT price_list_id FROM accounts WHERE account_number = param_account_number);

Meaning that the table holds more than a single record with the requested Id.

Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^] They hate us for our freedom![^]

[Lost User]

Quote:

Meaning that the table holds more than a single record with the requested Id.

True, this is the main reason for such errors. Many resolution are available for this errors. 1) Use Top 1 keyword i.e.

(SELECT TOP 1 account_number FROM patients WHERE file_no = param_file_no);
(SELECT TOP 1 account_number FROM accounts WHERE account_id = param_account_id);
(SELECT TOP 1 price_list_id FROM accounts WHERE account_number = param_account_number);

2. Some of your data require 1 or more condition (filter) in your sql query. 3) If any duplicate entry found, Please remove.

Maulik Dusara Sr. Sofware Engineer

[Lost User]

MaulikDusara wrote:

True, this is the main reason for such errors. Many resolution are available for this errors.

1. Use Top 1 keyword i.e.

That's assuming that there's more of those records allowed in the table, and being an "Id", this COULD lead to an inconsistent database - exaggerating the problem.

Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^] They hate us for our freedom![^]

[Mycroft Holmes]

I agree with Eddy, while top 1 fixes this problem it ignores the underlying data problem that the user was expecting 1 record per ID and is not getting the correct result.

Never underestimate the power of human stupidity RAH

[Lost User]

Quote:

not getting the correct result.

I also agree and I also mentioned that

Quote:

Many resolution are available for this errors

Please also check my other resolutions.

Maulik Dusara Software Engineer/Team Leader

[Lost User]

MaulikDusara wrote:

Please also check my other resolutions.

That's the point; he should not "try resolutions", but analyze the bug; if there are multiple records coming back on a key-field selection, chances are that there's an inconsistency. Selecting the "top 1" might hide that problem a little longer.

Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^] They hate us for our freedom![^]