A subquery returning multple rows

Question

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.

score 1 · Answer 1 · answered May 29 '20 at 06:04

What a mess! You should really consider using SQL formatter or - if you can't for some reason - try to format code you write manually. This is unreadable.

Anyway: although subqueries look like a nice way out of your problems, generally - avoid them. Most of those can be rewritten so that you properly join tables involved.

If you choose to use them within the SELECT statement, you have to make sure that each of them returns a single value.

This returns two or more rows:

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

How to fix it? I don't know, I don't have your tables and have no idea what you want. Simple options might be to:

select distinct timediff ...

or

select max(timediff) ...

or

select ...
where rownum = 1

or some other option, but - again, it depends on something we don't know.

score 0 · Accepted Answer · answered May 29 '20 at 06:40

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

Thank you so much both of you. I am completely new to it and trying to figure out. The above query was giving me expected result before adding that subquery. This query prints same rows multiple times and I am not able to use distinct in any column. Any suggestions? — Nagma, May 30 '20 at 06:37
The same row multiple times was the source of your issue originally. Just have to refine your query to select only one, or maybe use row_number() function to select the first of any group. Or if they are truly duplicate across all these columns then use SELECT DISTINCT. — TomC, May 31 '20 at 23:10

A subquery returning multple rows

2 Answers2