I thought this would be simple. I'm writing a CASE statement, and I need to use an OR. I cannot figure this out. The query is like:
SELECT City, CASE WHEN City = 'Chicago' THEN 'Midwest'
WHEN City = 'Cincinnati' THEN 'Midwest' OR 'Mid-Atlantic'
ELSE 'Where ever' END AS Region
FROM LocationTable
Is there any way for the THEN part of the statement to be one or the other? It doesn't make too much sense in the SELECT statement, but I'm using it in a JOIN to match to the region.
Thank you for any help.
November 4, 2021 at 8:44 pm
I think the problem is that a CASE statement cannot return 2 values like that.
The idea of a CASE statement in SQL is that if the lookup value is X then the return value must be Y.
What I would do in your case is have a scenario like this:
INSERT INTO LocationTable (City)
VALUES ('Cincinnati')
So you have 1 row in the table. What do you need your result look like?
Now you say you are using it in a JOIN. Seeing how you are using it in the JOIN will help. My GUESS, without seeing your JOIN, is that you will need to rewrite it as something like:
ON (City='Cincinnati' AND (Region='Midwest'OR Region='Mid-Atlantic') OR (City='Chicago' AND Region='Midwest')
BUT we can't see what you have, so the above is just a guess.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
November 4, 2021 at 9:11 pm
select 'Midwest' OR 'Mid-Atlantic'
Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'OR'.
Completion time: 2021-11-04T21:11:04.2830349+00:00
November 4, 2021 at 10:23 pm
This was removed by the editor as SPAM
November 5, 2021 at 10:01 am
I would probably create a mapping table between City and Region:
create table dbo.City2RegionMap (
City varchar(20) not null,
Region varchar(20) not null
)
INSERT INTO dbo.City2RegionMap (City, Region)
VALUES
('Chicago', 'Midwest'),
('Cincinnati', 'Midwest'),
('Cincinnati', 'Mid-Atlantic')
Depending on your needs you could then incorporate this mapping table into your qeueries as joins or exists subqueries. Anyway I think this is the best and most flexible way to handle the problem you've identified.
In the long run you'll come to appreciate such a mapping table.
November 5, 2021 at 1:03 pm
Wow! I put out a quick question before I need to go in for an MRI and I come back to find this comment. That is really rude. Thank you for making a tough situation worse.
November 5, 2021 at 1:40 pm
Oh, I think you mean the comment by Joe Celko about your low score etc.
Yes, I meant that. I really can't believe I reached out for help and was personally talked down to like that. Why would I have a high score on a site with such pettiness. Very disappointed. Being a woman in this field is hard enough without some stranger putting you down when you reach out for help.
November 5, 2021 at 1:46 pm
Can you explain what you are trying to do with the query?
You cannot return two values fin the THEN and you also cannot OR two strings together ( THEN 'Midwest' OR 'Mid-Atlantic').
I think it might be the case that you want to return 'Midwest OR Mid-Atlantic' instead of 'Midwest' OR 'Mid-Atlantic' ?
November 5, 2021 at 1:59 pm
kaj wrote:Oh, I think you mean the comment by Joe Celko about your low score etc.
Yes, I meant that. I really can't believe I reached out for help and was personally talked down to like that. Why would I have a high score on a site with such pettiness. Very disappointed. Being a woman in this field is hard enough without some stranger putting you down when you reach out for help.
Yeah, well don't let that lead you to believe all apples in the basket are rodden. I'm sure he means well, but this is not the first time for such types of comments. Look it up. He just can't help himself it seems. I'm always a little wary about reading his comments, because they can be cringe-worthy. He has valuable insights at times, but to me he just comes across as an overly pedantic scholar lacking in people skills. He really is a character!
November 5, 2021 at 2:07 pm
What I'm actually doing is more complicated than what I posted. I just wanted the quick answer -- Can you do this? Yes/no. Seems the answer is "no". But since I do have to figure this out, I'll explain the actual situation.
I have a table with 3 letter codes, and I have a comment table with comment codes. The comment code used is dependent on the 3 letter code. So if "ABC" then use comment code "ABCDEF". But one three letter code could be one of two comment codes. So the real query is more like this:
SELECT a.3ltrcode, b.CommentCode
FROM 3ltrcodeTBL a
INNER JOIN Comments b ON b.CommentCode =
(CASE WHEN a.3ltrcode = 'TRT' THEN 'REOPEN'
WHEN a.3ltrcode = 'BBB' THEN 'CLOSED'
ELSE END)
But the BBB code could be a different code "BCLOSE". It seems so simple, but I'm stuck on this little speed bump.
Should your example case statement end in ELSE 3ltrcode END instead of ELSE END? I assume you actually want to join on a3ltr code except for these/similar translations.
Comments is dependent on a value that apparently doesn't exist in a3ltrcode, so it looks like you need a CommentCode in a3ltrcode that does the translation you're trying to perform in the case statement for those missing codes. Then you could just join on a.CommentCode = b.CommentCode.
If that's not possible (e.g., this is a 3rd-party vendor database), you could build a mapping table (really replacement table unless there are other columns in a3ltrcode you're not showing) with a3ltrcode and CommentCode (even in another database if you can't put it in this database). In that case, I hope these codes are fairly static.
CASE statements in the join are going to hurt the ability to use an index, reducing performance... and get really ugly if you have many such translations
November 5, 2021 at 2:46 pm
From what you say maybe you could join the tables with a wildcard?:
SELECT a.3ltrcode,
b.CommentCode
FROM 3ltrcodeTBL a
INNER JOIN Comments b
ON b.CommentCode like a.CommentCode + '%'
November 5, 2021 at 3:07 pm
I have a table with 3 letter codes, and I have a comment table with comment codes. The comment code used is dependent on the 3 letter code. So if "ABC" then use comment code "ABCDEF". But one three letter code could be one of two comment codes. So the real query is more like this:
SELECT a.3ltrcode, b.CommentCode
FROM 3ltrcodeTBL a
INNER JOIN Comments b ON b.CommentCode =
(CASE WHEN a.3ltrcode = 'TRT' THEN 'REOPEN'
WHEN a.3ltrcode = 'BBB' THEN 'CLOSED'
ELSE END)
But the BBB code could be a different code "BCLOSE". It seems so simple, but I'm stuck on this little speed bump.
I find that difficult to parse, but I think the only way is to use a UNION ALL and therefore split the query in two
SELECT a.a3ltrcode, b.CommentCode
FROM a3ltrcodeTBL a
INNER JOIN Comments b ON b.CommentCode =
CASE
WHEN a.a3ltrcode = 'TRT' THEN 'REOPEN'
WHEN a.a3ltrcode = 'BBB' THEN 'CLOSED'
ELSE 'Something else'
END
UNION ALL
SELECT a.a3ltrcode, b.CommentCode
FROM a3ltrcodeTBL a
INNER JOIN Comments b ON b.CommentCode =
CASE
WHEN a.a3ltrcode = 'BBB' THEN 'BCLOSE'
ELSE 'Something else'
END
November 5, 2021 at 3:37 pm
Or maybe even better, simply reverse the order of the join criteria? Might work... 🙂
SELECT a.a3ltrcode, b.CommentCode
FROM a3ltrcodeTBL a
INNER JOIN Comments b ON a.a3ltrcode =
CASE
WHEN b.CommentCode = 'REOPEN' THEN 'TRT'
WHEN b.CommentCode = 'CLOSED' THEN 'BBB'
WHEN b.CommentCode = 'BCLOSE' THEN 'BBB'
ELSE 'Something else'
END
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply