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.
| Author |
Topic |
|
bubberz
Constraint Violating Yak Guru
289 Posts |
 Posted - 2007-02-01 : 17:43:15
|
| I have my where clause as follows, but it's not working:WHERE (WPID LIKE @WBS1 + '.' + CASE WHEN @WBS2 = '' THEN substring([Number], 4, 2) = __ ELSE substring([Number], 4, 2) = @WBS2 END CASE)I give the user two paramters.The first parameter is to populate the first two characters of a string. There is a "." then another two characters. What I'm trying to do is if the user types nothing in the second parameter, then I use the underscore characters so it can be any two characters. If they do enter two characters, then I want to use them for the 2nd part of the string, hence character 4 and 5. |
|
|
jhermiz
3564 Posts |
Posted - 2007-02-01 : 22:12:41
|
quote:
Originally posted by bubberz
I have my where clause as follows, but it's not working:WHERE (WPID LIKE @WBS1 + '.' + CASE WHEN @WBS2 = '' THEN substring([Number], 4, 2) = __ ELSE substring([Number], 4, 2) = @WBS2 END CASE)I give the user two paramters.The first parameter is to populate the first two characters of a string. There is a "." then another two characters. What I'm trying to do is if the user types nothing in the second parameter, then I use the underscore characters so it can be any two characters. If they do enter two characters, then I want to use them for the 2nd part of the string, hence character 4 and 5.
Try doing this outside of the where condition as it makes it much more readable and correct.Something to this effect:DECLARE @WBS_TEMP varchar(20)SELECT @WBS_TEMP = @WBS2IF @WBS_TEMP IS NULL BEGIN --do whatever you want to wbs_tempENDELSEBEGIN --do whatever you want when its not nullEND--now perform your dataset SELECT WITH WHERE CONDITION concatenating--WBS1 with WBS_TEMPSo now basically you get rid of all that casing within the where and the where just becomes:WHERE WPID LIKE ....
Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]RS Blog -- [url]http://weblogs.sqlteam.com/jhermiz[/url] |
 |
|
|
bubberz
Constraint Violating Yak Guru
289 Posts |
Posted - 2007-02-01 : 22:19:08
|
| jhermiz,Thanks for the replies. I should have explained more of what I'm trying to do on my post.The user will want to look for data via a particular number/pattern which looks similar to the following examples:01.03.06.01.01.1001.06.16.05.01.01The first two characters in the string are called the first element, the second two characters after the first '.' are called the second element, the third set of two characters are called the 3rd element after the 2nd '.', etc.What I'm going to try to do is present the user with 5 parameter text boxes so they can acutally "build" the number/pattern they are looking for by specific elements.Let's say they leave the first four parameter text boxes blank or NULL, and they enter 10 for the 5th element, this should only bring back data such as:01.01.02.02.1001.06.16.12.1001.08.05.05.10...etc. Another example might be let's say they enter 01 for the 1st element parameter, 06 for the 2nd parameter, and 10 for the 5th parameter, this should only bring back data such as:01.06.03.03.1001.06.02.02.1001.06.01.01.10...etc.I was wondering (I'm not at work now), if I made each of the 5 parameters to allow NULL, then did something like for the where clause:WHERE substring(WPID, 1, 2) like @WBS and substring(WPID, 4, 2) like @WBS2 and substring(WPID, 7, 2) like @WBS3 and substring(WPID, 10, 2) like @WBS4 andsubstring(WPID, 12, 2) like @WBS5 |
|
|
|
bubberz
Constraint Violating Yak Guru
289 Posts |
Posted - 2007-02-02 : 09:19:22
|
| ...this worked:WHERE WPID LIKE (CASE @WBS1 WHEN '' THEN '__' ELSE @WBS1 END + '.' + CASE @WBS2 WHEN '' THEN '__' ELSE @WBS2 END + '.' + CASE @WBS3 WHEN '' THEN '__' ELSE @WBS3 END + '.' + CASE @WBS4 WHEN '' THEN '__' ELSE @WBS4 END + '.' + CASE @WBS5 WHEN '' THEN '__' ELSE @WBS5 END) |
|
|
|
|
|
|
|
|
where clause to use Case for parameters