Rewite PL/SQL query in SQL Server 2005

Rewite PL/SQL query in SQL Server 2005

• goofaholix

  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)
  );
  
  Link
  
  
• Rewite PL/SQL query in SQL Server 2005