?
Solved

Question on SQL case statement

Posted on 2007-12-05
6
Medium Priority
?
181 Views
Last Modified: 2010-03-20
I receive an error for this SP:
Incorrect syntax near 'ManagementType'.

Anyone can help me on this one?
select
      HotelID,
      FieldDescription,
      case
            when FieldDescription in 'ManagementType'
                  begin
                  case OldValue
                        when -1      then      'Undefined'
                        when 1      then      'CompanyManaged'
                        when 2      then      'CompanyOwned'
                        when 3      then      'Franchised'
                  end
                  end

            else
                  OldValue
            end as 'OldValue',
            NewValue,
            ChangeComment,
            LastUpdatedBy,
            LastUpdatedDate
from
      PropertyChangeHistory
where
      HotelID = @HotelID
0
Comment
Question by:chuang4630
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20413923
select
      HotelID,
      FieldDescription,
      case
            when FieldDescription = 'ManagementType'
                  begin
                  case OldValue
                        when -1      then      'Undefined'
                        when 1      then      'CompanyManaged'
                        when 2      then      'CompanyOwned'
                        when 3      then      'Franchised'
                  end
                  end

            else
                  OldValue
            end as [OldValue],
            NewValue,
            ChangeComment,
            LastUpdatedBy,
            LastUpdatedDate
from
      PropertyChangeHistory
where
      HotelID = @HotelID
0
 
LVL 1

Author Comment

by:chuang4630
ID: 20413955
I tried. It gives me the error:
Incorrect syntax near the keyword 'begin'.

0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 1000 total points
ID: 20413983
there are indeed other errors in the syntax:

select
      HotelID,
      FieldDescription,
      case
            when FieldDescription = 'ManagementType'
                  select case OldValue
                        when -1      then      'Undefined'
                        when 1      then      'CompanyManaged'
                        when 2      then      'CompanyOwned'
                        when 3      then      'Franchised'
                  end
            else OldValue
            end as [OldValue],
            NewValue,
            ChangeComment,
            LastUpdatedBy,
            LastUpdatedDate
from
      PropertyChangeHistory
where
      HotelID = @HotelID
0
Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

 
LVL 9

Expert Comment

by:sognoct
ID: 20414021
select
      HotelID,
      FieldDescription,
      (case
                      when FieldDescription = 'ManagementType' then
                       case OldValue
                        when -1      then      'Undefined'
                        when 1      then      'CompanyManaged'
                        when 2      then      'CompanyOwned'
                        when 3      then      'Franchised'
                                    end
                  end)as [OldValue],
            NewValue,
            ChangeComment,
            LastUpdatedBy,
            LastUpdatedDate
from
      PropertyChangeHistory
where
      HotelID = @HotelID
0
 
LVL 1

Author Comment

by:chuang4630
ID: 20414180
They are not working. But I have a work around by adding a new table ManagementTypes.:

case FieldDescription
      when 'ManagementType'
            THEN (SELECT Description
            FROM HotelManagementTypes WHERE ID = OldValue)
      when 'RegionID'
            THEN (SELECT Code + '(' + Description + ')'
            FROM Regions WHERE ID = OldValue)
      when 'CountryID'
            THEN (SELECT Code + '(' + Description + ')'
            FROM Countries WHERE ID = OldValue)
      when 'HotelBrand'
            THEN (SELECT Code + '(' + Description + ')'
            FROM Brands WHERE ID = OldValue)
      else
            OldValue
end as 'OldValue',


0
 
LVL 25

Assisted Solution

by:imitchie
imitchie earned 1000 total points
ID: 20416110
the original query should be

select
      HotelID,
      FieldDescription,
      case
            when FieldDescription = 'ManagementType' then
                  case OldValue
                        when -1      then      'Undefined'
                        when 1      then      'CompanyManaged'
                        when 2      then      'CompanyOwned'
                        when 3      then      'Franchised'
                  end

            else
                  OldValue
            end as 'OldValue',
            NewValue,
            ChangeComment,
            LastUpdatedBy,
            LastUpdatedDate
from
      PropertyChangeHistory
where
      HotelID = @HotelID
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question