Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Question on SQL case statement

Posted on 2007-12-05
6
Medium Priority
?
182 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
Technology Partners: 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!

 
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

Independent Software Vendors: 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

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…

597 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