Solved

How to do an Order by Case with Union All

Posted on 2010-11-22
3
502 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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SSRS DateAdd function 4 66
Embed SSRS report in body of SMTP Email 5 229
SSRS Calculation help 6 44
Textboxes line spacing 6 36
This code started out as a fix for a customer that had incoming data that was hunderds of numbers and words long that was to fit in one column. The problem was that the customer did not want to split words or numbers when wrapping in the column. …
Introduction In the following article I’ll be discussing and demonstrating several different ways of how images can be put on a report. I’m using SQL Server Reporting Services 2008 R2 CTP, more precisely version 10.50.1352.12, but the methods ex…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

770 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