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.
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