[SOLVED] A subquery returning multple rows

A subquery returning multple rows

I am getting an error in the below query.. can anyone help fixing this query

select c.CHANGE_NUMBER, 
(select le.ENTRYVALUE from listentry  le
where le.ENTRYID = c.CATEGORY)as CATEGORY ,
(
            SELECT  last_name || ',' || first_name || '(' || loginid || ')'
            FROM agileuser
            WHERE id = c.ORIGINATOR
        ) initiator,(
            SELECT  last_name || ',' || first_name || '(' || loginid || ')'
            FROM agileuser
            WHERE id = c.owner
        ) coordinator, w.state,

(SELECT description
            FROM nodetable
            WHERE id = w.state
        ) workflow_status , TO_CHAR(w.LOCAL_DATE,'DD-MON-YY HH24:MI:SS')local_date,
      (select  
        (trunc(to_date(B.local_date, 'DD-MON-YY HH24:MI:SS') - to_date(w.local_date, 'DD-MON-YY HH24:MI:SS')))as timediff
        FROM WORKFLOW_PROCESS w INNER JOIN WORKFLOW_PROCESS B ON B.order_by = (w.order_by + 1) 
        where w.change_id=B.Change_id and w.change_id=9946301 )--ORDER BY w.order_by ASC 

       from change c, WORKFLOW_PROCESS w 
        where w.change_id = c.id-- order by w.ORDER_BY)
       and c.CHANGE_NUMBER='NPI001084' 
      order by w.ORDER_BY,local_date ASC

The trunc() query gives multiple records

 select  
     (trunc(to_date(B.local_date, 'DD-MON-YY HH24:MI:SS') - to_date(w.local_date, 'DD-MON-YY HH24:MI:SS')))as timediff
      FROM WORKFLOW_PROCESS w INNER JOIN WORKFLOW_PROCESS B ON B.order_by = (w.order_by + 1) 
      where w.change_id=B.Change_id and w.change_id=9946301 ORDER BY w.order_by ASC

hence not able to run along with this main query.

Solution

Completely untested, and probably with some typos, also possibly joins that should be left joins, and maybe incorrect oracle syntax, but...

select c.CHANGE_NUMBER, 
    le.ENTRYVALUE as CATEGORY
    orig.last_name || ',' || orig.first_name || '(' || orig.loginid || ')' as initiator,
    own.last_name || ',' || own.first_name || '(' || own.loginid || ')' as coordinator,
    w.state,
    n.description as workflow_status,
    TO_CHAR(w.LOCAL_DATE,'DD-MON-YY HH24:MI:SS')local_date,
    trunc(to_date(B.local_date, 'DD-MON-YY HH24:MI:SS') - to_date(w.local_date, 'DD-MON-YY HH24:MI:SS')) as timediff
from change c
join WORKFLOW_PROCESS w on w.change_id = c.id
join listentry le on le.ENTRYID = c.CATEGORY
join agileuser as orig on orig.id = c.ORIGINATOR
join agileuser as own on own.id = c.owner
join nodetable as n on n.id = w.state
JOIN WORKFLOW_PROCESS B ON B.order_by = (w.order_by + 1) 
where c.CHANGE_NUMBER='NPI001084' 
order by w.ORDER_BY,local_date ASC