CODEWITHSUNDEEP
sqlsql-server
bdrilling33
bdrilling33

Reputation: 57

Case Expression inside where clause

I have found a few posts that were very similar to what I am wanting to do, but it didn't seem to work. I have this case in my select:

case 
        when h.Posted_Flag=-1 and h.Returned_Flag=0 then 'Posted'
        when h.Posted_Flag=0 then 'Unposted'
        when h.Returned_Flag=1 and h.Posted_Flag=-1 then 'Returned'
        when h.Returned_Flag = 2 then 'Partial Return'
        else '*****'
end as Return_Status,

I would like to place this in my where clause and equal to a variable I have set:

Declare @TicketStatus nvarchar
set @TicketStatus = 'Returned'

and @TicketStatus=(case 
            when h.Posted_Flag=-1 and h.Returned_Flag=0 then 'Posted'
            when h.Posted_Flag=0 then 'Unposted'
            when h.Returned_Flag=1 and h.Posted_Flag=-1 then 'Returned'
            when h.Returned_Flag = 2 then 'Partial Return'
            end)

When I try to run what I have, I get no records returned, when I run this without the AND(case) there are many Retunred Status's with Returned in them.

any help would be great.

thanks BD

Upvotes: 0

Views: 59

Answers (2)

Lamak
Lamak

Reputation: 70638

This is a problem with how you declared your variable. What happens if you do this?

Declare @TicketStatus nvarchar
set @TicketStatus = 'Returned'

SELECT @TicketStatus

The returned value is 'R', not 'Returned', this happens because you didn't give a length to your variable. You should do this:

Declare @TicketStatus nvarchar(15)
set @TicketStatus = 'Returned'

Upvotes: 5

Jodrell
Jodrell

Reputation: 35706

Why don't you just use the condition?

h.Returned_Flag=1 and h.Posted_Flag=-1

Upvotes: 0

Related Questions