CODEWITHSUNDEEP
oracle-databaseplsqlora-01427
David Faizulaev
David Faizulaev

Reputation: 5721

PL SQL more than single row returned issue

I'm executing the following query and I get "ORA-01427: single-row subquery returns more than one row". The error returns in the DECODE which is located in the main SELECT. I cannot place the USER_PRF_NOTIFICATION_T table in the FROM section, as USER_PRF_NOTIFICATION_T may not contain any values. If it does than the values in SEC_USER_PRIV.ENTITY_ID do not all exist in USER_PRF_NOTIFICATION_T.ENTITY_ID.

    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,
    DECODE (
              (
              SELECT
                  SYSTMTYP_CODE
              FROM
                  USER_PRF_NOTIFICATION_T
              WHERE
                  USER_PRF_NOTIFICATION_T.USR_ID = SEC_USER.USR_ID
              AND USER_PRF_NOTIFICATION_T.ENTITY_ID IN
                  (
                      SELECT
                          SERVER_T.SRV_ID
                      FROM
                          SERVER_T
                      WHERE
                          UPPER(SERVER_T.SRV_NAME) LIKE UPPER('%gen%') )
              AND USER_PRF_NOTIFICATION_T.ENTITY_TYP_CODE = 21), 1, 'SYSTEM', 2, 'TEST', NULL ,
            'ALL') AS ENTITY_SYSTM_TYP_NOTIFICATION
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%') ))))

How can solve this? referencing the entity ID directly obviously resolves that issue. I tried using tableof which contains all ID and trying:

USER_PRF_NOTIFICATION_T.ENTITY_ID IN (tableof) but I get the same error. How can resolve this? I need to create a correlation between the ID selected in the FROM section and the ID used in the DECODE.

Thanks in advance.

Upvotes: 0

Views: 96

Answers (1)

Copro
Copro

Reputation: 26

I think you added "NULL" in the list of key/value pairs and that is causing the error.

`1, 'SYSTEM', 2, 'TEST', NULL , 'ALL'`

Remove NULL here

Upvotes: 1

Related Questions