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 2005 Forums

Transact-SQL (2005)

case statement error

Author

Topic

geraldjr30
Starting Member

5 Posts

Posted - 2014-06-11 : 11:49:50

hi. i have the following:

select

...
, case
when
LEFT(LTRIM(SubString(ltrim(rtrim(rms.nm12)),
CHARINDEX(' ', ltrim(rtrim(rms.nm12)))+1,
len(ltrim(rtrim(rms.nm12))) -
CHARINDEX(' ', ltrim(rtrim(rms.nm12))))),
CHARINDEX(' ', LTRIM(SubString(ltrim(rtrim(rms.nm12)),
CHARINDEX(' ', ltrim(rtrim(rms.nm12)))+1,
len(ltrim(rtrim(rms.nm12))) -
CHARINDEX(' ', ltrim(rtrim(rms.nm12)))))))
is null
and
right(LTRIM(SubString(ltrim(rtrim(rms.nm12)),
CHARINDEX(' ', ltrim(rtrim(rms.nm12)))+1,
len(rms.nm12) -
CHARINDEX(' ', ltrim(rtrim(rms.nm12))))), --FIRST TEMP
len(LTRIM(SubString(ltrim(rtrim(rms.nm12)),
CHARINDEX(' ', ltrim(rtrim(rms.nm12)))+1,
len(ltrim(rtrim(rms.nm12))) -
CHARINDEX(' ', ltrim(rtrim(rms.nm12)))))) -
CHARINDEX(' ', LTRIM(SubString(rms.nm12,
CHARINDEX(' ', ltrim(rtrim(rms.nm12)))+1,
len(ltrim(rtrim(rms.nm12))) -
CHARINDEX(' ', ltrim(rtrim(rms.nm12))))))
is not null
then
right(LTRIM(SubString(ltrim(rtrim(rms.nm12)),
CHARINDEX(' ', ltrim(rtrim(rms.nm12)))+1,
len(rms.nm12) -
CHARINDEX(' ', ltrim(rtrim(rms.nm12))))), --FIRST TEMP
len(LTRIM(SubString(ltrim(rtrim(rms.nm12)),
CHARINDEX(' ', ltrim(rtrim(rms.nm12)))+1,
len(ltrim(rtrim(rms.nm12))) -
CHARINDEX(' ', ltrim(rtrim(rms.nm12)))))) -
CHARINDEX(' ', LTRIM(SubString(rms.nm12,
CHARINDEX(' ', ltrim(rtrim(rms.nm12)))+1,
len(ltrim(rtrim(rms.nm12))) -
CHARINDEX(' ', ltrim(rtrim(rms.nm12))))))
else
LEFT(LTRIM(SubString(ltrim(rtrim(rms.nm12)),
CHARINDEX(' ', ltrim(rtrim(rms.nm12)))+1,
len(ltrim(rtrim(rms.nm12))) -
CHARINDEX(' ', ltrim(rtrim(rms.nm12))))),
CHARINDEX(' ', LTRIM(SubString(ltrim(rtrim(rms.nm12)),
CHARINDEX(' ', ltrim(rtrim(rms.nm12)))+1,
len(ltrim(rtrim(rms.nm12))) -
CHARINDEX(' ', ltrim(rtrim(rms.nm12)))))))
end as [first]

FROM
...

getting an error:
Msg 156, Level 15, State 1, Line 51
Incorrect syntax near the keyword 'is'.
Msg 319, Level 15, State 1, Line 108
Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.
Msg 319, Level 15, State 1, Line 109
Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.
Msg 319, Level 15, State 1, Line 111
Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.

can someone help me with the syntax?

thanks in advance

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-06-11 : 11:54:57

We can't help you as you didn't post the part of the code that has the issue. You likely just need to add a semi-colon before your query as the error suggests.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

sz1
Aged Yak Warrior

555 Posts

Posted - 2014-06-11 : 11:58:33

Not enough seperators:

Try this

select

...
, case
when
LEFT(LTRIM(SubString(ltrim(rtrim(rms.nm12)),
CHARINDEX(' ', ltrim(rtrim(rms.nm12)))+1,
len(ltrim(rtrim(rms.nm12))) -
CHARINDEX(' ', ltrim(rtrim(rms.nm12))))),
CHARINDEX(' ', LTRIM(SubString(ltrim(rtrim(rms.nm12)),
CHARINDEX(' ', ltrim(rtrim(rms.nm12)))+1,
len(ltrim(rtrim(rms.nm12))) -
CHARINDEX(' ', ltrim(rtrim(rms.nm12)))))))
is null
and
right(LTRIM(SubString(ltrim(rtrim(rms.nm12)),
CHARINDEX(' ', ltrim(rtrim(rms.nm12)))+1,
len(rms.nm12) -
CHARINDEX(' ', ltrim(rtrim(rms.nm12))))), --FIRST TEMP
len(LTRIM(SubString(ltrim(rtrim(rms.nm12)),
CHARINDEX(' ', ltrim(rtrim(rms.nm12)))+1,
len(ltrim(rtrim(rms.nm12))) -
CHARINDEX(' ', ltrim(rtrim(rms.nm12)))))) -
CHARINDEX(' ', LTRIM(SubString(rms.nm12,
CHARINDEX(' ', ltrim(rtrim(rms.nm12)))+1,
len(ltrim(rtrim(rms.nm12))) -
CHARINDEX(' ', ltrim(rtrim(rms.nm12)))))))
is not null
then
right(LTRIM(SubString(ltrim(rtrim(rms.nm12)),
CHARINDEX(' ', ltrim(rtrim(rms.nm12)))+1,
len(rms.nm12) -
CHARINDEX(' ', ltrim(rtrim(rms.nm12))))), --FIRST TEMP
len(LTRIM(SubString(ltrim(rtrim(rms.nm12)),
CHARINDEX(' ', ltrim(rtrim(rms.nm12)))+1,
len(ltrim(rtrim(rms.nm12))) -
CHARINDEX(' ', ltrim(rtrim(rms.nm12)))))) -
CHARINDEX(' ', LTRIM(SubString(rms.nm12,
CHARINDEX(' ', ltrim(rtrim(rms.nm12)))+1,
len(ltrim(rtrim(rms.nm12))) -
CHARINDEX(' ', ltrim(rtrim(rms.nm12)))))))
else
LEFT(LTRIM(SubString(ltrim(rtrim(rms.nm12)),
CHARINDEX(' ', ltrim(rtrim(rms.nm12)))+1,
len(ltrim(rtrim(rms.nm12))) -
CHARINDEX(' ', ltrim(rtrim(rms.nm12))))),
CHARINDEX(' ', LTRIM(SubString(ltrim(rtrim(rms.nm12)),
CHARINDEX(' ', ltrim(rtrim(rms.nm12)))+1,
len(ltrim(rtrim(rms.nm12))) -
CHARINDEX(' ', ltrim(rtrim(rms.nm12)))))))
end as [first]

We are the creators of our own reality!

geraldjr30
Starting Member

5 Posts

Posted - 2014-06-11 : 12:58:49

thanks... how can i also fix this one, as i am getting the same error message:

,case
when
LEFT(LTRIM(SubString(ltrim(rtrim(rms.nm12)),
CHARINDEX(' ', ltrim(rtrim(rms.nm12)))+1,
len(ltrim(rtrim(rms.nm12))) -
CHARINDEX(' ', ltrim(rtrim(rms.nm12))))),
CHARINDEX(' ', LTRIM(SubString(ltrim(rtrim(rms.nm12)),
CHARINDEX(' ', ltrim(rtrim(rms.nm12)))+1,
len(ltrim(rtrim(rms.nm12))) -
CHARINDEX(' ', ltrim(rtrim(rms.nm12)))))))
is null
and
right
(LTRIM(SubString(ltrim(rtrim(rms.nm12)),
CHARINDEX(' ', ltrim(rtrim(rms.nm12)))+1,
len(rms.nm12) -
CHARINDEX(' ', ltrim(rtrim(rms.nm12))))), --FIRST TEMP
len(LTRIM(SubString(ltrim(rtrim(rms.nm12)),
CHARINDEX(' ', ltrim(rtrim(rms.nm12)))+1,
len(ltrim(rtrim(rms.nm12))) -
CHARINDEX(' ', ltrim(rtrim(rms.nm12)))))) -
CHARINDEX(' ', LTRIM(SubString(rms.nm12,
CHARINDEX(' ', ltrim(rtrim(rms.nm12)))+1,
len(ltrim(rtrim(rms.nm12))) -
CHARINDEX(' ', ltrim(rtrim(rms.nm12))))))
is not null
then ''
else
right
(LTRIM(SubString(ltrim(rtrim(rms.nm12)),
CHARINDEX(' ', ltrim(rtrim(rms.nm12)))+1,
len(rms.nm12) -
CHARINDEX(' ', ltrim(rtrim(rms.nm12))))), --FIRST TEMP
len(LTRIM(SubString(ltrim(rtrim(rms.nm12)),
CHARINDEX(' ', ltrim(rtrim(rms.nm12)))+1,
len(ltrim(rtrim(rms.nm12))) -
CHARINDEX(' ', ltrim(rtrim(rms.nm12)))))) -
CHARINDEX(' ', LTRIM(SubString(rms.nm12,
CHARINDEX(' ', ltrim(rtrim(rms.nm12)))+1,
len(ltrim(rtrim(rms.nm12))) -
CHARINDEX(' ', ltrim(rtrim(rms.nm12))))))
end as middletest

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources