variable not truncating

[junkmail]

The following code works fine as far as the logic goes but can someone tell me why the salesrep variable always displays the max length even though I am telling it to display only 22 characters?

case when substring(b.jobnumber,1,1) ='N' then (select left(salesrep,22) from db1..import_order_table where b.jobnumber=import_order_table.orderid) else (select left(description,22) from db2..salesrep where o.salesrepcode=salesrep.code) end as salesrep

 

[gmmastros]

I don't know, but I am pretty sure that you can fix it by moving the LEFT function outside the case statement instead of inside, like this:

[!]Left([/!]case when substring(b.jobnumber,1,1) ='N' 
     then (select salesrep 
           from   db1..import_order_table 
           where  b.jobnumber=import_order_table.orderid) 
     else (select description 
           from db2..salesrep 
           where o.salesrepcode=salesrep.code) 
     end[!], 22)[/!] as salesrep

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[junkmail]

For some reason that did not seem to work for me. The weird thing is if I don't put the case statement in then it works as expected but then again I can't check to see what field I need to pull without the case statement.

 

[gk53]

select salesrep	=	left(case when substring(b.jobnumber,1,1) ='N' then 
							(select salesrep 
								from db1..import_order_table 
									where b.jobnumber	=	import_order_table.orderid) 
							else 
								(select [description] from db2..salesrep 
									where o.salesrepcode	=	salesrep.code) 
							end , 22) 
	from b