Rewrite PL/SQL query in SQL Server

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Rewrite PL/SQL query in SQL Server

Author

Topic

imtu_174
Starting Member

9 Posts

Posted - 2005-10-13 : 20:09:20

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)
);

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2005-10-14 : 00:33:16

I think this is what you want:

select	Top 1
	[YourFieldListHere]
from	sffnd_stdoper a,
	inner jon sfpl_oper_v c on a.stdoper_object_id = c.stdoper_object_id		
	inner join sfpl_plan_rev b
		on 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
	inner join --MaxRevisions	
		(select	plan_id,
			plan_version,
			max(plan_revision) as plan_revision
		from	sfpl_plan_rev
		group by plan_id,
			plan_version) MaxRevisions
		on b.plan_id = MaxRevisions.plan_id
		and b.plan_version = MaxRevisions.plan_version
		and b.plan_revision = MaxRevisions.plan_revision

But my memory is fuzzy on the meaning of the != operator in your original statement.

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2005-10-14 : 01:38:42

that IN list is the tricky part.

I think that the != is simply a not equals.

-ec

mmarovic
Aged Yak Warrior

518 Posts

Posted - 2005-10-14 : 09:04:52

Use exists instead of in. Since constant is returned (1) I guess it is query checking for existence so if exists(select * ...) can be used.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources