CASE in a Select Problem

[tena]

Hi,
I am getting a syntax error when I run the following, can anyone take a look and provide feedback?

TIA~

Tena

SELECT View_ACTIVE_IQ_USERS.fo_student_id, View_ACTIVE_IQ_USERS.user_pk1 AS ETUserID, 'Y' AS Active,
View_ACTIVE_IQ_USERS.first_name,View_ACTIVE_IQ_USE RS.address2,View_ACTIVE_IQ_USERS.address1,
Address = CASE View_ACTIVE_IQ_USERS.address2
WHEN View_ACTIVE_IQ_USERS.address2 is not null THEN (View_ACTIVE_IQ_USERS.address1 + ' ' + View_ACTIVE_IQ_USERS.address2)
WHEN View_ACTIVE_IQ_USERS.address2 null THEN View_ACTIVE_IQ_USERS.address1

END,
FROM View_IQ_Guardian INNER JOIN
View_ACTIVE_IQ_USERS ON View_IQ_Guardian.ETUserID = View_ACTIVE_IQ_USERS.user_pk1

[scripto]

for starts, remove the comma after END and let's see what happens...

[tena]

OK-tried that and still get:


Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'is'.


TIA~

Tena

[sukeshchand]

Try this code

SELECT View_ACTIVE_IQ_USERS.fo_student_id, View_ACTIVE_IQ_USERS.user_pk1 AS ETUserID, 'Y' AS Active, 
View_ACTIVE_IQ_USERS.first_name,View_ACTIVE_IQ_USE RS.address2,View_ACTIVE_IQ_USERS.address1,
Address = CASE WHEN View_ACTIVE_IQ_USERS.address2 is not null THEN (View_ACTIVE_IQ_USERS.address1 + ' ' + View_ACTIVE_IQ_USERS.address2)
WHEN View_ACTIVE_IQ_USERS.address2 null THEN View_ACTIVE_IQ_USERS.address1
END
FROM View_IQ_Guardian INNER JOIN
View_ACTIVE_IQ_USERS ON View_IQ_Guardian.ETUserID = View_ACTIVE_IQ_USERS.user_pk1