Solved

Question on SQL case statement

Posted on 2007-12-05
6
179 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 250 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
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 
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 250 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

Free Webinar: AWS Backup & DR

Join our upcoming webinar with experts from AWS, CloudBerry Lab, and the Town of Edgartown IT to discuss best practices for simplifying online backup management and cutting costs.

Question has a verified solution.

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

Suggested Solutions

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Finding and deleting duplicate (picture) files can be a time consuming task. My wife and I, our three kids and their families all share one dilemma: Managing our pictures. Between desktops, laptops, phones, tablets, and cameras; over the last decade…

730 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