CODEWITHSUNDEEP
sql-server-2005
happysmile
happysmile

Reputation: 7777

select statment with case condition sqlserver

i have an employee table

empname code1  code2
 kiran   aa    bng
 manju   yh    yh
 anu     yu01  yuo1

now i need to do an select statement like this

SELECT case when (employee.code1)=''
 then         
      mappingcode.Code            
   else             
then
       employee.code2          
   end as Code   
 FROM 
employee  where bic1=  'kiran'

but i get an error,

in my project i have an similar issue, just ot expalin the probem i have defined the issue

what i need to do here first check for code1 if null set its value from other table if not assign code2 value to code1

code1-->code2

i get an error as The multi-part identifier employee.code2 hope my Question is clear

can anyone help me teh syntax how to achive it.

thanks

Prince

Upvotes: 1

Views: 658

Answers (1)

Radu Caprescu
Radu Caprescu

Reputation: 993

If you just want the select, i think this will help you:

    SELECT empname, CASE 
WHEN employee.code1 IS NULL THEN mappingcode.code
ELSE
employee.code2 end as Code1
FROM employee INNER JOIN mappingcode ON employee.empname = mappingcode.empname

If you want the update, try this:

    Update employee set code1 = x.code from (SELECT empname, CASE 
WHEN employee.code1 IS NULL THEN mappingcode.code
ELSE
employee.code2 end as code
FROM employee INNER JOIN mappingcode ON employee.empname = mappingcode.empname) x 
 inner join employee on employee.empname = x.empname

Without knowing more about your schema, i think this is what you are looking for.

Thank you to Andriy for pointing out i had the same select in my post and a little mistake:)

Upvotes: 1

Related Questions