wrong query nested select error ORA-00913

Question

It doesnt work and I only got error ORA-00913: " too many values.".

Problem I think is with nested select. I want to use this select to view.

This code shows report for all employees. I changed attribute names for this post:

 SELECT sk."AAAA", sk."BBBB", sk."CCCC", sk."DDD",
          sk."EEEE", sk."FFFF", sk."GGGGG",
          sk."HHHHH",
          (SELECT f.VALUE
           FROM fnd_user_property_tab f
           WHERE f.identity = sk.login
           AND NAME = 'IIIII') iii,sk."JJJJ",

           (SELECT f.VALUE
           FROM fnd_user_property_tab f
           WHERE f.identity = sk.login
           AND NAME = 'SMTP_MAIL_ADDRESS') mail,

           (SELECT f.VALUE
            FROM fnd_user_property_tab f
            WHERE f.identity = sk.login 
            AND NAME = 'KKKK') kkkk,

            (SELECT fnd.ORACLE_ROLE
             FROM fnd_user_role_tab fnd
             WHERE fnd.identity = sk.login
             AND fnd.ORACLE_ROLE LIKE 'STH-%') profil,

             (SELECT dr.ROLE, sox_co_w_roli(dr.role) skład
              FROM sys.dba_roles dr
              WHERE dr.role = (SELECT fnd.ORACLE_ROLE
                              FROM fnd_user_role_tab fnd
                              WHERE fnd.identity = sk.login  
                              AND fnd.ORACLE_ROLE LIKE 'STH-%')
             ) profile_role
    FROM sox_konta sk
    WHERE("PROFIL_DB" LIKE '%ppppp%'
          OR "PROFIL_DB" = 'oooooooo'
          OR "PROFIL_DB" = 'rrrrrrrr'
          )
    AND "COMPANIES" IN ('ZZ; ', 'YY; ')

Answer 1

Try to rewrite your select in this way

SELECT sk."AAAA", sk."BBBB", sk."CCCC", sk."DDD",
          sk."EEEE", sk."FFFF", sk."GGGGG",
          sk."HHHHH",
          f_iii.value iii, sk."JJJ",
          f_mail.value mail,
          f_kkk.value kkk,
          fnd.oracle_role profil,
          dr.ROLE, sox_co_w_roli(dr.role) skład
    FROM sox_konta sk,
         fnd_user_property_tab f_iii,
         fnd_user_property_tab f_mail,
         fnd_user_property_tab f_kkk,
         fnd_user_role_tab fnd,
         sys.dba_roles dr
    WHERE("PROFIL_DB" LIKE '%ppppp%'
          OR "PROFIL_DB" = 'oooooooo'
          OR "PROFIL_DB" = 'rrrrrrrr'
          )
        AND "COMPANIES" IN ('ZZ; ', 'YY; ')
        AND f_iii.identity = sk.login
        AND f_iii.NAME = 'IIIII'
        AND f_mail.identity = sk.login
        AND f_mail.NAME = 'SMTP_MAIL_ADDRESS'
        AND f_kkk.identity = sk.login
        AND f_kkk.NAME = 'KKKK'
        AND fnd.identity = sk.login
        AND fnd.ORACLE_ROLE LIKE 'STH-%'
        AND dr.role = fnd.ORACLE_ROLE;

I think this will help.

Answer 2

Start with this piece. Are you getting the error? Yes? You need to alter the subquery that provides the value for iii so that it returns only one row.

If this segment does not return the error, then add the next piece until you identify what is causing the error.

SELECT sk."AAAA", sk."BBBB", sk."CCCC", sk."DDD",
      sk."EEEE", sk."FFFF", sk."GGGGG",
      sk."HHHHH",
      (SELECT f.VALUE
       FROM fnd_user_property_tab f
       WHERE f.identity = sk.login
       AND NAME = 'IIIII') iii
FROM sox_konta sk
WHERE("PROFIL_DB" LIKE '%ppppp%'
      OR "PROFIL_DB" = 'oooooooo'
      OR "PROFIL_DB" = 'rrrrrrrr'
      )
AND "COMPANIES" IN ('ZZ; ', 'YY; ')