SQL How to rewrite data and then sort or filter this data

Question

I need to write some SQL where I selectively rewrite some data in a table and then sort and / or filter overloaded data. Here's a very simplified example ...

I have a table with pet names called PET

PET_NAME PET_SPECIES PET_PK ---------- ----------- ----------- Barney Dog {PETGUID1} Fergus Cat {PETGUID2} Meatball Hamster {PETGUID3} 

I have another table that contains my life status LIFE

 LIF_PK LIF_STATUS LIF_PET_FK ---------- ------------ ------------ {LIFGUID1} Alive {PETGUID1} {LIFGUID2} Alive {PETGUID2} {LIFGUID3} Dead {PETGUID3} 

I want to report on pets and their life status.

 SELECT PET_NAME, PET_SPECIES, LIF_STATUS FROM PET INNER JOIN LIFE ON LIF_PET_PK=PET_PK 

If the pet is dead, I don’t want to report this name and simply “DEADPET” instead.
Too many memories. And then I want to order pet names.

I thought I could create a proxy variable and then set it to the name of the pet in the case case only when the animal was alive, for example ...

 DECLARE @petName nvarchar(18) SELECT @petName=(CASE LIF_STATUS='Alive' THEN PET_NAME ELSE 'DEADPET' END), PET_SPECIES, LIF_STATUS FROM PET INNER JOIN LIFE ON LIF_PET_PK=PET_PK ORDER BY @petName 

But I can not do this, TSQL will not allow it.

In reality, of course, this has nothing to do with pets and is related to financial loans {Meatball Lives!} But the principle is the same.

Any help I could get with this would be greatly appreciated, thanks in advance.

Answer 1

I believe the following should work for you (also, including fixing the error regarding variable assignment):

 SELECT (CASE LIF_STATUS='Alive' THEN PET_NAME ELSE 'DEADPET' END) as `PET_NAME` PET_SPECIES, LIF_STATUS FROM PET INNER JOIN LIFE ON LIF_PET_PK=PET_PK ORDER BY `PET_NAME`