Solved

How to do an Order by Case with Union All

Posted on 2010-11-22
3
493 Views
Last Modified: 2012-05-10
Hello,

I am trying to do an Order by Case on a Select with a UNION ALL. I am getting the error: sql order by items must appear in the select list if the statement contains a union.

I have tried using the order by in both select statements, and have also tried parenthaszing both select statements and placing the order by at the end outside of the parenthesis, but to no avail.

Could someone please take a look at the attachment and let me know what I'm doing wrong? Thanks!
Order-by-case-with-union-all.doc
0
Comment
Question by:erp1022
  • 2
3 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 34189079
this will do:
SELECT *

 FROM (SELECT a.account as Natural

,rtrim(a.ACTDESCR) as Descr

,(g.CRDTAMNT - g.DEBITAMT) AS Amount

,CASE

WHEN a.account like '400%' then 'G'

WHEN a.account like '42%' or a.accountlike '43%' then 'H'

WHEN a.account = '47100' then '3'

WHEN a.ACCOUNT IN ('45100', '45050') then 'P' 

END as Service



FROM GL00100 a 

INNER JOIN GL20000 g 

ON a.ACTINDX = g.ACTINDX 

INNER JOIN RM00101 c 

ON g.ORMSTRID = c.CUSTNMBR



WHERE a.ACCOUNT LIKE '4%' AND

etc.





UNION ALL



SELECT a.account as Natural

,rtrim(a.ACTDESCR) as Descr

,(g.CRDTAMNT - g.DEBITAMT) AS Amount

,CASE

WHEN a.account like '400%' then 'G'

WHEN a.account like '42%' or a.account like '43%' then 'H'

WHEN a.account = '47100' then '3'

WHEN a.ACCOUNT IN ('45100', '45050') then ‘P’ 

END as Service



FROM GL00100 a 

INNER JOIN GL30000 g 

ON a.ACTINDX = g.ACTINDX 

INNER JOIN RM00101 c 

ON g.ORMSTRID = c.CUSTNMBR



WHERE  a.ACCOUNTLIKE '4%' AND 

) sq



Order by CASE

	WHEN Natural like '400%' then 1

	WHEN Natural like '42%' or Natural like '43%' then 2

	WHEN Natural = '47100' then 3

	WHEN Natural IN ('45100', '45050') then 4

END

Open in new window

0
 

Author Comment

by:erp1022
ID: 34190390
Great, that works! Could you explain what you did and why? I have a couple of other queries I need to modify and would like to understand what I was doing wrong.
0
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 34194610
no problem.

you had (abstract):
select a.field ... from table a UNION select ... ORDER BY a.field 

Open in new window

the issue is that, because you have a union, the ORDER BY cannot know the 2 SELECT field names, because the second query could have completely other tables, aliases, fields ...

so, you have to ORDER BY the resulting field names, so for a.Account as Natural, Natural being the resulting field name, you have to order by that "name" .

CHeers
0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

Written by Valentino Vranken. Introduction: The first step of creating a SQL Server Reporting Services (SSRS) report involves setting up a connection to the data source and programming a dataset to retrieve data from that data source.  The data…
Introduction As you'll probably know, a data region in a SQL Server Reporting Services report can be linked to only one dataset.  This makes it troublesome when you need to display data from more than one dataset in the same data region.  SQL Serve…
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …

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

27 Experts available now in Live!

Get 1:1 Help Now