How to do an Order by Case with Union All

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
erp1022Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
erp1022Author Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.