case with field names

Question

question

n3w2sql asked May 31, '12

case with field names

I am trying to find out the logic for using the case function with field names, I have 2 fields [LossesFrom],[RisksFrom] and I am trying to return the result in 1 column, Where one column is null the other has a date so I am trying to have just the one column with the a list of dates, this is my code CASE WHEN [LossesFrom] = NULL THEN [RISKSFROM] ELSE [LossesFrom] END AS [DATE]

sql-server-2008

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2012-05-31T11:57:29Z

Kev Riley answered May 31, '12

You can do this with a CASE, but also with a [COALESCE()][1] COALESCE(LossesFrom, RisksFrom) or even a [ISNULL()][2], as there are only 2 options ISNULL(LossesFrom, RisksFrom) [1]: http://msdn.microsoft.com/en-us/library/ms190349.aspx [2]: http://msdn.microsoft.com/en-us/library/ms184325.aspx

1

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ThomasRushton ♦♦ commented · May 31, 2012 at 11:58 AM

And you can have the +1 for providing the alternatives... :-)

0 ·

Answer 2 · 2012-05-31T11:57:00Z

ThomasRushton answered May 31, '12

You've pretty much cracked it. SELECT CASE WHEN [LossesFrom] IS NULL THEN [RisksFrom] ELSE [LossesFrom] END AS [Date] FROM ... Note: it's "IS NULL", not "= NULL".

1

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Usman Butt commented · May 31, 2012 at 12:03 PM

+1 for beating me and to keep you on the top ;)

1 ·

Answer 3 · 2012-05-31T11:57:03Z

Usman Butt answered May 31, '12

You cannot compare NULL like that. Use CASE WHEN [LossesFrom] IS NULL THEN [RISKSFROM] ELSE [LossesFrom] END AS [DATE]

5

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Usman Butt commented · May 31, 2012 at 11:59 AM

hahaha...yap :) But Kev Riley beat us both on votes ;)

3 ·

ThomasRushton ♦♦ commented · May 31, 2012 at 11:57 AM

Oh, bad luck. 3 seconds too slow... ;-)

0 ·

Usman Butt commented · May 31, 2012 at 12:00 PM

Spoke too soon ;)

0 ·

ThomasRushton ♦♦ commented · May 31, 2012 at 12:01 PM

You mean you didn't vote for @Kev Riley's answer?

0 ·

Usman Butt commented · May 31, 2012 at 12:04 PM

I did it now. Although it took me to the bottom ;)

0 ·

Answer 4 · 2012-05-31T13:25:46Z

n3w2sql answered May 31, '12

Thank you for this, I think your help will help many others users.:)

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

question