Help with CASE in SELECT statment

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)

Help with CASE in SELECT statment

Author

Topic

Eagle_f90
Constraint Violating Yak Guru

424 Posts

Posted - 2012-06-13 : 22:31:58

Can anyone look over the below code and help me figure out why it I am told there is incorect syntac in my frist THEN statment of my CASE stement?

SELECT si.SectionID AS ID,
       si.ShortName AS Title,
       si.Synopsis AS Descript,
       CAST(
            CASE
                WHEN nc.CategoryName = 'Cinema' 
                    THEN CAST('Section/EditCinema' AS char(18)) AS PageName
                WHEN nc.CategoryName = 'Book'
                    THEN CAST('Section/EditBook' AS char(16)) AS PageName
                WHEN nc.CategoryName = 'Merchandise'
                    THEN CAST('Section/EditMerch' AS char(17)) AS PageName
                ELSE CAST('Section/EditGame' AS char(16)) AS PageName
            )
FROM dbo.SectionInfo AS si WITH (NOLOCK)
JOIN dbo.NavigationCategories AS nc WITH (NOLOCK)
ON si.SectionID = nc.SectionID

Error:

quote:
Msg 156, Level 15, State 1, Line 7
Incorrect syntax near the keyword 'AS'.

--
If I get used to envying others...
Those things about my self I pride will slowly fade away.
-Stellvia

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-06-13 : 22:34:36

[code]
SELECT si.SectionID AS ID,
si.ShortName AS Title,
si.Synopsis AS Descript,
~~CAST~~(
CASE
WHEN nc.CategoryName = 'Cinema'
THEN CAST('Section/EditCinema' AS char(18)) ~~AS PageName~~
WHEN nc.CategoryName = 'Book'
THEN CAST('Section/EditBook' AS char(16)) ~~AS PageName~~
WHEN nc.CategoryName = 'Merchandise'
THEN CAST('Section/EditMerch' AS char(17)) ~~AS PageName~~
ELSE CAST('Section/EditGame' AS char(16)) ~~AS PageName~~
END
) AS PageName
FROM dbo.SectionInfo AS si WITH (NOLOCK)
JOIN dbo.NavigationCategories AS nc WITH (NOLOCK)
ON si.SectionID = nc.SectionID
[/code]

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-06-13 : 22:38:56

actually you don't need to cast the string to char(xx) at all


SELECT si.SectionID AS ID,
       si.ShortName AS Title,
       si.Synopsis AS Descript,
            CASE
                WHEN nc.CategoryName = 'Cinema'      THEN 'Section/EditCinema'
                WHEN nc.CategoryName = 'Book'        THEN 'Section/EditBook'
                WHEN nc.CategoryName = 'Merchandise' THEN 'Section/EditMerch'
                ELSE                                      'Section/EditGame'
                END AS PageName
FROM dbo.SectionInfo AS si WITH (NOLOCK)
JOIN dbo.NavigationCategories AS nc WITH (NOLOCK)
ON si.SectionID = nc.SectionID

KH
[spoiler]Time is always against us[/spoiler]

Eagle_f90
Constraint Violating Yak Guru

424 Posts

Posted - 2012-06-13 : 22:50:07

Thanks

--
If I get used to envying others...
Those things about my self I pride will slowly fade away.
-Stellvia

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources