Hi Guys,


I have the following stored procedure which I use to pull hotel
information from the database. The following procedure does just what I

need but I can't get the Case statement to work with the OrderBy
clause.


When I try I get the following error:


Error 104: Order by items must appear in the select list if the
statement contains a union operator.


This is confusing because the order by item does appear in the select
list.


Is there anyway to make this work withour resorting to dynamic sql?


Cheers, Pete

PS: Apologies for the cross-post, I didn't realise the sql programming
group existed.





CREATE PROCEDURE [dbo].[SelectHotelsByList]


        @Target_Language  nvarchar(50),
        @Default_Language  nvarchar(50),
        @Hotels_Key nvarchar(255),
        @OrderBy nvarchar(255)


AS


BEGIN


        SELECT


                        [Hotels_Name]


        FROM            [dbo].[vHotels]


        WHERE   [Hotels_LanguageCode] = @Default_language


        AND             [Hotels_Key]    NOT IN


                        (
                        SELECT  [Hotels_Key]


                        FROM            [dbo].[vHotels]


                        WHERE   [Hotels_LanguageCode] =
@Target_language


                        )


        AND             [Hotels_key]    =       @Hotels_Key


        UNION ALL


        SELECT


                        [Hotels_Name]


        FROM            [dbo].[vHotels]


        WHERE   [Hotels_LanguageCode] =  @Target_language


        AND             [Hotels_key]    =       @Hotels_Key


        ORDER BY


                CASE            WHEN @OrderBy = 'HotelsNameAsc' THEN
[Hotels_Name]
                                WHEN @OrderBy = 'HotelsNameDesc' THEN
[Hotels_Name]
                                ELSE [Hotels_Name]
                END    


END
GO

Change the entire query to a subquery and select the result with a alias name
and order the resultset

CREATE PROCEDURE [dbo].[SelectHotelsByList]
        @Target_Language  nvarchar(50),
        @Default_Language  nvarchar(50),
        @Hotels_Key nvarchar(255),
        @OrderBy nvarchar(255)
AS
BEGIN
    SELECT * FROM
    (SELECT    
                        [Hotels_Name]
        FROM            [dbo].[vHotels]
        WHERE   [Hotels_LanguageCode] = @Default_language
        AND             [Hotels_Key]    NOT IN
                        (
                       SELECT  [Hotels_Key]
                        FROM            [dbo].[vHotels]
                        WHERE   [Hotels_LanguageCode] =@Target_language
                        )
        AND             [Hotels_key]    =       @Hotels_Key
        UNION ALL
        SELECT
                        [Hotels_Name]
        FROM            [dbo].[vHotels]
        WHERE   [Hotels_LanguageCode] =  @Target_language
        AND             [Hotels_key]    =       @Hotels_Key
    ) T
        ORDER BY
                CASE            WHEN @OrderBy = 'HotelsNameAsc' THEN
[Hotels_Name]
                                WHEN @OrderBy = 'HotelsNameDesc' THEN
[Hotels_Name]
                                ELSE [Hotels_Name]
                END    

END
GO


Show quoteHide quote

Post your DDL and Select statement here so we can correct it for you.

Show quoteHide quote

On 29 Jun 2005 06:02:54 -0700, lad4b***@hotmail.com wrote:

#1, no, your order by item does NOT appear in the select list. Your order
by item is a case expression BASED on the column that appears in the select
list. If you truly wish to order by this case expression, you must copy it
IN ITS ENTIRETY to the select list.

#2, your case expression seems nonsensical. It reduces to just
[Hotels_Name] in all logical cases.

CASE            WHEN @OrderBy = 'HotelsNameAsc' THEN [Hotels_Name]
                     WHEN @OrderBy = 'HotelsNameDesc' THEN [Hotels_Name]
                     ELSE [Hotels_Name]
END    

#3, if you are trying to order either ascending or descending dynamically
depending on a variable expression, you're going about it the wrong way.
Use this doubled order by expression:

ORDER BY
CASE            WHEN @OrderBy = 'HotelsNameAsc' THEN [Hotels_Name]
                     WHEN @OrderBy = 'HotelsNameDesc' THEN ''
                     ELSE [Hotels_Name]
END,
CASE            WHEN @OrderBy = 'HotelsNameAsc' THEN ''
                     WHEN @OrderBy = 'HotelsNameDesc' THEN [Hotels_Name]
                     ELSE ''
END

Once again, since you have a UNION Statement, BOTH of these CASE statements
must be copied to the SELECT list of EACH part of the UNION.

Oops! I forgot the ASC and DESC keywords. 

On Wed, 29 Jun 2005 10:10:33 -0400, Ross Presser wrote:

CASE            WHEN @OrderBy = 'HotelsNameAsc' THEN [Hotels_Name]
                     WHEN @OrderBy = 'HotelsNameDesc' THEN ''
                     ELSE [Hotels_Name]
END ASC,
CASE            WHEN @OrderBy = 'HotelsNameAsc' THEN ''
                     WHEN @OrderBy = 'HotelsNameDesc' THEN [Hotels_Name]
                     ELSE ''
END DESC
Of course, if you'd rather, you can wrap the UNION in a subquery as Ravi
suggested, in which case you won't need to add the CASEs to your SELECTs.
but you'll still need to change your order by expression as I have shown.


Finally ... looking over the whole mess again... it appears you want to
retrieve all hotels with the default language, and all hotels with the
target language, without duplicates.  If that's all you want, there's no
need for a union!

CREATE PROCEDURE [dbo].[SelectHotelsByList]
        @Target_Language  nvarchar(50),
        @Default_Language  nvarchar(50),
        @Hotels_Key nvarchar(255),
        @OrderBy nvarchar(255)
AS

SELECT
    [Hotels_Name]
FROM
    [dbo].[vHotels]
WHERE
    (    [Hotels_LanguageCode] = @Default_Language
       OR [Hotels_LanguageCode] = @Target_language
    )
    AND     [Hotels_key] = @Hotels_Key
ORDER BY
    CASE WHEN @OrderBy = 'HotelsNameAsc' THEN [Hotels_Name]
            WHEN @OrderBy = 'HotelsNameDesc' THEN ''
              ELSE [Hotels_Name]
    END ASC,
    CASE WHEN @OrderBy = 'HotelsNameAsc' THEN ''
              WHEN @OrderBy = 'HotelsNameDesc' THEN [Hotels_Name]
              ELSE ''
    END DESC

Hi Ross,

I know the example I provided was poor and my apologies for that. And
thankyou for posting code, it's very kind of you. Unfortunately, it
doesn't do what I need it to do.  I will provide a more complete
description this time.

I have two tables. Hotel and HotelDetails. Hotel carries all the
language neutral information (Name, No. of Rooms etc) and HotelDetails
carries all the language specific information (Description, Facilities
etc). Hotel has a standard incrementing primary key. HotelDetails has a
two column primary key consisting of the Hotel key and the
LanguageCode. To make accessing the information easier (I'm not a smart
man) I've created a view that allows me treat the two tables as it if
they were one. As 1 Hotel can have many HotelDetails (1 for each
language) I end up with multiple rows for each Hotel.

I have to return the HotelInformation as a single result set with a
single row for each Hotel. If the HotelInformation is available in the
Target language (i.e Italian), return that. If the HotelInformation in
the default language (i.e English). The union approach is messy but I
couldn't for the life of me work out a way to handle another way.

I modified the code you posted but it doesn't handle the situation
described above. If a Hotel has HotelDetails in both the Target
language and the Default Language it returns both. You can see the
modified code below. (I'm not too concerned about the OrderBy clause
just at the minute)


    SELECT
            *
    FROM
            [dbo].[vHotelInformation]
    WHERE
                (
            [Hotels_LanguageCode] = @Target_language

            OR

            [Hotels_LanguageCode] = @Default_Language

               )
                    AND     [Hotels_key] = @Hotels_Key


I really hope you can help,

Cheers, Pete

On 29 Jun 2005 09:27:55 -0700, lad4b***@hotmail.com wrote:

Ah, now it is clear. I misinterpreted your original union query, probably
because the only column you were returning there was Hotel_Name, which I
figured was language-independent. You ultimately need all the
HotelInformation columns, I understand now.

If your union query was working, as your original post said, then it is a
reasonable way to do it. For medium sized tables, up to say thousands of
rows in each table, performance should not be an issue.

Here's another way, I believe. Making some assumptions about your Hotel and
HotelDetails tables. Dropping the Hotel_Prefix from some columns for
clarity:

CREATE TABLE Hotel (
    Hotel_Key int PRIMARY KEY,
    Name varchar(30) NOT NULL,
    Roomcount int NOT NULL
)

CREATE TABLE HotelDetails (
    Hotel_Key int NOT NULL
        REFERENCES Hotel (Hotel_Key),
    LanguageCode char(2) NOT NULL,
    Description varchar(80) NOT NULL,
    Facilities varchar(255) NOT NULL,
    PRIMARY KEY (Hotel_Key, LanguageCode)
)

CREATE PROCEDURE MatchingHotels
( @TargetLanguage char(2), @Default_Language char(2) )
AS
SELECT
    H.*, HD.*  -- trim this to fewer columns as desired
FROM
    Hotel H
    INNER JOIN HotelDetails HD
        ON H.Hotel_Key = HD.Hotel_Key
        AND (HD.LanguageCode = @Target_Language
            OR HD.LanguageCode = @Default_Language)
    LEFT JOIN HotelDetails HD2
        ON H.Hotel_Key = HD2.Hotel_Key
        AND HD2.LanguageCode = @Target_Language
WHERE
    HD2.LanguageCode IS NULL
    OR HD.LanguageCode = @Target_Language

To explain this: the INNER JOIN selects all rows from HotelDetails with
either matching language code. This would result in duplicates as you know.
The LEFT JOIN then joins in only the target language rows. The Where clause
then filters the results to where there's no HD2 row, which would be the
default language rows of HD, or else the HD row is the target language.

Two joins have the same effect therefore as your UNION ALL.

This method would have a bit better performance in the hundreds of
thousands of rows, I think.

Thanks Ross, am gonna give this a try later. It seems like a much more
elegant way to do it. I must admit though, it's gonna take me a week
for this to penetrate my thick skull! :)

This newsgroups top

Pete

Reliability of saving C# doubles in SQL Server... infinities and NaNs?
UDF Better?
Problem with PrimaryKey change in Audit table
Transation deadlock on cluster environment
Dynamic Trigger creation ?!