Incorrect syntax near the keyword 'case'.

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

General SQL Server Forums

New to SQL Server Programming

Incorrect syntax near the keyword 'case'.

Author

Topic

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2012-09-12 : 06:00:10

Hey guys

i am receiving this error msg
Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'case'.

and this is my query , any ideas ?
SELECT
u.DBA_Name,
u.parentid,
u.Rolling_12,
u.post_code,
u.NewPostcode,
g.[RMSC],
dbo.RollingSalesBandRM (u.Rolling_12)as SalesBanding
into #banding
FROM [FDMS].[dbo].[Geo_PCA_Sellers] g
INNER JOIN #Update u
ON u.NewPostcode = g.PCA

--select * from #banding
--drop table #banding
----------------------------------------------------

Select * from #banding

case when Salesbanding in ('50M to 100+')then 'R05'
When Salesbanding in ('2M to 5M')then 'R15'
else RMSC end as RMSC
into #RmUpdate

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-09-12 : 06:46:13

select
col1,
col2,
case when ... then ... end as col3

into ...

from ...

Too old to Rock'n'Roll too young to die.

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2012-09-12 : 06:50:46

hi webfred

Are you stating that it should be
select
#banding.DBA_Name,
#banding.ParentID,
#banding.Post_Code,
#banding.Rolling_12,
#banding.RMSC,
#banding.SalesBanding,
case
when Salesbanding in ('50M to 100+')then 'R05'
When Salesbanding in ('2M to 5M')then 'R15'
end as RMSC
Into #RmUpdate
From #banding

if thats the case i would get the following error msg

Msg 2705, Level 16, State 3, Line 1
Column names in each table must be unique. Column name 'RMSC' in table '#RmUpdate' is specified more than once.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-09-12 : 06:59:11

instead of having 2 the same column names, just name your case-column RMSC2 or whatever.

Too old to Rock'n'Roll too young to die.

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2012-09-12 : 07:09:16

Webfred i am being thick !

its been a long day ! lol

Can you answer me this question

SELECT
u.DBA_Name,
u.parentid,
u.Rolling_12,
u.post_code,
u.NewPostcode,
g.[RMSC],
dbo.RollingSalesBandRM (u.Rolling_12)as SalesBanding
into #banding
FROM [FDMS].[dbo].[Geo_PCA_Sellers] g
INNER JOIN #Update u
ON u.NewPostcode = g.PCA

--select * from #banding
--drop table #banding
----------------------------------------------------

select
#banding.DBA_Name,
#banding.ParentID,
#banding.Post_Code,
#banding.Rolling_12,
#banding.RMSC,
#banding.SalesBanding,
case
when Salesbanding in ('50M to 100+')then 'R05'
When Salesbanding in ('2M to 5M')then 'R16'
else #banding.RMSC end as NewRMSC
Into #test
From #banding

--select * from #test
--drop table #test
----------------------------------------

update test
set NewRMSC = case when rn % 2 = 0 then 'R16' else 'R15' end
from
(select NewRmsc, rn = row_number() over (order by rolling_12 desc)
from #test
where NewRMSC = 'R16'
) test

now i have updated the table, how can i choose what Specific columns i want from that update table ?

for eg if i put select * from #test

it produce the following

DBA_Name ,ParentID, Post_Code, Rolling_12, RMSC, SalesBanding ,NewRMSC

All i need to select is
DBA_Name ,ParentID, Post_Code, Rolling_12, SalesBanding ,NewRMSC

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-09-12 : 07:16:26

instead of
SELECT *

just do
SELECT DBA_Name ,ParentID, Post_Code, Rolling_12, SalesBanding ,NewRMSC from #test

Too old to Rock'n'Roll too young to die.

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2012-09-12 : 07:20:49

:) cheers buddy !

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources