here Oracle Query pls rewrite in to mssql

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)

here Oracle Query pls rewrite in to mssql

Author

Topic

sponguru_dba
Yak Posting Veteran

93 Posts

Posted - 2005-12-09 : 09:01:03

SELECT
mfu.UserUID,mfu.LoginID,mfu.FirstName,mfu.LastName,mfu.Key1,mfu.Key1Desc,mfu.Key2,mfu.Key2Desc,mfu.Role,mfu.RoleDesc,
mfu.UserClass,mfu.SkillList,mfu.PrivilegeList,mfu.ZIP,mfu.TZ,mfu.ActivationDate,mfu.DeactivationDate,mfu.PhoneNumber,
mfu.FaxNumber,mfu.Email,mfu.Creation,mfu.CreatedBy,mfu.UserUID,mfu.Expiration,mfu.PartitionID,mfu.DST,mfu.Latitude,
mfu.Longitude,mfu.LanguageCode,mfu.SessionTimeout,ubu.BUID,bu.BusinessUnitDesc,bu.OrgType,us.RoutingAreaList,
us.UserScheduleUID,us.ShiftStartDateTime,us.ShiftStopDateTime,us.ScheduleDate,us.AtWork,us.BreakList,ua.LastLatitude,
ua.LastLongitude,ua.StatusHistory,ua.GISTrack,ua.GPSTrack,ua.HHStatusCode,ua.TruckID,ua.GPSUnitID
FROM
MFUser mfu,UserBU ubu, BusinessUnit bu,UserSchedule us, UserActivity ua
WHERE
ubu.UserUID = mfu.UserUID
AND ubu.BUID = bu.BUID
AND ubu.Creation >= mfu.Creation
AND ubu.Expiration = mfu.Expiration
AND ubu.Creation >= bu.Creation
AND ubu.Expiration <= bu.Expiration
AND mfu.UserUID = us.UserUID(+)
AND mfu.UserUID = ua.UserUID(+)
AND us.ScheduleDate(+) = '2005-12-09'
AND ua.ActivityDate(+) = '2005-12-09'
AND mfu.Expiration = '9999-01-01'

ORDER BY mfu.LoginID

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2005-12-09 : 10:51:14

I think the (+) bit indicate that LEFT JOIN's should be used on these conditions.
I've come across this before in PervasiveSQL.

Change your
"FROM MFUser mfu,UserBU ubu, BusinessUnit bu,UserSchedule us, UserActivity ua"
into something like
"FROM MFUser mfu
INNER/LEFT JOIN UserBU ubu on mfu.y = ubu.x
INNER/LEFT JOIN BusinessUnit bu on bu.z = ?"
etc

putting in INNER or LEFT as appropriate based on the various AND clauses.
MOST of the WHERE clause should move up to the "ON" clauses.

Have a go yourself and post your efforts for review/correction.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources