Trouble with CASE IN WHERE - 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

Trouble with CASE IN WHERE

Author

Topic

AdenJones
Starting Member

17 Posts

Posted - 2009-09-20 : 03:34:55

The following query keeps complaining about something being wrong with the CASE expression but I can't see it

SELECT tblOrganisation.OrgName,
		tblPosition.PosID,
		tblPosition.OrgID,
		tblPosition.PosName,
		tblPStatus.PStatusName,
		tblJobHistory.*
FROM tblOrganisation
LEFT OUTER JOIN tblPosition
ON tblOrganisation.OrgID = tblPosition.OrgID
LEFT OUTER JOIN tblJobHistory
ON tblJobHistory.PosID = tblPosition.PosID
LEFT OUTER JOIN tblPTimeFrame
ON tblPosition.PosTimeFrame = tblPTimeFrame.PTFID
LEFT OUTER JOIN tblPStatus
ON tblPosition.PosStatus = tblPStatus.PStatusID
WHERE tblOrganisation.Deleted = 0
AND tblPosition.Deleted = 0
AND tblPStatus.PStatusID = 2
CASE WHEN tblJobHistory.JobHistoryID IS NOT NULL THEN AND tblJobHistory.Deleted = 0 END

Any help would be greatly appreciated

YellowBug
Aged Yak Warrior

616 Posts

Posted - 2009-09-20 : 06:58:54

You need to specify a condition in the WHERE clause, and you are missing an AND/OR
Try this instead:
AND (tblJobHistory.JobHistoryID IS NOT NULL AND tblJobHistory.Deleted = 0 )

Hope I have understood this correctly. If not please explain further what the CASE statement should do.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources