ORA-01427 error in crystal report

Question

Looking for a resolution to the error

ORA-01427 single-row subquery returns more than one row

I am getting on this SQL code. This is a command in my crystal report which I have narrowed the error down to. But there are multiple sub-queries in this code and I can't find the best approach to getting this error to resolve. I know that this type of error is generally associated with using '=' in a select statement for a sub-query and that I should use another such as IN, LIKE, etc. Any advice would be helpful.

Thanks,

SELECT
       JREQ.HRORGANIZATION,
       JREQ.JOBREQUISITION                   AS "Req #",
        cmdPoolRef."Pool Req Ref"             AS "Candidate Pool",
        cmdDualPostingRef."Dual Posting Ref"  AS "Dual Posting Ref",
        DEPTNAME.DESCRIPTION                  AS "Department Name",
        JOB.DESCRIPTION                       AS "Job Title",
        JREQ.OPENDATE                         AS "Date Opened",
        CASE JREQ.STATUS
             WHEN 1 THEN 'Pending'
             WHEN 2 THEN 'Open'
             WHEN 3 THEN 'On Hold'
             WHEN 4 THEN 'Cancelled'
             WHEN 5 THEN 'Filled'
             WHEN 7 THEN 'Draft'
             WHEN 8 THEN 'Approval Requested'
             ELSE CONCAT('Unknown',JREQ.STATUS)
        END                                 AS "Req Status",
        cmdFTELimited."FTE Limted"          AS "FTE",
        JREQ.INITIALOPENINGS                AS "# of  Positions",
        JREQ.FILLEDOPENINGS                 AS "# of Positions Filled",
        DEPTNAME.SHORTDESCRIPTION           AS "Cost Center",
        JREQ.REASONFOROPENING               AS "Req Reason Code",
        cmdEmpReplacing."Replacement ID"    AS "Replacement ID",
        cmdEmpReplacing."Replacement Name"  AS "Replacement Name",
        JREQ."UNION"                        AS "Union",
        DEPT_LOCATION.DESCRIPTION           AS "Location",
        JREQ.SALARYSTRUCTURE                AS "Family",
        POS.SALARYSTRUCTUREGRADE            AS "Grade",
        TRIM(RECRUITER.PRESENTATIONNAMESNAPSHOT)  AS "Recruiter Name",
        TRIM(HIREMGR.PRESENTATIONNAMESNAPSHOT)    AS "Hiring Manager",
        cmdApptType."Appt Type"                   AS "Appointment Type",
        JREQ.CATEGORY                             AS "Category",
        cmdCandidateHired."Person Hired"          AS "Person Hired",
        cmdCandidateHired."Start Date"            AS "Start Date",
        cmdCandidateHired."Offer Amount"          AS "Offer Amount",
        VW.ENTITYORGUNIT
FROM LTMPROD.JOBREQUISITION JREQ
LEFT OUTER JOIN LTMPROD.HRORGANIZATIONUNIT DEPTNAME         ON   JREQ.HRORGANIZATIONUNIT = DEPTNAME.HRORGANIZATIONUNIT
LEFT OUTER JOIN LTMPROD.HRLOCATION DEPT_LOCATION            ON   JREQ.LOCATION = DEPT_LOCATION.HRLOCATION 
LEFT OUTER JOIN LTMPROD.EMPLOYEE RECRUITER                  ON   JREQ.RECRUITER = RECRUITER.EMPLOYEE 
LEFT OUTER JOIN LTMPROD.POSITION POS                        ON   JREQ.POSITION = POS.POSITION
LEFT OUTER JOIN LTMPROD.EMPLOYEE HIREMGR                    ON   JREQ.HIRINGMANAGER = HIREMGR.EMPLOYEE
LEFT OUTER JOIN LTMPROD.JOB                                 ON   POS.JOB = JOB.JOB
LEFT OUTER JOIN LTMPROD.XXX_ORGUNIT_ENTITY_VIEW VW          ON   JREQ.HRORGANIZATIONUNIT = VW.HRORGANIZATIONUNIT
LEFT OUTER JOIN (
                    SELECT TO_NUMBER(UFS.ALPHA)                               AS "ReplaceThisEmp",
                           UFS.UFSUNIQUEID,
                           REPLACETHISEMPLOYEE.EMPLOYEE                       AS "Replacement ID",
                           TRIM(REPLACETHISEMPLOYEE.PRESENTATIONNAMESNAPSHOT) AS "Replacement Name"
                    FROM LTMPROD.USERFIELD_STORAGE  UFS
                    LEFT OUTER JOIN LTMPROD.EMPLOYEE REPLACETHISEMPLOYEE      ON  TO_NUMBER(UFS.ALPHA) = REPLACETHISEMPLOYEE.EMPLOYEE
                    WHERE UFS.UFSCLASSNAME = 'JobRequisition'
                    AND UFS.UFSFIELDNAME = 'EmployeeReplacingID'
                )  cmdEmpReplacing        ON cmdEmpReplacing.UFSUNIQUEID = JREQ.UNIQUEID
