"Subquery returns more than 1 row" when use select to set value of parameter-CodePudding

I have an issue when I try to set the value of a variable with a subquery.

This is my SQL code:

SELECT @V_SOURCE = (SELECT ITEM_SOURCE
                    FROM TABLE1
                    WHERE OPP_CODE = @V_OPP_CODE
                      AND PDGROUPNO = @V_PRD_GROUP_NO
                      AND DELETE_FLAG IS NULL
                      AND CONTRACTOR = @V_CONTRACTOR
                      AND OPP_ITEM_NO = @_OPP_ITEM_NO)

When I run this code with an assumed variable that is used in WHERE condition, it returns only 1 row and 1 col that is correct but if I run this code with store procedure it will return 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

CodePudding user response：

The sub-query can return multiple rows.
Even if it shouldn't, that won't do.

But you can change it to this.

SELECT @V_SOURCE = ITEM_SOURCE
FROM TABLE1
WHERE OPP_CODE = @V_OPP_CODE
  AND PDGROUPNO = @V_PRD_GROUP_NO
  AND DELETE_FLAG IS NULL
  AND CONTRACTOR = @V_CONTRACTOR
  AND OPP_ITEM_NO = @_OPP_ITEM_NO
GROUP BY ITEM_SOURCE;

It'll assign the last value of the resultset to the variable.
Which is fine, since you expect only one anyway.

Another way is to pick only the top 1

SET @V_SOURCE = (
     SELECT TOP 1 ITEM_SOURCE
     FROM TABLE1
     WHERE OPP_CODE = @V_OPP_CODE
       AND PDGROUPNO = @V_PRD_GROUP_NO
       AND DELETE_FLAG IS NULL
       AND CONTRACTOR = @V_CONTRACTOR
       AND OPP_ITEM_NO = @_OPP_ITEM_NO
);

CodePudding user response：

I find an issue in my query because of I query data to use for this query some CONTRACTOR is NULL then it makes an error when using this query

SELECT @V_SOURCE = (SELECT ITEM_SOURCE
                FROM TABLE1
                WHERE OPP_CODE = @V_OPP_CODE
                  AND PDGROUPNO = @V_PRD_GROUP_NO
                  AND DELETE_FLAG IS NULL
                  AND CONTRACTOR = @V_CONTRACTOR
                  AND OPP_ITEM_NO = @_OPP_ITEM_NO)

When I filter data that CONTRACTOR is NULL out, I don't get any error now.

Thank you, everyone.