Can I do this? - 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

SQL Server 2000 Forums

SQL Server Development (2000)

Can I do this?

Author

Topic

ajthepoolman
Constraint Violating Yak Guru

384 Posts

Posted - 2002-03-14 : 11:58:34

Hello all, I am wondering if I can do this in SQL.

WHERE
CASE WHEN (CHARINDEX(',', F.txtFacilityName) > 0) THEN
RTRIM(ISNULL(LEFT(F.txtFacilityName, CHARINDEX(',', F.txtFacilityName)), '')) LIKE
CASE WHEN PT.txtPersonNameLast IS NOT NULL THEN
PT.txtPersonNameLast
ELSE
RTRIM(ISNULL(LEFT(F.txtFacilityName, CHARINDEX(',', F.txtFacilityName)), ''))
END
AND LTRIM(ISNULL(RIGHT(F.txtFacilityName, CHARINDEX(',', F.txtFacilityName)), '')) LIKE
CASE WHEN PT.txtPersonNameLast IS NOT NULL THEN
PT.txtPersonNameLast
ELSE
RTRIM(ISNULL(RIGHT(F.txtFacilityName, CHARINDEX(',', F.txtFacilityName)), ''))
END
ELSE
RTRIM(ISNULL(F.txtFacilityName, '')) LIKE
CASE WHEN PT.txtPersonNameLast IS NOT NULL THEN
PT.txtPersonNameLast
ELSE
ISNULL(F.txtFacilityName , '')
END
END

Here is what is supposed to happen. I am trying to search on Facilities. Facilities only have a name. No First name, or last name, just a name.

I want to try and find a comma in the name of the facility. If I find one, I want to compare the left side value of the comma against a Last Name parameter. Then I want to compare the right side value against a First Name paramter.

I know it is confusing, but it is what I have to deal with!

As you can see, this is a where statement. When I try to compile the code, I get an error saying "Incorrect Syntax near LIKE". I guess my basic question is: Can I put CASE statements in a where clause before I tell it which variable name it is going to compare against?

Any help would be greatly appreciated!

Adrian

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-03-14 : 12:08:34

How about doing it in reverse; construct a LastName + FirstName value, then compare that to FacilityName:

WHERE CASE Replace(F.txtFacilityName, ', ', ',')
WHEN PT.txtPersonLastName + ',' + PT.txtPersonFirstName
THEN (whatever you want to return if they match)
ELSE IsNull(F.txtFacilityName,'') END

The Replace() is used to remove a comma-space, leaving just the comma.

Edited by - robvolk on 03/14/2002 12:09:07

ajthepoolman
Constraint Violating Yak Guru

384 Posts

Posted - 2002-03-14 : 15:02:53

Thanks for the response!

It is close, but no cigar.

The only problem with it now, is it won't return facilities that don't have a comma in their name.

Oh, well. I will keep working with it!

Adrian

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources