Assign value to another fieldname using CASE statement

Question

Post reply

Assign value to another fieldname using CASE statement

simflex-897410

Hall of Fame

Points: 3881
More actions
March 23, 2010 at 10:54 am

#218477

Greetings again friends,
Perhaps, this is not possible but I wanted to get your thoughts.
I have a fieldname called responseTotal (int data type).
Then I have another fieldname called isComments (bit data type).
If isComments = 0 (no comments entered), then rather than display a value of 0 for responseTotal, we would like to have a blank space as value instead.
If, however, isComments = 1 (comments was entered as value), then assign that value to responseTotal for that particular row.
Given that responseTotal is an integer data type, is there a way to manipulate it to accept a string value?
I know I have a way of not making myself clear. Please let me know if further clarification is needed.
Again, thanks to you all for all you do here to help us.
Below is the simple-minded code:
Select rq.ReportShortName, rq.reportQTitle,rd.responseTotal as 'Reporting Week', Sum(responseTotal) as YTD,
(CASE WHEN isComments <> 0 THEN rd.responseComments
WHEN isComments <> 1 THEN ''
ELSE 'No Comments'
END)
from ReportsData rd, ReportQuestions rq WHERE rq.ReportQID = rd.ReportQID AND rd.ReportGroupID=52
GROUP BY ReportShortName,reportQTitle, responseTotal, ReportQOrder,isComments,responseComments
ORDER BY reportQOrder
The numeric parameter is always converted to FLOAT(53) first
The output is always (fixed length) CHAR - the default is CHAR(10)
The default string output is left-padded with spaces if necessary
It uses the ROUND algorithm (round up) if rounding is required
The default CHAR(10) is not enough to hold all INTEGER values

PRINT STR(-2147483647)

DECLARE @tbl

TABLE (

ResponseTotal INT,

ResponseComments VARCHAR(20),

isComments BIT

);

INSERT @tbl

SELECT 1, 'test', 'true' UNION ALL

SELECT 0, 'No Comments', 'false' UNION ALL

SELECT -2147483647, 'Testing', 'false'

SELECT

*,

CASE isComments

WHEN 0 THEN STR(ResponseTotal)

ELSE ResponseComments

END AS RESULT

FROM @tbl;

Output:

ResponseTotal ResponseComments isComments RESULT

1 test 1 test

0 No Comments 0 0

-2147483647 Testing 0 **********

The data type of the RESULT column in that example is VARCHAR(20) - from the ResponseComments column - since VARCHAR has a higher data type precedence than the CHAR(10) returned by STR.

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

LutzM SSC Guru Points: 107049 More actions · Answer 1

You should use equal comparison rather than not equal.

CASE isComments WHEN 1 THEN rd.responseComments

WHEN 0 THEN ''

ELSE 'No Comments'

END

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 2

Lutz is right, it's better to expressly test a value for equality. Not equals testing may cause you problems with NULLs.

In any event, the case expression evaluates to a datatype based on the values returned, rather than the values tested. In SQL 2008, you can use the SQL_Variant datatype to mix character and numeric results.

-- CASE expression evaluates to varchar based on the values returned, not tested

declare @isComments int = 1

declare @responseComments varchar(50) = 'None of your business.'

SELECTCASE WHEN @isComments = 1 THEN @responseComments

WHEN @isComments = 0 THEN ''

ELSE 'No Comments'

END

GO

-- or, use SQL_Variant to retain the datatype

declare @isComments int = 1

declare @responseComments int = 6345789

SELECTCASE WHEN @isComments = 1 THEN cast(@responseComments as sql_variant)

WHEN @isComments = 0 THEN ''

ELSE 'No Comments'

END

GO

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

simflex-897410 Hall of Fame Points: 3881 More actions · Answer 3

Thanks again great people.

I can see that I have done it again!

I didn't explain myself well.

Let me attempt to represent the issue graphically.

For the purpose of what I am trying to accomplish, I have these 2 fieldnames:

responseTotal int

responseComments nvarchar(50)

isComments bit

sample data looks like this:

ResponseTotal ResponseComments isComments

1 test true

0 No Comments false

2 Testing false

Given this sample data,

If isComments is false (if isComments = 0), then display 0 or whatever the numeric value for responseTotal for that row is.

Otherwise, display the value of responseComments under responseTotal.

So, taking the sample data, we would like to see the followining:

ResponseTotal ResponseComments isComments

test test true

0 No Comments false

2 Testing false

From above example, isComments is true (isComments =1) , indicating there is a value called test under ResponseComments.

The rest of the value for ResponseTotal are numeric because isComments is either false or responseComments has a value of No Comments.

My worry is that since responseTotal is an integer datatype, it won't allow a value of string data type.

So, my question is whether you wizards can somehow manipulate it.

Sorry for not being clear.

I hope it is a bit clearer this time.

LutzM SSC Guru Points: 107049 More actions · Answer 4

Is it something like the following that you're looking for?

Please note that I used the STR() function to convert the numeric value to to a string data type:

DECLARE @tbl TABLE

(

ResponseTotal INT,

ResponseComments VARCHAR(20),

isComments BIT

)

INSERT INTO @tbl

SELECT 1, 'test', 'true' UNION ALL

SELECT 0, 'No Comments', 'false' UNION ALL

SELECT 2, 'Testing', 'false'

SELECT

*,

CASE isComments

WHEN 0 THEN STR(ResponseTotal)

ELSE ResponseComments

END AS RESULT

FROM @tbl

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 5

Did you not even look at my example casting an integer as SQL_VARIANT datatype?

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

simflex-897410 Hall of Fame Points: 3881 More actions · Answer 6

Lutz,

Your legend continues to grow everyday.

This worked like a dream!

Thank you very much.

You have bailed me out twice.

I hope I don't use up all of your good graces.

Again, thanks a lot.

LutzM SSC Guru Points: 107049 More actions · Answer 7

The Dixie Flatline (3/24/2010)
Did you not even look at my example casting an integer as SQL_VARIANT datatype?

The main reason why I try to avoid SQL_VARIANT is the need to cast it again once you need to use that column in a join condition.

As per BOL:

In assignments from sql_variant objects to an object that has any other data type, the sql_variant value must be explicitly cast to the data type of the destination. No implicit conversions are supported when a sql_variant value is assigned to an object that has another data type.

Therefore, it became a habit to assign a "known" data type "at the source".

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Paul White SSC Guru Points: 150467 More actions · Answer 8

The Dixie Flatline (3/24/2010)
Did you not even look at my example casting an integer as SQL_VARIANT datatype?

I did, and I liked it.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 9

The main reason why I try to avoid SQL_VARIANT is the need to cast it again once you need to use that column in a join condition.

Fair enough, Lutz.

Both STR() and SQL_VARIANT solve the problem of sorting data by the result column.

Thanks, Paul.

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

Paul White SSC Guru Points: 150467 More actions · Answer 10

Paul White

SSC Guru

Points: 150467

March 24, 2010 at 10:42 pm

#1139396

STR has some interesting properties:

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 11

Paul, please tell me you don't have all that committed to memory. 😉

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

Paul White SSC Guru Points: 150467 More actions · Answer 12

The Dixie Flatline (3/25/2010)
Paul, please tell me you don't have all that committed to memory. 😉

Ha...no, not at all. I remember very few facts, since they do not age well 🙂

Books Online :w00t:

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 13

Looking at the number of posts you've been involved in lately, and extrapolating from that a certain percentage of BOL lookups, factor in typing time.....

Tell me you have a life outside SQL.

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

Paul White SSC Guru Points: 150467 More actions · Answer 14

The Dixie Flatline (3/25/2010)
Tell me you have a life outside SQL.

(0 row(s) affected)

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi