前端之家

  1. 首页
  2. 问答

ORA-01427单行子查询返回多行-如何解决？

• 问答

该查询已运行2年多了，最近才开始抛出ORA-01427单行子查询，返回多行错误。有多个子查询时，调试此错误的最佳方法是什么？我应该在每个子查询中添加MAX语句吗？我尝试过将某些=运算符切换为IN，但这不起作用，而且我也不相信它会给我正确的结果。

感谢该社区可以提供的任何见解或帮助。我正在通过这个工作撕掉头发！ 

SELECT
        NVL(
                (SELECT 
                'Y'
                FROM 
                PER_ASSIGnmENT_SUPERVISORS_F 
                WHERE
                 MANAGER_ID = PAPF.PERSON_ID
                AND  MANAGER_TYPE ='LINE_MANAGER'
                AND TRUNC(SYSDATE) BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE
                AND ROWNUM = 1),'N') MANAGER_flaG,PAAM.ASSIGnmENT_STATUS_TYPE,PAAM.EMPLOYMENT_CATEGORY,TO_CHAR(PPOS.DATE_START,'YYYY-MM-DD') AS HIRE_DATE,PER_EXTRact_UTILITY.GET_STANDARD_WORKING_HOURS(PAAM.ASSIGnmENT_ID,TRUNC(SYSDATE)) AS STANDARD_WORKING_HOURS,TO_CHAR(PAAM.EFFECTIVE_START_DATE,'YYYY-MM-DD') AS EFFECTIVE_START_DATE,(
                        SELECT 
                        PJFVL.JOB_CODE
                        FROM 
                        PER_ALL_ASSIGnmENTS_M PAAM1,PER_JOBS_F_VL PJFVL
                        WHERE 
                        PAAM1.PERSON_ID = PASF.MANAGER_ID
                        AND TRUNC(SYSDATE) BETWEEN PAAM1.EFFECTIVE_START_DATE AND PAAM1.EFFECTIVE_END_DATE
                        AND TRUNC(SYSDATE) BETWEEN PJFVL.EFFECTIVE_START_DATE AND PJFVL.EFFECTIVE_END_DATE
                        and PAAM1.ASSIGnmENT_STATUS_TYPE='actIVE'
                        AND PAAM1.ASSIGnmENT_TYPE = 'E'
                        AND PAAM1.effective_latest_change = 'Y' 
                        AND PJFVL.JOB_ID = PAAM1.JOB_ID

)as Manager_job_code,(
                        SELECT 
                                     HOIF.ORG_INFORMATION1 
                        FROM 

                        PER_ALL_ASSIGnmENTS_M PAAM2,HR_ORGANIZATION_INFORMATION_F HOIF

                        WHERE 

                        PAAM2.PERSON_ID = PASF.MANAGER_ID
                        AND HOIF.ORG_INFORMATION_CONTEXT = 'DEPT_DET'
                        and PAAM2.ASSIGnmENT_STATUS_TYPE='actIVE'
                        AND PAAM2.ASSIGnmENT_TYPE = 'E'
                        AND PAAM2.effective_latest_change = 'Y' 
                        AND TRUNC(SYSDATE) BETWEEN PAAM2.EFFECTIVE_START_DATE AND PAAM2.EFFECTIVE_END_DATE
                        AND TRUNC(SYSDATE) BETWEEN HOIF.EFFECTIVE_START_DATE AND HOIF.EFFECTIVE_END_DATE
                        AND  HOIF.ORGANIZATION_ID = PAAM2.ORGANIZATION_ID

) as Manager_dep_code,(SELECT 
            COUNT(PPOS.PERIOD_OF_SERVICE_ID)
            FROM

            PER_PERIODS_OF_SERVICE PPOS
            WHERE 
            1 = 1
            AND PAAM.PERSON_ID = PPOS.PERSON_ID
) AS INactIVE_WORKRELATIONSHIP,PAPF.PERSON_NUMber as SAMaccOUNTNAME,(SELECT CSB.NAME FROM 
        CMP_SALARY CS,CMP_SALARY_BASES CSB
        WHERE 
        CS.ASSIGnmENT_ID = PAAM.ASSIGnmENT_ID
        AND CS.SALARY_BASIS_ID = CSB.SALARY_BASIS_ID
        and TRUNC(SYSDATE) BETWEEN CS.DATE_FROM AND CS.DATE_TO
) AS hourly_salary_Paid,TO_CHAR(PP.DATE_OF_BIRTH,'YYYY-MM-DD') AS DOB,PPNFV.LAST_NAME,PPNFV.FIRST_NAME,HLA.LOCATION_NAME as Location,HLA.ADDRESS_LINE_1 AS LOC_ADDRESS_1,HLA.ADDRESS_LINE_2 AS LOC_ADDRESS_2,HLA.TOWN_OR_CITY AS City,HLA.POSTAL_CODE AS ZIP_CODE,HLA.REGION_2 AS STATE,PPNFV.KNOWN_AS AS PREFERRED_NAME,TRIM((PPNFV.KNOWN_AS||' '||PPNFV.LAST_NAME)) AS PREFERRED_NAME_LAST_NAME,(SELECT 
        PPNFV.DISPLAY_NAME 
        FROM  per_person_names_f_v PPNFV
        WHERE  1 = 1
        AND PPNFV.PERSON_ID = PASF.MANAGER_ID
        AND PPNFV.NAME_TYPE='GLOBAL'
        AND TRUNC(SYSDATE) BETWEEN PPNFV.EFFECTIVE_START_DATE AND PPNFV.EFFECTIVE_END_DATE
) AS  MANAGER_NAME,(SELECT 
        PAPF.PERSON_NUMber
        FROM PER_ALL_PEOPLE_F PAPF
        WHERE PAPF.PERSON_ID = PASF.MANAGER_ID
        AND TRUNC(SYSDATE) BETWEEN PAPF.EFFECTIVE_START_DATE AND PAPF.EFFECTIVE_END_DATE
) AS  MANAGER_NUMber,HAOUFVL.NAME  AS DEPARTMENT,PAAM.ASSIGnmENT_NAME AS JOB_TILE,PLE.NAME as Company,PJLG.INFORMATION1 AS FLSA,SUBSTR(PNI.NATIONAL_IDENTIFIER_NUMber,-4) AS Ssn_NUMber,PAAM.ASS_ATTRIBUTE1 as Officer_TITLE,(
         select
          bu.name
         from  
         hr_all_organization_units_f_vl bu
         where 1 = 1
         and paam.business_unit_id = bu.organization_id
         and trunc(sysdate) between bu.effective_start_date and bu.effective_end_date

) as BUS_UNIT,TO_CHAR (PPOS.ORIGINAL_DATE_OF_HIRE,'YYYY-MM-DD') AS ORIGINAL_DATE_OF_HIRE1,(
            CASE  WHEN PPLF.SEX = 'F' THEN 
            'TRUE'
            ELSE
            'FALSE'
            END 
) AS GENDER,(
            CASE  WHEN PJFFVL.JOB_FAMILY_NAME  = 'Executive' THEN 
            'ELT'
            ELSE
            ' '
            END 
) AS ELT_DESIGNATION,HOIF.ORG_INFORMATION1 as DEPATMENT_CODE,PJFV.JOB_CODE  AS JOB_CODE,PAF.ADDRESS_LINE_1 AS HOME_ADDRESS_LINE_1,PAF.ADDRESS_LINE_2 AS HOME_ADDRESS_LINE_2,PAF.TOWN_OR_CITY AS HOW_ADDRESS_CITY,PAF.REGION_2 AS HOME_ADDRESS_STATE,PAF.POSTAL_CODE AS HOME_ADRESS_ZIP_CODE,PGFTL.NAME as Grade_level,(SELECT 
        distinct (per_extract_utility.get_decoded_lookup('JOB_FUNCTION_CODE',PJF.JOB_FUNCTION_CODE))
        FROM           
        PER_JOB_SECURED_LIST_V job WHERE TRUNC(SYSDATE) BETWEEN effective_start_date AND effective_end_date
) as JOB_FUNCTION,pp.attribute1 as PER_NETWORKID,(SELECT 
        PPNFV.attribute1 
        FROM  PER_PERSONS PPNFV
        WHERE 1 = 1
        AND PPNFV.PERSON_ID = PASF.MANAGER_ID
) AS  MANAGER_NETWORKID,HOIF.ORG_INFORMATION2 AS REGION,to_char(paam.ass_attribute_date1,'MM/DD/YYYY') as OfficerPromoDate
FROM 
PER_ALL_ASSIGnmENTS_M PAAM,PER_ASSIGnmENT_SUPERVISORS_F PASF,PER_PERIODS_OF_SERVICE PPOS,PER_ALL_PEOPLE_F PAPF,PER_PERSONS PP,per_person_names_f_v PPNFV,HR_LOCATIONS_ALL HLA,HR_ALL_ORGANIZATION_UNITS_F_VL HAOUFVL,PER_JOB_LEG_F PJLG,PER_NATIONAL_IDENTIFIERS PNI,PER_PEOPLE_LEGISLATIVE_F   PPLF,PER_JOB_FAMILY_F_VL   PJFFVL,PER_JOBS_F_V PJFV,HR_ORGANIZATION_INFORMATION_F HOIF,PER_ADDRESSES_F PAF,PER_PERSON_ADDR_USAGES_F PPAUF,PER_GRADES_F_TL PGFTL,PER_JOBS_F PJF,PER_LEGAL_EMPLOYERS PLE,HR_ALL_ORGANIZATION_UNITS_F HAOUF
WHERE 
PAAM.ASSIGnmENT_STATUS_TYPE='actIVE'
AND PAAM.ASSIGnmENT_TYPE = 'E'
AND PAAM.effective_latest_change = 'Y' 
AND PAAM.ASSIGnmENT_ID = PASF.ASSIGnmENT_ID(+)
AND PASF.MANAGER_TYPE (+) = 'LINE_MANAGER'
AND PNI.PERSON_ID(+) = PAAM.PERSON_ID
AND PAAM.PERSON_ID  = PPLF.PERSON_ID(+)
and PJFV.JOB_FAMILY_ID= PJFFVL.JOB_FAMILY_ID(+)
AND PJFV.JOB_ID(+) = PAAM.JOB_ID
AND HOIF.ORGANIZATION_ID(+)= PAAM.ORGANIZATION_ID
AND HOIF.ORG_INFORMATION_CONTEXT(+) = 'DEPT_DetaILS'
AND PPAUF.PERSON_ID(+)= PAPF.PERSON_ID
AND PPAUF.ADDRESS_TYPE (+) = 'HOME'
AND PPAUF.ADDRESS_ID= PAF.ADDRESS_ID(+)
AND PGFTL.GRADE_ID(+)= PAAM.GRADE_ID
AND PJF.JOB_ID(+)= PAAM.JOB_ID
AND PJLG.INFORMATION_CATEGORY='HRX_US_JOBS'
and HAOUF.ORGANIZATION_ID(+)=PLE.ORGANIZATION_ID
and PAAM.LEGAL_ENTITY_ID=PLE.ORGANIZATION_ID
AND TRUNC(SYSDATE) BETWEEN PAAM.EFFECTIVE_START_DATE(+) AND PAAM.EFFECTIVE_END_DATE (+)
AND PAAM.PERIOD_OF_SERVICE_ID = PPOS.PERIOD_OF_SERVICE_ID
AND PAPF.PERSON_ID = PAAM.PERSON_ID
AND PP.PERSON_ID = PAAM.PERSON_ID
AND PPNFV.PERSON_ID = PAAM.PERSON_ID
AND HLA.LOCATION_ID(+) = PAAM.LOCATION_ID
AND PJLG.JOB_ID(+) = PAAM.JOB_ID
AND  HAOUFVL.ORGANIZATION_ID(+) = PAAM.ORGANIZATION_ID
AND PPNFV.NAME_TYPE = 'GLOBAL'
AND TRUNC(SYSDATE) BETWEEN PPNFV.EFFECTIVE_START_DATE AND PPNFV.EFFECTIVE_END_DATE
AND TRUNC(SYSDATE) BETWEEN PAPF.EFFECTIVE_START_DATE AND PAPF.EFFECTIVE_END_DATE 
AND TRUNC(SYSDATE) BETWEEN HAOUFVL.EFFECTIVE_START_DATE(+) AND HAOUFVL.EFFECTIVE_END_DATE(+)
AND TRUNC(SYSDATE) BETWEEN PPLF.EFFECTIVE_START_DATE(+) AND PPLF.EFFECTIVE_END_DATE(+)
AND TRUNC(SYSDATE) BETWEEN PJFFVL.EFFECTIVE_START_DATE(+) AND PJFFVL.EFFECTIVE_END_DATE(+)
AND TRUNC(SYSDATE) BETWEEN PJFV.EFFECTIVE_START_DATE(+) AND PJFV.EFFECTIVE_END_DATE(+)
AND TRUNC(SYSDATE) BETWEEN HOIF.EFFECTIVE_START_DATE(+) AND HOIF.EFFECTIVE_END_DATE(+)
AND TRUNC(SYSDATE) BETWEEN PAF.EFFECTIVE_START_DATE(+) AND PAF.EFFECTIVE_END_DATE(+)
AND TRUNC(SYSDATE) BETWEEN PPAUF.EFFECTIVE_START_DATE(+) AND PPAUF.EFFECTIVE_END_DATE(+)
AND TRUNC(SYSDATE) BETWEEN PGFTL.EFFECTIVE_START_DATE(+) AND PGFTL.EFFECTIVE_END_DATE(+)
AND TRUNC(SYSDATE) BETWEEN PJF.EFFECTIVE_START_DATE(+) AND PJF.EFFECTIVE_END_DATE(+)
AND TRUNC(SYSDATE) BETWEEN PLE.EFFECTIVE_START_DATE AND PLE.EFFECTIVE_END_DATE
AND TRUNC(SYSDATE) BETWEEN HAOUF.EFFECTIVE_START_DATE(+) AND HAOUF.EFFECTIVE_END_DATE(+)
AND TRUNC(SYSDATE) BETWEEN HLA.EFFECTIVE_START_DATE(+) AND HLA.EFFECTIVE_END_DATE(+)
AND TRUNC(SYSDATE) BETWEEN PJLG.EFFECTIVE_START_DATE(+) AND PJLG.EFFECTIVE_END_DATE(+)
AND TRUNC(SYSDATE) BETWEEN PASF.EFFECTIVE_START_DATE(+) AND PASF.EFFECTIVE_END_DATE(+)

ORDER BY PAPF.PERSON_NUMber
zc5210528 回答：ORA-01427单行子查询返回多行-如何解决？

在SQL客户端中运行查询，该查询将生成更详细的错误消息。例如，下面的代码在SQL * Plus中运行，它正确指示问题出在第3行的子查询中。

SQL> select
  2     (select 1 from dual) this_will_work,3     (select 1 from dba_objects) this_will_fail,4     (select 1 from dual) this_will_work
  5  from dual;
   (select 1 from dba_objects) this_will_fail,*
ERROR at line 3:
ORA-01427: single-row subquery returns more than one row
bi-publisherora-01427oracle
本文链接：https://www.f2er.com/2728137.html

大家都在问