• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 187
  • Last Modified:

Question on SQL case statement

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
chuang4630
Asked:
chuang4630
2 Solutions
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
chuang4630Author Commented:
I tried. It gives me the error:
Incorrect syntax near the keyword 'begin'.

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
sognoctCommented:
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
 
chuang4630Author Commented:
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
 
imitchieCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now