How do I convert my CASE WHEN THEN statement?

Question

Please help me to convert the below statement:

CASE 
  WHEN TITLE IS NOT NULL THEN 'A'
  WHEN LOCAL_TITLE IS NOT NULL THEN 'B'
  END
AS COMBINED_TITLE

to something like this:

CASE 
  WHEN TITLE IS NOT NULL THEN COMBINED_TITLE=TITLE
  WHEN LOCAL_TITLE IS NOT NULL THEN COMBINED_TITLE=LOCAL_TITLE
  END
AS COMBINED_TITLE

Thanx in advance Greg

Answer 1

ISNULL(TITLE, LOCAL_TITLE) AS COMBINED_TITLE

Answer 2

I'm not sure what you mean, but if you're trying to set a variable, you would do this:

SELECT
    @CombinedTitle = CASE
        WHEN Title IS NOT NULL THEN Title
        WHEN Local_Title IS NOT NULL THEN Local_Title
    END
...

If you still want to create column Combined_Title but with values from the two title columns, you would do:

SELECT
    CASE
        WHEN Title IS NOT NULL THEN Title
        WHEN Local_Title IS NOT NULL THEN Local_Title
    END AS Combined_Title
...

Also see documentation on COALESCE, it even mentions that it is equivalent to a CASE statement just like yours (with the addition of ELSE NULL.)

Answer 3

Are you trying to use it in an update statement? You could actually do away with the CASE statement in this instance

UPDATE
    <table>
SET
    COMBINED_TITLE = COALESCE(TITLE, LOCAL_TITLE, COMBINED_TITLE)

COALESCE will find the first NON-NULL value in the list