PLSQL more than single row returned issue in ID field

Question

I have an issue with the query below, in the main SELECT the value of ENTITY_ID cannot be retrieved, as I'm using LIKE I get more than a single result back. How can I overcome this? If I place an inner SELECT such as:

    (
            SELECT
                SEC_NN_SERVER_T.SRV_ID
            FROM
                SEC_NN_SERVER_T
            WHERE
                UPPER(SEC_NN_SERVER_T.SRV_NAME) LIKE UPPER('%gen%') )       AS ENTITY_ID,

I get the same error:

"ORA-01427: single-row subquery returns more than one row".

He is the full query:

SELECT DISTINCT
SEC_USER.USR_ID         AS USR_ID,
SEC_USER.USR_FIRST_NAME AS USR_FIRST_NAME,
SEC_USER.USR_LAST_NAME  AS USR_LAST_NAME,
SEC_USER_PRIV.ROLE_ID   AS SYSTEM_ROLE_ID,
21                      AS ENTITY_TYP_CODE,
(
    SELECT
        SEC_NN_SERVER_T.SRV_ID
    FROM
        SEC_NN_SERVER_T
    WHERE
        UPPER(SEC_NN_SERVER_T.SRV_NAME) LIKE UPPER('%gen%') ) AS ENTITY_ID
FROM
    SEC_USER_PRIV,
    SEC_USER
WHERE
    SEC_USER.USR_ID = SEC_USER_PRIV.USR_ID
AND ((
            SEC_USER_PRIV.ENTITY_TYP_CODE = 21
        AND SEC_USER_PRIV.ENTITY_ID IN (
            (
                SELECT
                    SERVER_T.SRV_ID
                FROM
                    SERVER_T
                WHERE
                    UPPER(SERVER_T.SRV_NAME) LIKE UPPER('%gen%') ))))

Please assist thanks in advance.

Answer 1

By repeating the subquery from your where clause in your select you destroy the coupling between SEC_USER_PRIV.ENTITY_ID and the subquery. Subqueries in the SELECT part should always return only one value, because the select constructs the row to be returned. I expect your problem will be solved by simply including SEC_USER_PRIV.ENTITY_ID instead of the subquery.