sqloracle-databaseoracle-agile-plm

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