Syntax Update - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Syntax Update

Author

Topic

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2010-04-01 : 07:56:39

Please could you change this acces sql select statement to sql server 2005..

Thanks

SELECT tblProjectScheduleEv.ProjectPracticeID, Format([tblprojectPractices].[PracticeGPID],"000000") AS GPID, 
tblODSPractices.MainContact, Format([EvBFDate],"d mmm yy") AS [BF Date], lkupEvTypes.EvType, Format([RemLetterGenDate],"d mmm yy") 
AS [Rem Lttr], tblProjectScheduleEv.EvAccount AS Comment
FROM tblODSPractices INNER JOIN (qryRecentScheduledEvents INNER JOIN ((tblProjectScheduleEv INNER JOIN tblProjectPractices ON 
tblProjectScheduleEv.ProjectPracticeID = tblProjectPractices.ProjectPracticeID) INNER JOIN lkupEvTypes ON 
tblProjectScheduleEv.EvTypeID = lkupEvTypes.EvTypeID) ON (qryRecentScheduledEvents.ProjectPracticeID = tblProjectScheduleEv.ProjectPracticeID) 
AND (qryRecentScheduledEvents.MaxOfEvBFDate = tblProjectScheduleEv.EvBFDate)) ON tblODSPractices.PracticeEID = tblProjectPractices.PracticeEID
WHERE (((tblProjectScheduleEv.EvBFDate)<=Date()) AND ((tblProjectPractices.ProjectID)=[forms]![frmNoticeBoard]![lstProjects]))
ORDER BY Format([tblprojectPractices].[PracticeGPID],"000000");

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2010-04-01 : 07:58:47

Have you tried converting it by yourself? show us what you did.

Harsh Athalye
http://www.letsgeek.net/

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2010-04-01 : 08:10:59

[code] SELECT TOP (100) PERCENT dbo.tblProjectScheduleEv.ProjectPracticeID, RIGHT('000000' + CAST(dbo.tblProjectPractices.PracticeGPID AS varchar(10)), 6)
AS GPID, dbo.tblODSPractices.MainContact, CONVERT(varchar(20), dbo.tblProjectScheduleEv.EvBFDate, 5) AS [BF Date], dbo.lkupEvTypes.EvType,
CONVERT(varchar(20), dbo.tblProjectScheduleEv.RemLetterGenDate, 5) AS [Rem Lttr], dbo.tblProjectScheduleEv.EvAccount AS Comment
FROM dbo.tblODSPractices INNER JOIN
dbo.qryRecentScheduledEvents INNER JOIN
dbo.tblProjectScheduleEv INNER JOIN
dbo.tblProjectPractices ON dbo.tblProjectScheduleEv.ProjectPracticeID = dbo.tblProjectPractices.ProjectPracticeID INNER JOIN
dbo.lkupEvTypes ON dbo.tblProjectScheduleEv.EvTypeID = dbo.lkupEvTypes.EvTypeID ON
dbo.qryRecentScheduledEvents.ProjectPracticeID = dbo.tblProjectScheduleEv.ProjectPracticeID AND
dbo.qryRecentScheduledEvents.MaxOfEvBFDate = dbo.tblProjectScheduleEv.EvBFDate ON
dbo.tblODSPractices.PracticeEID = dbo.tblProjectPractices.PracticeEID
WHERE (dbo.tblProjectScheduleEv.EvBFDate <= GETDATE())
ORDER BY GPID [/code]

But the result I receive from SQL and Access are quite different

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2010-04-01 : 08:28:14

You JOIN syntax seems bit odd. It should be like this:


FROM 
	qryRecentScheduledEvents RSE INNER JOIN tblProjectScheduleEv PSE ON RSE.ProjectPracticeID = PSE.ProjectPracticeID AND RSE.MaxOfEvBFDate = PSE.EvBFDate
	INNER JOIN tblProjectPractices PP ON PSE.ProjectPracticeID = PP.ProjectPracticeID 
	INNER JOIN tblODSPractices ODS ON OSD.PracticeEID = PP.PracticeEID
	INNER JOIN lkupEvTypes ET ON PSE.EvTypeID = ET.EvTypeID

Also using alias will make your life simple to understand queries.

Harsh Athalye
http://www.letsgeek.net/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources