Home » SQL & PL/SQL » SQL & PL/SQL » Order by Case and Union All (Oracle)
Order by Case and Union All [message #332185] Mon, 07 July 2008 15:24 Go to next message
Jonathan.Harvey
Messages: 2
Registered: July 2008
Location: Montréal
Junior Member
Hi everybody , I'm new to this forum Smile
I got a problem with my query who look like this one .
http://www.orafaq.com/forum/t/101965/2/

But I've try to copy de solution and it didn't work for me .

SELECT DECODE(RELEVE_JR_NUIT,'J','Jour','N','Nuît') Releve,
       gt.tpfnc_desc fonction,
       'La' Effectif,
       te.LA.Mout Mout,
       te.NOM||', '||te.PRENOM Mat_Nom,
       to_char(HR_DEB,'HH24:MI') HR_DEB,
       HR_FIN,
       VEHICULE    
           
from GMU_EFFECTIFS ge,TCM_EMPLOYE_PLUS te,GMU_OPERATIONS go,GMU_CENTRES_DECISIONS gc
    ,GMU_TYPES_FONCTIONS gt
where te.NO_EMPLOYE=EMPLP_NO_EMPLOYE
  and go.an_ope = ge.LILO
  and go.no_ope = ge.ABAB
  and go.an_ope=gc.GAGA
  and go.no_ope=gc.ZOZA
  and CNTDC_SEQ =gc.seq
  and gt.code = ge.TPFNC_CODE
 -- &P_WHERES

UNION ALL
select 
       DECODE(RELEVE_JR_NUIT,'J','Jour','N','Nuît') Releve,
       gt.tpfnc_desc fonction,
       'EXTER' Effectif,
       to_char(Mat) Mout,
       GE.NOM||', '||GE.PRENOM Mat_Nom,
       to_char(HR_DEB,'HH24:MI') HR_DEB,
       HR_FIN ,
       VEHICULE
from GMU_EFFECTIF_EXTERS ge,GMU_OPERATIONS go,GMU_TYPES_FONCTIONS gt,GMU_CENTRES_DECISIONS gc
where go.an_ope = ge.LILO
  and go.no_ope = ge.ABAB
  and go.no_ope = ge.ABAB
  and go.an_ope=gc.GAGA
  and go.no_ope=gc.ZOZA
  and CNTDC_SEQ =gc.seq
  and gt.code = ge.TPFNC_CODE
 -- &P_WHERE

order by Case when tpfnc_desc='ORA' then '1'
              when tpfnc_desc= 'RAR' then '2'
              Else '3'
         End,tpfnc_desc fonction


I want to order by what is inside tpfnc_desc. Can anyone help me with that ? :S thanks !
Re: Order by Case and Union All [message #332186 is a reply to message #332185] Mon, 07 July 2008 15:27 Go to previous messageGo to next message
BlackSwan
Messages: 25045
Registered: January 2009
Location: SoCal
Senior Member
> it didn't work for me .
My car didn't work for me.
Tell me how I can make my car go.

http://www.orafaq.com/forum/t/88153/0/
Please read & follow posting guidelines as stated in URL above

We don't have your table.
We don't have your data.
Therefore we can't really assist you now so You're On Your Own (YOYO)!
Re: Order by Case and Union All [message #332187 is a reply to message #332186] Mon, 07 July 2008 15:36 Go to previous messageGo to next message
Jonathan.Harvey
Messages: 2
Registered: July 2008
Location: Montréal
Junior Member
Or... what else you want to know ?
the error code ? ;

ORA-01785: ORDER BY item must be the number of a SELECT-list expression
Re: Order by Case and Union All [message #332200 is a reply to message #332187] Mon, 07 July 2008 17:19 Go to previous message
Barbara Boehmer
Messages: 8635
Registered: November 2002
Location: California, USA
Senior Member
As discussed in the other thread, you need to put the union all's in a sub-query, in order to reference the columns by name, instead of position, in an order by clause.

SCOTT@orcl_11g> SELECT 'ORA' col1 FROM DUAL
  2  UNION ALL
  3  SELECT 'RAR' col1 FROM DUAL
  4  UNION ALL
  5  SELECT 'ABC' col1 FROM DUAL
  6  ORDER BY CASE WHEN col1 = 'ORA' THEN '1'
  7  		   WHEN col1 = 'RAR' THEN '2'
  8  		   ELSE '3'
  9  	      END
 10  /
ORDER BY CASE WHEN col1 = 'ORA' THEN '1'
         *
ERROR at line 6:
ORA-01785: ORDER BY item must be the number of a SELECT-list expression


SCOTT@orcl_11g> SELECT *
  2  FROM   (SELECT 'ORA' col1 FROM DUAL
  3  	     UNION ALL
  4  	     SELECT 'RAR' col1 FROM DUAL
  5  	     UNION ALL
  6  	     SELECT 'ABC' col1 FROM DUAL)
  7  ORDER BY CASE WHEN col1 = 'ORA' THEN '1'
  8  		   WHEN col1 = 'RAR' THEN '2'
  9  		   ELSE '3'
 10  	      END
 11  
SCOTT@orcl_11g> /

COL
---
ORA
RAR
ABC

SCOTT@orcl_11g>

Previous Topic: error in where clause
Next Topic: UNDOTB2 dba_free_space Increased (merged)
Goto Forum:
  


Current Time: Wed Dec 07 08:47:17 CST 2016

Total time taken to generate the page: 0.09221 seconds