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/
Go to Top of Page

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!
Go to Top of Page

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

Go to Top of Page
   

- Advertisement -