case statement error - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

case statement error

Author

Topic

geraldjr30
Starting Member

5 Posts

Posted - 2014-06-10 : 12:54:38

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
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-06-10 : 12:57:54

What is the error?

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-06-10 : 13:08:56

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

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2014-06-11 : 14:05:42

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 .

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources