select  name,data,pid,Did,Ref_Type,value,
        case Ref_Type
            when 1 then
                        (   -- Start sub-query a
                            select data
                            from   (   -- Start sub-query b
                                       select  data,row_number() over(order by data)-1 val
                                       from    tab4 d
                                       [b]where   d.Eid = b.Did[/b] )   -- end sub-query b
                            where  val=a.value
                       )    -- End sub-query a
            when 2 then
                        to_char(value)
            when 3 then
                        to_char(value)
       end as value
from
    (
        select  b.Name,b.Did,
                b.pid,b.Ref_Type,a.value
        from    tab1 a, tab2 b, tab3 c
        where a.IDX = 31101
        and a.ID = b.ID
        and a.IDX = c.IDX
    ) 

select  b.did
from
    (
        select  b.Name,b.Did,
                b.pid,b.Ref_Type,a.value
        from    tab1 a, tab2 b, tab3 c
        where a.IDX = 31101
        and a.ID = b.ID
        and a.IDX = c.IDX
    ) 

with t1 as (
            select  b.Name,
                    b.Did,
                    b.pid,
                    b.Ref_Type,
                    a.value
              from  tab1 a,
                    tab2 b,
                    tab3 c
              where a.IDX = 31101
                and a.ID = b.ID
                and a.IDX = c.IDX
           ),
     t2 as (
            select  Eid Did,
                    data,
                    row_number() over(partition by Eid order by data) - 1 val
              from  tab4 d
              where d.Eid in (
                              select Did from t1
                             )
           )
select  name,
        data,
        pid,
        t1.Did,
        Ref_Type,
        value,
        case Ref_Type
            when 1 then data
            when 2 then to_char(value)
            when 3 then to_char(value)
        end as value
  from  t1,
        t2
  where t2.did(+) = t1.did
    and t2.val(+) = t1.value
/