Home » SQL & PL/SQL » SQL & PL/SQL » Order by in a cursor (DS 10g, Forms 10g, Wimdows)
Order by in a cursor [message #431735] Thu, 19 November 2009 06:01 Go to next message
rkgoyal98
Messages: 39
Registered: October 2009
Member

Hello Seniors,

I have a form in which one program unit is being used to populate data in the block. I have used a cursor for getting the record set, however when i add order by clause in the cursor it gives an error at the time of compilation. The error is as under. Kindly help me to solve the error.


ERROR 103 AT LINE 14, COLUMN 13

ENCOUNTERED THE SYMBOL "ORDER" WHEN EXPECTING ONE OF THE FOLLOWING:

)


PROCEDURE PRTRLVH_CUR IS
CURSOR C_LEAVE IS (SELECT EM.EMPNO,LD.LEAVECODE, LD.LEAVEDESC, LH.LEAVEYEAR,   'I'  AS HALFYEAR,NVL(LH.IHALFLEAVEACCUMULATED,0) OB, 
NVL(LH.IHALFLEAVEELIGIBLE,0) AS LEAVEEARNED,(NVL(LH.IHALFLEAVEACCUMULATED,0) +  NVL(LH.IHALFLEAVEELIGIBLE,0))  TOTCR,  
NVL(LH.IHALFLEAVEAVAILED,0) DAYSNO,  (NVL(LH.IHALFLEAVEACCUMULATED,0) +  NVL(LH.IHALFLEAVEELIGIBLE,0) -  
NVL(LH.IHALFLEAVEAVAILED,0)) AS BALANACEDAYS  FROM PRMAEMP EM, PRTRLVH LH,  PRMALVM LD WHERE EM.EMPNO = LH.EMPNO AND  
LH.LEAVECODE = LD.LEAVECODE AND LD.LEAVECODE <> '00' AND EM.EMPNO =  LTRIM(RTRIM(:PRTRLVHMAS.EMPNO))  AND LH.IHALFLEAVEACCUMULATED 
IS NOT NULL
UNION
SELECT EM.EMPNO,LD.LEAVECODE, LD.LEAVEDESC, LH.LEAVEYEAR,   'II'  AS HALFYEAR,NVL(LH.IIHALFLEAVEACCUMULATED,0) OB, 
  NVL(LH.IIHALFLEAVEELIGIBLE,0) AS LEAVEEARNED,(NVL(LH.IIHALFLEAVEACCUMULATED,0) +  NVL(LH.IIHALFLEAVEELIGIBLE,0))  TOTCR,  
NVL(LH.IIHALFLEAVEAVAILED,0) DAYSNO,  (NVL(LH.IIHALFLEAVEACCUMULATED,0) +  NVL(LH.IIHALFLEAVEELIGIBLE,0) - 
NVL(LH.IIHALFLEAVEAVAILED,0)) AS BALANACEDAYS  FROM PRMAEMP EM, PRTRLVH LH,  PRMALVM LD WHERE EM.EMPNO = LH.EMPNO AND  
LH.LEAVECODE = LD.LEAVECODE AND LD.LEAVECODE <> '00' AND EM.EMPNO =  LTRIM(RTRIM(:PRTRLVHMAS.EMPNO)) AND LH.IIHALFLEAVEACCUMULATED 
IS NOT NULL ORDER BY LEAVEYEAR,HALFYEAR);
BEGIN
	GO_BLOCK('PRTRLVHDTL');
FOR R_LEAVE IN C_LEAVE
  LOOP
  	:PRTRLVHDTL.LEAVECODE := R_LEAVE.LEAVECODE;
  	:PRTRLVHDTL.LEAVEDESC := R_LEAVE.LEAVEDESC;
  	:PRTRLVHDTL.LEAVEYEAR := R_LEAVE.LEAVEYEAR;
  	:PRTRLVHDTL.HALFYEAR  := R_LEAVE.HALFYEAR;
  	:OB				:= R_LEAVE.OB;
  	:LEAVEEARNED := R_LEAVE.LEAVEEARNED;
  	:TOTCR := R_LEAVE.TOTCR;
  	:DAYSNO := R_LEAVE.DAYSNO;
  	:BALANACEDAYS := R_LEAVE.BALANACEDAYS;
  	NEXT_RECORD;
  END LOOP;
  FIRST_RECORD;
END;

Re: Order by in a cursor [message #431743 is a reply to message #431735] Thu, 19 November 2009 06:14 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Nice query formatting, easy to read and understant at first sight.
Good work!

Regards
Michel
Re: Order by in a cursor [message #431782 is a reply to message #431735] Thu, 19 November 2009 07:40 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Try removing the brackets from around the query entirely.
Re: Order by in a cursor [message #431790 is a reply to message #431782] Thu, 19 November 2009 08:24 Go to previous messageGo to next message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

OR
CURSOR c_leave
   IS
      (SQL stmnt )
ORDER BY LEAVEYEAR,HALFYEAR
Re: Order by in a cursor [message #432070 is a reply to message #431735] Fri, 20 November 2009 20:46 Go to previous messageGo to next message
rkgoyal98
Messages: 39
Registered: October 2009
Member

Thanks all, Solved by removing the bracket
Regards

[Updated on: Fri, 20 November 2009 20:47]

Report message to a moderator

Re: Order by in a cursor [message #432126 is a reply to message #431735] Sat, 21 November 2009 09:18 Go to previous messageGo to next message
wakula
Messages: 150
Registered: February 2008
Location: Poland
Senior Member
Hi,
Although the problem is "solved" - I believe that I know the reason for that problem. The solution might probably fail under some circumstances. Please review the following codes:
SELECT col_1 FROM tab1
UNION
SELECT col_2 FROM tab2 ORDER BY col_2

SELECT col_1 FROM tab1 ORDER BY col_1
UNION
SELECT col_2 FROM tab2 ORDER BY col_2

SELECT col_final FROM (
   SELECT col_1 AS col_final FROM tab1
   UNION
   SELECT col_2 AS col_final FROM tab2
) ORDER BY col_final

The initial question contained something what looks like first code sample.
Re: Order by in a cursor [message #432443 is a reply to message #432126] Tue, 24 November 2009 04:33 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
@wakula - I'm not quite sure what you're trying to say

The first two queries you've posted are going to error

I'm not sure under what conditions you think the solution won't work.
Re: Order by in a cursor [message #432641 is a reply to message #432443] Wed, 25 November 2009 07:12 Go to previous message
wakula
Messages: 150
Registered: February 2008
Location: Poland
Senior Member
JRowbottom wrote on Tue, 24 November 2009 11:33
The first two queries you've posted are going to error

Yes, you are right. It would work if we add brackets there but not in the example provided by me.
Previous Topic: how requirement is specified?
Next Topic: last 2
Goto Forum:
  


Current Time: Fri Dec 09 09:34:49 CST 2016

Total time taken to generate the page: 0.05460 seconds