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

Toad for Oracle & DevOps Toolkit
1

Hello All

Have select statement that I can’t get to run.

I have a data table and reference table

Issue is that my reference table is updated 4x times day along with my data table with it being the same look up data.

When i join on the reference table its inflating my numbers.

Wondering how i can join on my reference table and have a distinct join

a.SERVICE_ID,
count ( a.Dialout) as “Dials”,
count ( a.DISP_ID) as"Connects",
sum (c.CONTACT) as “Contacts”

FROM call_data a

JOIN reference_table c

ON a.DISP_ID = (select distinct c.DISP_ID
from reference_table c
where UPD_DATE = (select max (UPD_DATE)
from reference_table))

group by
a.SERVICE_ID
order by
a.SERVICE_ID

Thanks

2

I think you meant to do something like this.

 select a.service_id, a.disp_id, a.dials, a.connects, a.contacts, c.upd_date
from
(select SERVICE_ID,
disp_id,
COUNT(Dialout) AS Dials,
COUNT(DISP_ID) AS Connects,
SUM(CONTACT) AS Contacts
FROM call_data
group by SERVICE_ID, disp_id) a
JOIN (select disp_id, max(upd_date) upd_date
from reference_table
group by disp_id) c on a.disp_id = c.disp_id
order by a.service_id;
3

I have the same issue with my querie:

SELECT

COALESCE(E.EMPLCODE,E.EMPLCODE) EMPLCODE,

COALESCE(E.FIRST_NAME,E.FIRST_NAME) FIRST_NAME,

COALESCE(E.NAME_INITIAL,E.NAME_INITIAL) NAME_INITIAL,

COALESCE(E.LAST_NAME1,E.LAST_NAME1) LAST_NAME1,

COALESCE(E.LAST_NAME2,E.LAST_NAME2) LAST_NAME2,

(SELECT D2.DESCRIPTION

FROM hrsense1.PADIV1 D2

LEFT JOIN hrsense1.PAEMP1 E2 ON E2.DIV1 = D2.DIV1

LEFT JOIN HRSENSE1.SSCODE S2 ON E2.COST_CENTER = S2.CODE

WHERE S2.TYPE = ‘LAB’ ) LAB_DESCRIPTION,

COALESCE(E.DIV1,E.DIV1) BUSINESS_LEVEL_1,

COALESCE(D.DESCRIPTION, null) BUSINESS_LEVEL_1_DESCRIPTION,

COALESCE(E.COST_CENTER,E.COST_CENTER) COST_CENTER,

COALESCE(S.DESCRIPTION, null) DESCRIPTION,

COALESCE(E.POSITION, null) POSITION,

COALESCE(P.DESCRIPTION, null) POSITION_DESCRIPTION,

COALESCE(E.ACTUAL_SALARY_HOURS, E.ACTUAL_SALARY_HOURS) ACTUAL_SALARY_HOURS,

COALESCE(E.HIRE_DATE, E.HIRE_DATE) HIRE_DATE,

COALESCE(E.STATUS, E.STATUS) STATUS,

COALESCE(PA.RATE, NULL) RATE,

coalesce(e.DEFAULT_WORKS_HRS_PERIOD, null)DEFAULT_WORKS_HRS_PERIOD

FROM HRSENSE1.PAEMP1 E

LEFT JOIN hrsense1.PADIV1 D ON E.DIV1 = D.DIV1

LEFT JOIN HRSENSE1.PAPOSI P ON E.POSITION = P.POSITION

LEFT JOIN HRSENSE1.SSCODE S ON E.COST_CENTER = S.CODE

LEFT JOIN HRSENSE1.patram PA ON E.EMPLCODE = PA.EMPLCODE

where s.type = ‘COS’ AND E.EMPLCODE = ‘H0919918268’;