Rewite PL/SQL query in SQL Server 2005

How do I rewrite the folloiwng query of PL/SQL in SQL Server ?

select 1 from sffnd_stdoper a,sfpl_plan_rev b,sfpl_oper_v c
where b.plan_id = c.plan_id
and b.plan_version = c.plan_version
and b.plan_revision = c.plan_revision
and b.plan_alterations = c.plan_alterations
and a.stdoper_object_id = c.stdoper_object_id
and (b.plan_id,b.plan_version,b.plan_revision)
in (select plan_id, plan_version, max(plan_revision)
from sfpl_plan_rev
where plan_id = c.plan_id
and c.plan_id != a.stdoper_plan_id
group by plan_id, plan_version)
);SQL Server 2005 does not have row constructors so you have to write something like:
with p1
as
(
select plan_id, plan_version, max(plan_revision) as maxrev
from sfpl_plan_rev
group by plan_id, plan_version
)
select 1 from sffnd_stdoper a,sfpl_plan_rev b,sfpl_oper_v c
where b.plan_id = c.plan_id
and b.plan_version = c.plan_version
and b.plan_revision = c.plan_revision
and b.plan_alterations = c.plan_alterations
and a.stdoper_object_id = c.stdoper_object_id
and exists(select *
from p1
where p1.plan_id = c.plan_id
and c.plan_id a.stdoper_plan_id
and p1.pla_id = b.plan_id
and p1.plan_version = b.plan_version
and p1.maxrev = b.plan_revision);
--or
select 1 from sffnd_stdoper a,sfpl_plan_rev b,sfpl_oper_v c
where b.plan_id = c.plan_id
and b.plan_version = c.plan_version
and b.plan_revision = c.plan_revision
and b.plan_alterations = c.plan_alterations
and a.stdoper_object_id = c.stdoper_object_id
and exists(select *
from
(
select plan_id, plan_version, max(plan_revision) as maxrev
from sfpl_plan_rev
group by plan_id, plan_version
) as p1
where p1.plan_id = c.plan_id
and c.plan_id a.stdoper_plan_id
and p1.pla_id = b.plan_id
and p1.plan_version = b.plan_version
and p1.maxrev = b.plan_revision);

How do I rewrite the folloiwng query of PL/SQL in SQL Server ?

select 1 from sffnd_stdoper a,sfpl_plan_rev b,sfpl_oper_v c
where b.plan_id = c.plan_id
and b.plan_version = c.plan_version
and b.plan_revision = c.plan_revision
and b.plan_alterations = c.plan_alterations
and a.stdoper_object_id = c.stdoper_object_id
and (b.plan_id,b.plan_version,b.plan_revision)
in (select plan_id, plan_version, max(plan_revision)
from sfpl_plan_rev
where plan_id = c.plan_id
and c.plan_id != a.stdoper_plan_id
group by plan_id, plan_version)
);

Linksql