return multiple column data from subquery

user_2303858 0
Viewed 31

I have a functional query, however, I now need to add an additional column that would display data from one of the sub-queries. Im a bit stuck on how to do this.

Ive considered modifying the existing sql into a join type query, but have been unsuccessful. 

--
select case when max(intid) != min(intid) then max(intid) end as INTID_NEW,min(intid) as INTID_PREV
from   DFUND
where   ANum in
    (select ANum from DFUND              
        where intid in
            (select intid as pair
            from AReview
            where Rule = 3366
            and trunc(RECVDDATE) >= trunc(sysdate-4) 
                        )
    )
group by ANum
order by ANum;
--

Here is what the current query returns

 INTID_NEW INTID_PREV
---------- ----------
   4421156    3450805
   4426479    3174829

--

I would like it to return the following:

 INTID_NEW INTID_PREV RECVDDATE
---------- ---------- -----------
   4421156    3450805 01-MAY-2019
   4426479    3174829 04-MAY-2019

--

oracle sql
1 Answers

You could use INNER JOIN instead of IN clause and select from the subquery also the column you need 

    select case when max(intid) != min(intid) then max(intid) end as INTID_NEW, min(intid) as INTID_PREV, t.my_RECVDDATE 
    from   DFUND
    INNER JOIN (
        select ANum,  t2.my_RECVDDATE
        from D  
        INNER JOIN   (
          select intid , trunc(RECVDDATE) as my_RECVDDATE
                from AReview
                where Rule = 3366
                and trunc(RECVDDATE) >= trunc(sysdate-4) 
          ) t2 ON t2.intid =  DFund.intid
    ) t on t.ANum = DFUND.ANum 
    group by ANum,  t.my_RECVDDATE 
    order by ANum;
user_35223120
Related