SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 case statement error
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

geraldjr30
Starting Member

USA
5 Posts

Posted - 06/10/2014 :  12:54:38  Show Profile  Reply with Quote
hi. getting an error for:

select case when
LEFT(LTRIM(SubString('JONES TOBY ',
CHARINDEX(' ', 'JONES TOBY ')+1,
len('JONES TOBY ') -
CHARINDEX(' ', 'JONES TOBY '))),
CHARINDEX(' ', LTRIM(SubString('JONES TOBY ',
CHARINDEX(' ', 'JONES TOBY ')+1,
len('JONES TOBY ') -
CHARINDEX(' ', 'JONES TOBY ')))))
is null
and
(LTRIM(SubString('JONES TOBY ',
CHARINDEX(' ', 'JONES TOBY ')+1,
len('JONES TOBY ') -
CHARINDEX(' ', 'JONES TOBY '))), --FIRST TEMP
len(LTRIM(SubString('JONES TOBY ',
CHARINDEX(' ', 'JONES TOBY ')+1,
len('JONES TOBY ') -
CHARINDEX(' ', 'JONES TOBY ')))) -
CHARINDEX(' ', LTRIM(SubString('JONES TOBY ',
CHARINDEX(' ', 'JONES TOBY ')+1,
len('JONES TOBY ') -
CHARINDEX(' ', 'JONES TOBY '))))
is not null
then
(LTRIM(SubString('JONES TOBY ',
CHARINDEX(' ', 'JONES TOBY ')+1,
len('JONES TOBY ') -
CHARINDEX(' ', 'JONES TOBY '))), --FIRST TEMP
len(LTRIM(SubString('JONES TOBY ',
CHARINDEX(' ', 'JONES TOBY ')+1,
len('JONES TOBY ') -
CHARINDEX(' ', 'JONES TOBY ')))) -
CHARINDEX(' ', LTRIM(SubString('JONES TOBY ',
CHARINDEX(' ', 'JONES TOBY ')+1,
len('JONES TOBY ') -
CHARINDEX(' ', 'JONES TOBY '))))
else
LEFT(LTRIM(SubString('JONES TOBY ',
CHARINDEX(' ', 'JONES TOBY ')+1,
len('JONES TOBY ') -
CHARINDEX(' ', 'JONES TOBY '))),
CHARINDEX(' ', LTRIM(SubString('JONES TOBY ',
CHARINDEX(' ', 'JONES TOBY ')+1,
len('JONES TOBY ') -
CHARINDEX(' ', 'JONES TOBY ')))))
end as firstTEST


please help

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 06/10/2014 :  12:57:54  Show Profile  Reply with Quote
What is the error?
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 06/10/2014 :  13:08:56  Show Profile  Reply with Quote
Looks like you left off the LEFT (or some) function on two of the sections or, if you actually want to test for NULL, the code doesn't make any sense.
select case when 
LEFT(LTRIM(SubString('JONES TOBY ',
CHARINDEX(' ', 'JONES TOBY ')+1,
len('JONES TOBY ') - 
CHARINDEX(' ', 'JONES TOBY '))), 
CHARINDEX(' ', LTRIM(SubString('JONES TOBY ',
CHARINDEX(' ', 'JONES TOBY ')+1,
len('JONES TOBY ') - 
CHARINDEX(' ', 'JONES TOBY '))))) 
is null 
and 
????(LTRIM(SubString('JONES TOBY ',
CHARINDEX(' ', 'JONES TOBY ')+1,
len('JONES TOBY ') - 
CHARINDEX(' ', 'JONES TOBY '))), --FIRST TEMP
len(LTRIM(SubString('JONES TOBY ',
CHARINDEX(' ', 'JONES TOBY ')+1,
len('JONES TOBY ') - 
CHARINDEX(' ', 'JONES TOBY ')))) -
CHARINDEX(' ', LTRIM(SubString('JONES TOBY ',
CHARINDEX(' ', 'JONES TOBY ')+1,
len('JONES TOBY ') - 
CHARINDEX(' ', 'JONES TOBY ')))) 
is not null 
then 
????(LTRIM(SubString('JONES TOBY ',
CHARINDEX(' ', 'JONES TOBY ')+1,
len('JONES TOBY ') - 
CHARINDEX(' ', 'JONES TOBY '))), --FIRST TEMP
len(LTRIM(SubString('JONES TOBY ',
CHARINDEX(' ', 'JONES TOBY ')+1,
len('JONES TOBY ') - 
CHARINDEX(' ', 'JONES TOBY ')))) -
CHARINDEX(' ', LTRIM(SubString('JONES TOBY ',
CHARINDEX(' ', 'JONES TOBY ')+1,
len('JONES TOBY ') - 
CHARINDEX(' ', 'JONES TOBY ')))) 
else 
LEFT(LTRIM(SubString('JONES TOBY ',
CHARINDEX(' ', 'JONES TOBY ')+1,
len('JONES TOBY ') - 
CHARINDEX(' ', 'JONES TOBY '))), 
CHARINDEX(' ', LTRIM(SubString('JONES TOBY ',
CHARINDEX(' ', 'JONES TOBY ')+1,
len('JONES TOBY ') - 
CHARINDEX(' ', 'JONES TOBY ')))))
end as firstTEST


Edited by - Lamprey on 06/10/2014 13:11:13
Go to Top of Page

ScottPletcher
Constraint Violating Yak Guru

USA
362 Posts

Posted - 06/11/2014 :  14:05:42  Show Profile  Reply with Quote
Would you just state what you're trying to do with that code?

There's a much easier way to do whatever it is you're trying to do .
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000