July 10, 2008 at 3:19 pm
Hello All,
I am trying to create a complex case statement which involves validating a zipcode (meaning it has to start with either a 3,7, or 8 and be in the ZipCode table). If valid then proceed else return error message. Depending if the zipcode starts with a 3 then i want to match the inputted zip against all the zipcodes in the table starting with the number 3. if inputted zipcode is not in ZipCode table return error message. Depending if the zipcode starts with a 7 then i want match the inputted zipcode against all the zipcodes starting with the number 7 if inputted zipcode is not in ZipCode table return error message, and same for 8.
This is what I have. I know right off the bat that SQL does not like THEN SELECT (*) FROM ZipCodes in the Case statements. I tried it without the ( ) and putting the SELECT Statement in () AS F but that did not work. If its not obvious I'm relatively new to SQL so easy on the criticism. I definitely don't mind feed back though. Thanks for looking
DECLARE
@ipinput INT(5)
IF @ipinput LIKE '%'
BEGIN
SELECT
CASE
WHEN ZipCode LIKE '3%' THEN '3%'
WHEN ZipCode LIKE '7%' THEN '7%'
WHEN ZipCode LIKE '8%' THEN '8%'
ELSE 'InvalidZip'
END AS ValidZip
IF @ipinput LIKE '3%'
BEGIN
SELECT
CASE
WHEN ZipCode LIKE '3%' THEN SELECT (*) FROM ZipCodes
WHERE ZipCode LIKE '3%'
AND
WHERE ZipCode = @ipinput
ELSE 'Invaild Zip starting with 3'
END AS Zip3
IF @ipinput LIKE '7%'
BEGIN
SELECT
CASE
WHEN ZipCode LIKE '7%' THEN SELECT (*) FROM ZipCodes
WHERE ZipCode LIKE '7%'
AND
WHERE ZipCode = @ipinput
ELSE 'Invaild Zip starting with 7'
END AS Zip7
IF @ipinput LIKE '8%'
BEGIN
SELECT
CASE
WHEN ZipCode LIKE '8%' THEN SELECT (*) FROM ZipCodes
WHERE ZipCode LIKE '8%'
AND
WHERE ZipCode = @ipinput
ELSE 'Invaild Zip starting with 8'
END AS Zip8
END
July 11, 2008 at 12:42 am
First, I didnt understand this condition;
IF @ipinput LIKE '%'
What are you trying to check by this condition?
Secondly, for 3,7 and 8;
simply check by
Declare @ipinput as int
Declare @sinput varchar(5)
Set @sinput = rtrim(ltrim(cast(@ipinput as varchar(5))))
if left(@sinput,1) in ('3','7','8')
begin
if Exists(SELECT * FROM ZipCodes
WHERE ZipCode LIKE @sinput + '%' AND ZipCode = @ipinput)
SELECT * FROM ZipCodes WHERE ZipCode LIKE @sinput + '%' AND ZipCode = @ipinput
else
Select 'Invaild Zip starting with ' + @sinput
end
else
Select 'Invaild Zip'
hope this will solve your problem...
Atif Sheikh
July 11, 2008 at 5:16 am
The way to sort out in your mind how CASE works VS. how IF works is this:
IF is a flow-control statement
however
CASE is a function.
Yes, it does not have "( .. )" but it still acts exactly like a function (technically its an expression operator, but think of it as a function).
IF's purpose is to determine which other Statements get executed.
CASE's purpose is to determine what value is returned to the rest of the expression. So you cannot put statements like "SELECT ..." in a CASE expression anymore than you could put them in a LIKE expression or in a LEFT(..) function.
Note that you can, however, put a sub-Select expression, "(Select ..)" in a CASE function. Note the parenthesis which are required. Also, sub-Select's in this context may only return a single value. That is, one column and one row.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
July 11, 2008 at 7:22 am
Try this:
declare @ipinput char(5)
select *
from dbo.ZipCodes
where ZipCode = @ipinput
and right(@ipinput, 1) in ('3', '7', '8')
First, you should store Zip Codes as character data, not as integers. Some Zips begin with zero, and integers won't store that way.
Second, you can't declare a variable in SQL as "Int(5)". Not a valid data type. In SQL, Int is 4-byte (goes up to about 32-billion).
Third, you're using Case the way it's used in Visual Basic (possibly other languages, VB is the one I'm used to), not the way it's used in SQL. It doesn't do the same thing in SQL, and won't work the way you wrote it.
Fourth, if a Zip Code is equal to the input variable, then the first character is also equal, so checking both things is redundant.
Fifthy, if you want an error if the Zip doesn't begin with the desired digits, check out Raiserror in Books Online.
I hope that helps. I'm going to guess that you're coming from a background in VB and hitting SQL relatively fresh. It takes a bit to get used to, but you found the right web page to learn at.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 11, 2008 at 9:14 am
Atif,
IF @ipinput LIKE '%' means if value (1,22,abc,bec,c..etc) is 'anything but null' check to see if that 'anything but null' value starts with a 3, 7, or 8. Once i check that anything value thats the first step of the process. Next, to categorize whether it can proceed or not. IF @ipinput LIKE '3%', IF @ipinput LIKE '7%', and IF @ipinput LIKE '8%' checks to see if that zipcode entered is in the ZipCodes table. Example someone might type in 72512 which will pass the first part because it starts with a 7 and proceed to IF @ipinput LIKE '7%' and will check to see if that zipcode is in the ZipCodes table. If its in the table it will return the values of that row zip,city,state, whatever other fields are in the ZipCodes table. If its not in the table then it will return an error message. I understand what you are saying i wanted to say away from If statements but i think will have to use them.
Thanks,
-Chris
July 11, 2008 at 9:20 am
rbarryyoung,
Yeah the more i start to reconsider using IF statements the more it makes sense to use them rather than Case. I just did not want to take that route. But at this point it seems more logical for this situation. Thanks for the explanation on the difference between IF vs. Case
-Chris
July 11, 2008 at 9:29 am
GSquared,
Thanks for the pointers. I did not think of using varchar but i see why. With the tips I got from you and the other two posts I should have enough to work with. Yeah I wrote in my original post 'If its not obvious I'm relatively new to SQL so easy on the criticism' but you probably over read that part. I rather you over read that than my code. :hehe: thanks again.
-Chris
July 11, 2008 at 9:38 am
ctics112 (7/11/2008)
GSquared,Thanks for the pointers. I did not think of using varchar but i see why. With the tips I got from you and the other two posts I should have enough to work with. Yeah I wrote in my original post 'If its not obvious I'm relatively new to SQL so easy on the criticism' but you probably over read that part. I rather you over read that than my code. :hehe: thanks again.
-Chris
Actually, I wasn't criticizing, I was trying to educate. I appologize if it came accross otherwise. One of the things I try to do is concisely point out why something isn't working, rather than just giving you code that will work, so that, in the future, you can prevent the errors.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 11, 2008 at 9:49 am
GSquared,
I did not take offense to what you said. No hard feelings on my end. And yeah I rather learn than get hand fed answers. If I wanted to do that I would not have taken the time to code.
Thanks,
-Chris
July 11, 2008 at 3:10 pm
Cool biz.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply