Failed to retrieve data from the database ORA-01427

Question

A Crystal Report I am running is getting an error message. It says "Failed to retrieve data from the database". It also then shows, "ORA-01427: single-row subquery returns more than one row". Seems pretty straight forward but I can't figure it out. I have one command built into the report with no sub-queries:

SELECT WA.HRORGANIZATION,TRIM(PA.ACTOR) AS "Epass",E.EMPLOYEE,TRIM(E.PRESENTATIONNAMESNAPSHOT),WA.HRORGANIZATIONUNIT,VW.ENTITYORGUNIT,VW.ENTITY
FROM LTMPROD.AGENT AG
LEFT OUTER JOIN LTMPROD.PARENTAGENT PA      ON PA.PARENTAGENT = AG.PARENTAGENT 
LEFT OUTER JOIN LTMPROD.EMPLOYEE E          ON TRIM(AG.KEYTEXT) = TRIM(E.HRORGANIZATION)||', '||TRIM(E.EMPLOYEE)
LEFT OUTER JOIN LTMPROD.WORKASSIGNMENT WA   ON WA.EMPLOYEE = E.EMPLOYEE
                                            AND WA.WORKASSIGNMENT = E.PRIMARYWORKASSIGNMENT
LEFT OUTER JOIN LTMPROD.XXX_ORGUNIT_ENTITY_VIEW VW  ON VW.HRORGANIZATIONUNIT = WA.HRORGANIZATIONUNIT
WHERE AG.BORBUSINESSCLASSNAME = 'Employee'
AND TRIM(PA.ACTOR) = '{?LAWSONRS_CurrentUser}'

I also have a formula built in the record selection:

if {?LAWSONRS_CurrentUser} = "xyz" then  
    {JOBPOSTING.PDRBEGIN} in {?Start Date} to {?End Date} 
else   
{cmdEpass.Epass} = {?LAWSONRS_CurrentUser}           
and {XXX_ORGUNIT_ENTITY_VIEW.ENTITYORGUNIT} IN 
(
    SELECT {cmdEpass.ENTITYORGUNIT} 
    CASE 606: [606]      
    CASE 609: [609,610]  
    CASE 610: [609,610]  
    CASE 883: [883]      
    DEFAULT : [0]
)
and 
{JOBPOSTING.PDRBEGIN} in {?Start Date} to {?End Date}

Where would this error be occurring (sorry for any formatting issues)? If I completely delete the formula the report runs just fine. So I do not think the problem is in the command. But I still want to filter based on the users Epass.

Looks like your formula is returning multiple values-- and you are using it in a WHERE clause with an equals comparison. — Jacob H
Don't return multiple values from the function or use IN instead of = — Jacob H

sdsc81 sdsc81 · Accepted Answer · 2017-09-06T18:09:36

Your formula is returning more than one element.

You can change = for IN ( ... )

Or use LIMIT 1 for selectin just one row.

Failed to retrieve data from the database ORA-01427

1 Answers