Order by and Union problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Order by and Union problem

Author

Topic

mibheenick
Starting Member

12 Posts

Posted - 2005-09-29 : 13:09:40

Hi everyone(especially to Madhivanan),

U sent me a solution to an “Order By” problem some time back, but I still have some problems with the issue. Can u please help me out with this one.

The solution u gave me looks approximately like this:

Select top 1 RequiredField from tableName Where Code = 123 and Lang in (@Lang, ‘fr’, ‘en’) Order by case when Lang = @Lang then 1 when Lang = ‘fr’ then 2 when Lang = ‘en’ then 3 end

Now when I am putting the above in another Select statement containing a UNION it’s not working, as follows:

Select T.ID, T.Name, (Select top 1 RequiredField from tableName Where Code = T.Code and Lang in (@Lang, ‘fr’, ‘en’) Order by case when Lang = @Lang then 1 when Lang = ‘fr’ then 2 when Lang = ‘en’ then 3 end) from AnotherTable T where T.Code = 111
UNION ALL
Select T.ID, T.Name, (Select top 1 RequiredField from tableName Where Code = T.Code and Lang in (@Lang, ‘fr’, ‘en’) Order by case when Lang = @Lang then 1 when Lang = ‘fr’ then 2 when Lang = ‘en’ then 3 end) from AnotherTable T where T.Code = 222

If I execute the above it gives me an error! Please help

thanx

Live together like brothers and do business like strangers.....

X002548
Not Just a Number

15586 Posts

Posted - 2005-09-29 : 13:34:11

Please read the link below and supply us with more info, thanks

I'll have a look anyway

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

mmarovic
Aged Yak Warrior

518 Posts

Posted - 2005-09-29 : 14:00:53

Can you post an error message?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-09-29 : 15:09:24

Select T.ID, T.Name, (Select top 1 RequiredField from tableName Where Code = T.Code and Lang in (@Lang, ‘fr’, ‘en’) Order by case when Lang = @Lang then 1 when Lang = ‘fr’ then 2 when Lang = ‘en’ then 3 end) ~~as NewColumnName~~ from AnotherTable T where T.Code = 111
UNION ALL
Select T.ID, T.Name, (Select top 1 RequiredField from tableName Where Code = T.Code and Lang in (@Lang, ‘fr’, ‘en’) Order by case when Lang = @Lang then 1 when Lang = ‘fr’ then 2 when Lang = ‘en’ then 3 end) ~~as NewColumnName~~ from AnotherTable T where T.Code = 222

don't think that's it.
but you do have some weird quotation marks aound your strings:
‘en’ -> 'en'

Go with the flow & have fun! Else fight the flow

mibheenick
Starting Member

12 Posts

Posted - 2005-09-30 : 04:13:03

sorry everyone, but the error message is in french, as follows:

Serveur : Msg 104, Niveau 15, État 1, Procédure ps_Impression_Facture, Ligne 379
Si l'instruction contient un opérateur UNION, les éléments ORDER BY doivent figurer dans la liste de sélection.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-30 : 04:51:54

You cannot use Order by Clause in the first query and then union it by other query
The only way I know is to move the result set to temporary table and do query based on that table

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources