Using IF statements in SQL 1

[ShawnMolloy]

Hey,

I have a column that returns an INT. I want to replace the int with a word, like if Status = 1 then Status = "Read". Here is my query:

SELECT
A.MessageId, A.Sender, A.Recipient, A.Status, A.Subject,A.Message,A.DateSent,
B.UserName, B.MainImage

FROM message [A]
LEFT JOIN user B ON A.Sender = B.UserId
WHERE A.Recipient=@userId


Thanks!

- shawn

 

[PHV]

A starting point:
SELECT
A.MessageId, A.Sender, A.Recipient,
CASE WHEN A.Status = 1 THEN 'Read'
WHEN A.Status = 2 THEN 'Write'
ELSE 'Unknown' END Operation,
A.Subject,A.Message,A.DateSent,
B.UserName, B.MainImage
FROM message A
LEFT JOIN user B ON A.Sender = B.UserId
WHERE A.Recipient=@userId


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[ShawnMolloy]

Hi PHV,

Thanks once again for your great help! This works great. Using CASE in SQL doesn't seem that intuitive. Is there another kind of conditional operator that works like the if else operator?

Thanks!

-- shawn

 

[gmmastros]

Shawn,

The case syntax isn't so bad once you get used to it. I explain it a little in this thread. thread183-1245512 I hope it helps you to understand it a little better.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[PHV]

Using CASE in SQL doesn't seem that intuitive
A simpler way:
...
CASE A.Status
WHEN 1 THEN 'Read'
WHEN 2 THEN 'Write'
ELSE 'Unknown' END Operation
...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[ShawnMolloy]

That makes more sense readibilty wise. Moving the condition next to the CASE statment provides a whole nother perspective.

Thanks again.

-- shawn

 

[ShawnMolloy]

Hi,

After your advice I tried using the CASE statement for another query problem I needed to solve and found that it didn't work as expected.

@orderByColumn string
...
-- set sort order
CASE
WHEN
@orderByColumn = "DateAdded"
THEN
ORDER BY = DateAdded
END

After reading up on the CASE statement I realized that the reason why it wasn't working was because the case statement compares a column, and "ORDER BY" needs a valid column name.

what do you think is an alternative to this method?

Thanks!

-- shawn

 

[JarlH]

Shawn,

Try to move the case into the ORDER BY specification,

ORDER BY CASE WHEN ... END

 

[gmmastros]

I've been working on an on-line survey lately. One of the requirements was to list the items in a random order, except for 'other' which should always appear at the bottom of the list. Check it out...

Declare @Colors Table(ColorId Integer, Color VarChar(20))

insert into @Colors values(1, 'Red')
insert into @Colors values(2, 'Blue')
insert into @Colors values(3, 'Green')
insert into @Colors values(4, 'Orange')
insert into @Colors values(5, 'Yellow')
insert into @Colors values(6, 'Purple')
insert into @Colors values(7, 'Other')

Select ColorId, Color
From   @Colors
Order By Case When Color Like 'Other%'
              Then 1
              Else 0
              End,
         NewId()

You can copy/paste this to query analyzer and run it, because this uses a table variable. Run this multiple times and notice how 'other' always stays at the bottom, but the rest of the colors are listed randomly. The NewId() stuff allows use to order things randomly. I could just as easily have done...

Select ColorId, Color
From   @Colors
Order By Case When Color Like 'Other%'
              Then 1
              Else 0
              End,
         [!]Color[/!]

Again, other is always at the bottom, but the other colors are alphabetical. Now, suppose I wanted the user to be able to choose which column to order by.

Declare @OrderBy VarChar(10)
[blue]Set @OrderBy = 'Color'
Set @OrderBy = ''[/blue]

Select ColorId, Color
From   @Colors
Order By Case When @OrderBy = 'Color'
              Then Color
              Else Right('000000' + Convert(varChar(20), ColorId), 6)
              End

Try running this with @OrderBy = 'color' and again with any other value.

Notice that I have Right('000000' + Convert(varChar(20), ColorId), 6) This is so that each condition of the case statement will have the same data type.

I hope you find this useful.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom