Convert SQL Server CTE into Oracle CTE -

here sql server cte, trying convert oracle cte or regular oracle query..

;with cte   (select ac, m, y, d, e, f, cd   tbla   (y = year(getdate()) , m = month(dateadd(month, -1, getdate())))   ),   cte2  (select a.ac,max(a.y)as y, max(a.m) m, max(a.cd) cd  tbl  inner join cte b on b.ac = a.ac  a.cd not null , b.cd null  group a.ac)  , cte3  (select c.ac, c.y, c.m, c.d, c.e, c.f, c.cd  tbla c  inner join cte2 d on c.ac = d.ac , c.y= d.y , c.m = d.m ,       d.cd = c.cd )  select * cte  union  select * cte3; 

assuming didn't have m , y columns reversed on purpose in cte/cte3 select lists, think rewrite query as:

with cte1 (select a.ac,                      a.m,                      a.y,                      a.d,                      a.e,                      a.f,                      a.cd,                      max(case when a.cd not null , b.cd not null a.y end) on (partition a.ac) max_y,                      max(case when a.cd not null , b.cd not null a.m end) on (partition a.ac) max_m,                      max(case when a.cd not null , b.cd not null a.cd end) on (partition a.ac) max_cd                 tbla                      left outer join tblb b on (a.ac = b.ac)) select ac,        m,        y,        d,        e,        f,        cd   cte1  (y = to_char(sysdate, 'yyyy')         , m = to_char(add_months(sysdate, -1), 'mm')) or     (y = max_y         , m = max_m         , cd = max_cd); 

you haven't provided sample data, can't test, worth converting date functions sql server equivalents , testing make sure data returned same.

this way, you're not querying same table 3 times, should improve performance some.