Alias in CASE possible?

[ServerStorm]

Hi is there some way that output can have different aliases for the THEN or ELSE of the CASE?

SELECT
  CASE WHEN prg.program_name LIKE '%Family Emergency%' 
        THEN l2prg.registration_number as 'Registrant Number' 
        ELSE l2prg.registration_number as 'Program Number' 
   END

This does not work and I have tried various bracketing that did not work. It is not a huge deal if this can't be done because it easy enough to do this programatically but it would be nice to know if there is some way to do this?

Steve

[r937]

you're on the right track but barking up the wrong tree

offtopic: anyone familiar with the classic book "Go Dog Go"?

what you want is something like this --

SELECT l2prg.registration_number
     , CASE WHEN prg.program_name LIKE '%Family Emergency%' 
            THEN 'Registrant Number' 
            ELSE 'Program Number' 
        END AS number_type

although in my experience a yellow flag should go up whenever the same column sometimes means one thing and sometimes another

[ServerStorm]

you're on the right track but barking up the wrong tree

offtopic: anyone familiar with the classic book "Go Dog Go"?

That is a fantastic book that my kids loved when they were little!

what you want is something like this --

SELECT l2prg.registration_number
     , CASE WHEN prg.program_name LIKE '%Family Emergency%' 
            THEN 'Registrant Number' 
            ELSE 'Program Number' 
        END AS number_type

Yes that divergent thinking thing coming into play again. Thank you.

although in my experience a yellow flag should go up whenever the same column sometimes means one thing and sometimes another

The customer for this application has a primary program that they want to display and input in separate fields than the other programs they have; although All these programs have a unique registrant number, program name and registration date.

If I was to break this into separate tables then I would have a complete duplicate structure to the programs table. Does it make sense to break these apart or does this way of doing things - in this case - make sense?

[r937]

Does it make sense to break these apart ...

no, i do not like that hat

[ServerStorm]

Ok, already rolled ahead keeping as is...

off topic: you have quite the plethora of classic book quotes because the 'no, i do not like that hat' is CAT in the HAT...NICE

Thank you.

[r937]

Aside from being a great tool for teaching, this book is also a humorous tale for the reader. Several times throughout the book, one dog wearing a simple hat asks another dog whether or not he likes it. He bluntly replies, “I do not like that hat,” and the two part ways. This happens several more times with several more plain hats, and each time the dog replies “I do not like that hat.” At the large dog party at the end of the book, the first dog is wearing an over-the-top, outlandish party hat that is borderline ridiculous; it is at this time that the second dog agrees that it is a great hat, and the two ride off into the sunset together.

just thought you should know which book i was quoting

now we can meander off into the sunset at last...