Why do I get error connecting Tableau to Oracle

Question

I am using a SQL code for Oracle, which works perfectly in TOAD. However, when I try to translate the same code in Tableau using Custom SQL, I get following error:

Oracle database error 907: ORA-00907: missing right parenthesis

Please find the SQL code below: Any help would be appreciated

    Select coreid,eid, Period,Period_Test,datastate,imnem,dsrs,Currency,NID,val,NewValue, srsseq,mseq,
case
      when nid = 'T' then to_char(NewValue/1000)
      when nid = 'B' then to_char(NewValue*1000)
      when nid = 'TR' then to_char(NewValue*1000000)
      else to_Char(NewValue)
      END AS FinValue
from (
select coreid,eid, Period,
Period_Test,
datastate,imnem,dsrs,Currency,NID,val,round(Value, 2) as NewValue,srsseq,mseq,
CASE WHEN 
Value is null then coreid||Period||to_compare||'NULL'
else coreid||Period||to_compare||Value 
END AS finalvalue
from
(
select
    fp.eid as eid, 
fp.absref as Period,  
substr(fp.absref,4) || substr(fp.absref,1,2) as Period_Test, 
    fp.datastate as datastate,
        fd.imnem as imnem,fp.CCD as Currency,fp.NID as NID,fd.dsrs as dsrs,
        (select eaval from arrow.eattrs where imnem = 'COREID' and eid = fp.eid) coreid, fd.fvalue as val,
        CASE       
        WHEN fd.fvalue in ('NM','@NA') then null
        when fp.NID = 'T' then to_char(fd.fvalue/1000)
        when fp.NID = 'B' then to_char(fd.fvalue*1000)
        when fp.NID = 'TR' then to_char(fd.fvalue*1000000)
        ELSE to_Char(round(fd.fvalue,2))
        END AS Value,
       dsrs.srsseq as srsseq,
     max(dsrs.srsseq) over(partition by fd.eid,fd.imnem,fd.absref,fd.dset,fd.pid) mseq,
    fd.imnem to_compare 
    from arrow.ipd_fperiods fp, arrow.fdata fd, arrow.dsources dsrs
where fp.eid in (
    29940,29942  ,29943,29944,29946,29947,29948,29949,29954,29956,29957,29959,29966,29967,29968,29969)
and fp.fpid = fd.fpid   
     and fp.pid is NULL
     and fd.imnem in 
('REVTREP','EBITDAA','OPINCADAREP','EBITREP','IB','OANCF.VLP','CAPX','FOCFREP','DIVREP','SHRREPUR','DCFREP','CHE','TDREP','PSTK','CEQ')
and fp.absref like 'Q%'
        and dsrs.dsrs = fd.dsrs)
        where srsseq = mseq);