LEFT OUTER JOIN (
                    SELECT JA.JOBREQUISITION,    
                           JA.EMPLOYMENTSTARTDATE                AS "Start Date",
                           JA.SALARY                             AS "Offer Amount",
                           TRIM(CAN.PRESENTATIONNAMESNAPSHOT)    AS "Person Hired"
                    FROM LTMPROD.JOBAPPLICATION JA              
                    LEFT OUTER JOIN LTMPROD.CANDIDATE CAN     ON CAN.CANDIDATE = JA.CANDIDATE
                    WHERE JA.SELECTIONPROCESS = 9  
                )   cmdCandidateHired       ON cmdCandidateHired.JOBREQUISITION = JREQ.JOBREQUISITION
LEFT OUTER JOIN (
                  SELECT UFS.ALPHA AS "Appt Type", UFS.UFSUNIQUEID
                  FROM LTMPROD.USERFIELD_STORAGE UFS
                  WHERE UFS.UFSCLASSNAME = 'JobRequisition'
                  AND UFS.UFSFIELDNAME = 'AppointmentType'
                )   cmdApptType            ON cmdApptType.UFSUNIQUEID = JREQ.UNIQUEID
LEFT OUTER JOIN (
                  SELECT UFS.ALPHA AS "Pool Req Ref", UFS.UFSUNIQUEID
                  FROM LTMPROD.USERFIELD_STORAGE UFS
                  WHERE UFS.UFSCLASSNAME = 'JobRequisition'
                  AND UFS.UFSFIELDNAME = 'PoolRequistionReference'
                )    cmdPoolRef            ON cmdPoolRef.UFSUNIQUEID = JREQ.UNIQUEID
LEFT OUTER JOIN (
                  SELECT UFS.ALPHA AS "Dual Posting Ref", UFS.UFSUNIQUEID
                  FROM LTMPROD.USERFIELD_STORAGE UFS
                  WHERE UFS.UFSCLASSNAME = 'JobRequisition'
                  AND UFS.UFSFIELDNAME = 'DualPostingReference'
                )    cmdDualPostingRef      ON cmdDualPostingRef.UFSUNIQUEID = JREQ.UNIQUEID
LEFT OUTER JOIN (
                  SELECT UF.ALPHA AS "FTE Limted",UF.UFSUNIQUEID
                  FROM LTMPROD.USERFIELD_STORAGE UF 
                  WHERE UF.UFSCLASSNAME = 'JobRequisition'
                  AND UF.UFSFIELDNAME = 'FTELimited'
                )    cmdFTELimited          ON cmdFTELimited.UFSUNIQUEID = JREQ.UNIQUEID

the problem is not in the query see the error ORA-01427 says single-row subquery returns more than one row - it should be a subquery that you use somewhere in select or where section and where database expects to have one and only one value — are

BA. BA. · Accepted Answer · 2017-09-07T05:45:11

Note: Crystal Report could have generated this error not due to the above query but other queries you might be using within the report, say for dynamic parameters? Check all other queries used in the report, and as you mentioned, it is somewhere where oracle expects one record and got more: usually an ( = (select...))

ORA-01427 error in crystal report

1 Answers