SQLTeam.com | Weblogs | Forums

How to add case when to displayportal field based on first number on DoneCode?

I work on SQL server 2012 I face issue I can't add case when to display portal when make update

update t1 set t1.DisplayonPortal= NewReplacementType + t1.ReplacementFeature + (acc2.Name)  from    #TempReplacementImporter t1

inner join Parts.QualificationStanders h2 WITH(NOLOCK) ON t1.PartIDX=h2.PartId
LEFT join nop_acceptedvaluesoption acc2 WITH(NOLOCK) on acc2.AcceptedValuesOptionID=h2.QulificationValue

where t1.ReplacementFeature='Automotive'

what i need to do is
Done code related to table Parts.QualificationStanders
get first number on left from Done Code on left as 01111234

case when 0 then No
case when 1 then yes

then add to display portal above
here I will add to

t1.DisplayonPortal= NewReplacementType + t1.ReplacementFeature + (acc2.Name) + case when donecode......

No text because first number is 0

so How to add yes or no by case when to display portal

hi

what is the data type of Done Code ???

select case left(loot,1)
          when '0' then 'No'
		  when '1' then 'Yes'
		  else 'Maybe' end
  from (select '01111234' as loot
	    union
		select '11111234' as loot
		union
		select 'cheese' as loot
  ) a

DoneCode is nvarchar(20)
can you help me by adding result of case when on
update t1 set t1.DisplayonPortal= NewReplacementType + t1.ReplacementFeature + (acc2.Name)

what have you tried?

update t1 set t1.DisplayonPortal= NewReplacementType + (t1.ReplacementFeature) + case left(DoneCode,1) when '0' then 'No' when '1' then 'Yes'   from    #TempReplacementImporter t1

inner join Parts.QualificationStanders h2 WITH(NOLOCK) ON t1.PartIDX=h2.PartId

LEFT join nop_acceptedvaluesoption acc2 WITH(NOLOCK) on acc2.AcceptedValuesOptionID=h2.QulificationValue

where t1.ReplacementFeature='Automotive' and t1.status is null

hi

UPDATE
   t1 
SET 
    t1.DisplayonPortal  =    NewReplacementType 
						  +  t1.ReplacementFeature 
						  +  (acc2.Name) 
						  +  case when left(donecode,1) = '0' then 'No' when left(donecode,1) = '1' then 'Yes' end 
FROM
    #TempReplacementImporter t1
        inner join 
	Parts.QualificationStanders h2 WITH(NOLOCK) ON t1.PartIDX=h2.PartId
        LEFT join 
	nop_acceptedvaluesoption acc2 WITH(NOLOCK)  ON acc2.AcceptedValuesOptionID=h2.QulificationValue
WHERE 
    t1.ReplacementFeature='Automotive'

and did that work?