Convert SQL Server CTE into Oracle CTE

Question

Here is the SQL Server CTE, trying to convert to Oracle CTE or regular oracle query..

;with cte as 

(Select AC, M, Y, D, E, F, CD 

from tblA
WHere 

(Y = YEAR(GETDATE()) and M = Month(dateadd(month, -1, GETDATE()))) 

), 

cte2 as

(Select A.AC,Max(A.Y)as Y, Max(A.M) as M, Max(A.CD) as CD

from tbl A

Inner join cte B on B.AC = A.AC

WHere A.CD is Not Null and B.CD is Null

Group by A.AC)

, cte3 as

(Select C.AC, C.Y, C.M, C.D, C.E, C.F, C.CD

from tblA C

Inner join cte2 D on C.AC = D.AC and C.Y= D.Y and C.M = D.M and 

    D.CD = C.CD
) 
select * from cte

union

select * from cte3;

Answer 1

Assuming you didn't have the m and y columns reversed on purpose in your cte/cte3 select lists, I think you could rewrite your query as:

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

You haven't provided any sample data, so I can't test, but it would be worth converting the date functions to their SQL Server equivalents and testing to make sure the data returned is the same.

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