Using an OR in a CASE statement

Question

Using an OR in a CASE statement

Viewing 3 posts - 16 through 17 (of 17 total)

You must be logged in to reply to this topic. Login to reply

Jonathan AC Roberts SSCoach Points: 18271 More actions · Answer 1

For this type of design it is normal practice to have a table to join on that relates the 3-character code to the Comments table.

e.g:

CREATE TABLE a3ltrcodeTBL_Comments 
(
    [3ltrcode] char(3) NOT NULL,
    [CommentCode] varchar(10) NOT NULL
);

But you could put this as an inline table expression into your query:

SELECT a.[3ltrcode], b.CommentCode
  FROM [3ltrcodeTBL] a
 INNER JOIN (VALUES ('TRT', 'REOPEN'),
                    ('BBB', 'CLOSED'),
                    ('BBB', 'CLOSED2')) x(ThreeLetterCode,CommentCode)
         ON x.ThreeLetterCode = a.[3ltrcode]
 INNER JOIN Comments b 
         ON b.CommentCode = x.CommentCode;

amyfgehring Old Hand Points: 388 More actions · Answer 2

The dam broke in my brain when ratbak said "mapping table". I made a temporary crosstab table mapping the 3ltrcodes to the comment codes. Thank you.

jcelko212 32090 SSCrazy Eights Points: 9295 More actions · Answer 3

30 years ago, when I was on the ANSI X3 H2 SQL standards committee, I used to go out of my way not to offend snowflakes. The result was they never learned anything! They kept using the wrong terms, inventing their own syntax, and insisting that the compiler just wasn't doing what they meant it to do (it insisted on doing what it was supposed to do, dammit!).

You have a chance to learn, for free, from an expert who wrote the standards for the language, thousands of articles, and has 10 books still in print. Get over yourself. I still remember when Dykstra said "you are doing it completely wrong"; I had the good sense and lack of arrogance to listen to him. I could have taken the approach that he was talking down and being offensive. Being a mature adult, however, I realized he had every right and a lot of proof to say what he said.

Please post DDL and follow ANSI/ISO standards when asking for help.