Case Expresion Then Containing String of Text and attribute result?

Question

Case Expresion Then Containing String of Text and attribute result?

elzool

SSC Eights!

Points: 931
More actions
May 4, 2017 at 4:33 pm

#330023

I've a case expression that I'd like to look like this, but this isn't working.
I'm trying to have it return text and the value of an attribute in my first THEN.
Instead it returns NULL
CASE WHEN Serv.Type = 'Assessment' AND Serv.ID IS NOT NULL Then 'Assessment' + ' - ' + Serv.ID WHEN Serv.Type = 'Assessment' and Serv.ID IS NULL Then 'Assessment' Else ' ' End AS 'test'
I would like to have it return: Assessment - 12345
Is this possible with a case expression?

Viewing 6 posts - 1 through 5 (of 5 total)

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

Alan Burstein SSC Guru Points: 61141 More actions · Answer 1

elzool - Thursday, May 4, 2017 4:33 PM
I've a case expression that I'd like to look like this, but this isn't working.
I'm trying to have it return text and the value of an attribute in my first THEN.
Instead it returns NULL
CASE WHEN Serv.Type = 'Assessment' AND Serv.ID IS NOT NULL Then 'Assessment' + ' - ' + Serv.ID WHEN Serv.Type = 'Assessment' and Serv.ID IS NULL Then 'Assessment' Else ' ' End AS 'test'
I would like to have it return: Assessment - 12345
Is this possible with a case expression?

Without table definitions and sample data its not easy for us to figure out. The logic of your query should dictate that you never get a NULL. Something isn't adding up. Do you have some DDL and sample data ?

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

elzool SSC Eights! Points: 931 More actions · Answer 2

I will try and provide this as soon as I am able.

My thought was that my syntax was simply incorrect.
I have no idea if this is even valid.
Then 'Assessment' + ' - ' + Serv.ID

Alan Burstein SSC Guru Points: 61141 More actions · Answer 3

If Serv.ID is numeric then you would have to convert it to a string first:
Then 'Assessment' + ' - ' + CAST(Serv.ID AS varchar(10))
Or, my favorite, CONCAT:
Then CONCAT( 'Assessment', ' - ' , Serv.ID)

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

sgmunson SSC Guru Points: 110640 More actions · Answer 4

CASE WHEN Serv.Type = 'Assessment' THEN 'Assessment' + ISNULL(' - ' + CAST(Serv.ID AS varchar(10)),'') ELSE '' END

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

elzool SSC Eights! Points: 931 More actions · Answer 5

elzool

SSC Eights!

Points: 931

May 8, 2017 at 11:57 am

#1941654

Excellent, thank you!