Solved

Question on SQL case statement

Posted on 2007-12-05
6
175 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
6 Comments
 
LVL 142

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 142

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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Select Query help 3 44
Difference in number of minutes between 2 timestamps 16 39
Script to randomly create characters in MS SQL 6 32
SQL Error in WHERE Clause 5 42
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
Hi friends,  in this video  I'll show you how new windows 10 user can learn the using of windows 10. Thank you.

863 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now