CODEWITHSUNDEEP
sql-server
GIRIJA
GIRIJA

Reputation: 15

Subquery returned more than 1 value errors

SELECT 
    CASE CR.claim_status 
       WHEN 'RC' 
          THEN (SELECT ins_clause_no 
                FROM tbl_rej_code_master RCM 
                WHERE CR.s_no IN (SELECT s_no 
                                  FROM claim_rejection_code_trans CRCT 
                                  WHERE CRCT.irrmp_sr_no = RCM.rej_srno)) 
    END 
FROM   
    tblclaimregistration CR 

SELECT 
    CASE CR.claim_status 
       WHEN 'RC' THEN (SELECT clause_no 
                       FROM claim_rejection_code_trans CRCT 
                       WHERE CR.s_no = CRCT.s_no) 
    END AS Clause_No 
FROM
    tblclaimregistration CR;

while running this code I am getting the error

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Please help

Upvotes: 0

Views: 97

Answers (1)

Mofid.Moghimi
Mofid.Moghimi

Reputation: 1005

Your subquery in case statement returns more than 1 value.

You must return just one value because this subquery executed per row of CR table.

You can used "TOP 1" in each subquery.

For example:

SELECT 
CASE CR.claim_status 
   WHEN 'RC' 
      THEN (SELECT TOP 1 ins_clause_no 
            FROM tbl_rej_code_master RCM 
            WHERE CR.s_no IN (SELECT s_no 
                              FROM claim_rejection_code_trans CRCT 
                              WHERE CRCT.irrmp_sr_no = RCM.rej_srno ORDER BY <Your Column>)) 
END 
FROM   
tblclaimregistration CR 

SELECT 
CASE CR.claim_status 
   WHEN 'RC' THEN (SELECT TOP 1 clause_no 
                   FROM claim_rejection_code_trans CRCT 
                   WHERE CR.s_no = CRCT.s_no ORDER BY <Your Column>) 
END AS Clause_No 
FROM
tblclaimregistration CR;

Upvotes: 1

Related Questions