Home » Developer & Programmer » Forms » can't used the order by clause in Cursor? (form 6i, oracle 9i)
can't used the order by clause in Cursor? [message #387419] Thu, 19 February 2009 03:36 Go to next message
mm_kanish05
Messages: 487
Registered: January 2007
Location: Chennai
Senior Member

Hi
I am using the below query in cursor

cursor empgrs is 
(SELECT   a.emplcode, 
         a.emplname, 
         Nvl(plcash,0) pl, 
         Nvl(alta,0)   lta, 
         Nvl(medic,0)  mdi, 
         Nvl(bns,0)    bonus, 
         stiching, 
         washing 
FROM     (SELECT a.emplcode, 
                 emplname, 
                 washing, 
                 stiching, 
                 Nvl(bonus,0) bns 
          FROM   (SELECT   emplcode, 
                           emplname, 
                           Sum(wash)  washing, 
                           Sum(stich) stiching 
                  FROM     (SELECT   a.emplcode, 
                                     emplname, 
                                     Sum(Decode(paycode,'E08',amount, 
                                                        0)) wash, 
                                     Sum(Decode(paycode,'E31',amount, 
                                                        0)) stich 
                            FROM     payfile a, 
                                     emplmast b 
                            WHERE    a.unitid = 1 
                                     AND a.emplcode = b.emplcode 
                                     AND paycode IN ('E31','E08') 
                                     AND Substr(catgcode,1,1) IN ('S','O','T','E', 
                                                                  'W') 
                                     AND ((monthno BETWEEN 04 AND 12 
                                           AND yearno = 2008) 
                                           OR (monthno BETWEEN 01 AND 03 
                                               AND yearno = 2009)) 
                            GROUP BY a.emplcode, 
                                     emplname 
                            UNION 
                            SELECT   a.emplcode, 
                                     emplname, 
                                     0 wash, 
                                     0 stich 
                            FROM     payfile a, 
                                     emplmast b 
                            WHERE    a.unitid = 1 
                                     AND a.emplcode = b.emplcode 
                                     AND paycode IN ('E97') 
                                     AND Substr(catgcode,1,1) IN ('S','O','T','E', 
                                                                  'W') 
                                     AND ((monthno BETWEEN 04 AND 12 
                                           AND yearno = 2008) 
                                           OR (monthno BETWEEN 01 AND 03 
                                               AND yearno = 2009)) 
                            GROUP BY a.emplcode, 
                                     emplname) 
                  GROUP BY emplcode, 
                           emplname) a, 
                 (SELECT emplcode, 
                         gross_amount bonus 
                  FROM   bonus 
                  WHERE  bonus_period = '2007-2008' 
                         AND unitid = 1) b 
          WHERE  a.emplcode = b.emplcode (+)) a, 
         (SELECT   slcode, 
                   emplname, 
                   Sum(plncash) plcash, 
                   Sum(lta)     alta, 
                   Sum(mdi)     medic 
          FROM     (SELECT   slcode, 
                             emplname, 
                             Sum(debitamount - creditamount) plncash, 
                             0                               lta, 
                             0                               mdi 
                    FROM     ledger a, 
                             emplmast b 
                    WHERE    a.unitid = 1 
                             AND periodid = 13 
                             AND glcode IN ('94117','94128','94139') 
                             AND slcode IS NOT NULL 
                             AND a.unitid = b.unitid 
                             AND a.slcode = b.emplcode 
                             AND Upper(particulars) LIKE '%LEAVE ENCASH%' 
                    GROUP BY slcode, 
                             emplname 
                    UNION 
                    SELECT   slcode, 
                             emplname, 
                             0                               plncash, 
                             Sum(debitamount - creditamount) lta, 
                             0                               mdi 
                    FROM     ledger a, 
                             emplmast b 
                    WHERE    a.unitid = 1 
                             AND periodid = 13 
                             AND glcode IN ('94321') 
                             AND slcode IS NOT NULL 
                             AND a.unitid = b.unitid 
                             AND a.slcode = b.emplcode 
                    GROUP BY slcode, 
                             emplname 
                    UNION 
                    SELECT   slcode, 
                             emplname, 
                             0 plncash, 
                             0 lta, 
                             Sum(debitamount - creditamount) 
                    FROM     ledger a, 
                             emplmast b 
                    WHERE    a.unitid = 1 
                             AND periodid = 13 
                             AND glcode IN ('94310') 
                             AND slcode IS NOT NULL 
                             AND a.unitid = b.unitid 
                             AND a.slcode = b.emplcode 
                    GROUP BY slcode, 
                             emplname) 
          GROUP BY slcode, 
                   emplname) b 
WHERE    a.emplcode = b.slcode (+) 
ORDER BY a.emplcode);


when i am using order by clause,getting error.

without order by it working, what is the problem

kanish

Re: can't used the order by clause in Cursor? [message #387427 is a reply to message #387419] Thu, 19 February 2009 03:55 Go to previous messageGo to next message
browd@whl.co.uk
Messages: 5
Registered: February 2009
Location: UK
Junior Member
Okay, what I suspect is happening is that the ORDER BY clause doesn't know which emplcode to order by.

I suggest you look into naming your table aliases a bit more neatly.

For a possible quick test, try adding a new alias to your highest a.emplcode and ordering by that.

like:

cursor empgrs is 
(SELECT   a.emplcode emplcode_order, 
         a.emplname, 
         Nvl(plcash,0) pl, 
         Nvl(alta,0)   lta, 
         Nvl(medic,0)  mdi, 
         Nvl(bns,0)    bonus, 
         stiching, 
         washing 
FROM     (SELECT a.emplcode, 
                 emplname, 
                 washing, 
                 stiching, 
                 Nvl(bonus,0) bns 
          FROM   (SELECT   emplcode, 
                           emplname, 
                           Sum(wash)  washing, 
                           Sum(stich) stiching 
                  FROM     (SELECT   a.emplcode, 
                                     emplname, 
                                     Sum(Decode(paycode,'E08',amount, 
                                                        0)) wash, 
                                     Sum(Decode(paycode,'E31',amount, 
                                                        0)) stich 
                            FROM     payfile a, 
                                     emplmast b 
                            WHERE    a.unitid = 1 
                                     AND a.emplcode = b.emplcode 
                                     AND paycode IN ('E31','E08') 
                                     AND Substr(catgcode,1,1) IN ('S','O','T','E', 
                                                                  'W') 
                                     AND ((monthno BETWEEN 04 AND 12 
                                           AND yearno = 2008) 
                                           OR (monthno BETWEEN 01 AND 03 
                                               AND yearno = 2009)) 
                            GROUP BY a.emplcode, 
                                     emplname 
                            UNION 
                            SELECT   a.emplcode, 
                                     emplname, 
                                     0 wash, 
                                     0 stich 
                            FROM     payfile a, 
                                     emplmast b 
                            WHERE    a.unitid = 1 
                                     AND a.emplcode = b.emplcode 
                                     AND paycode IN ('E97') 
                                     AND Substr(catgcode,1,1) IN ('S','O','T','E', 
                                                                  'W') 
                                     AND ((monthno BETWEEN 04 AND 12 
                                           AND yearno = 2008) 
                                           OR (monthno BETWEEN 01 AND 03 
                                               AND yearno = 2009)) 
                            GROUP BY a.emplcode, 
                                     emplname) 
                  GROUP BY emplcode, 
                           emplname) a, 
                 (SELECT emplcode, 
                         gross_amount bonus 
                  FROM   bonus 
                  WHERE  bonus_period = '2007-2008' 
                         AND unitid = 1) b 
          WHERE  a.emplcode = b.emplcode (+)) a, 
         (SELECT   slcode, 
                   emplname, 
                   Sum(plncash) plcash, 
                   Sum(lta)     alta, 
                   Sum(mdi)     medic 
          FROM     (SELECT   slcode, 
                             emplname, 
                             Sum(debitamount - creditamount) plncash, 
                             0                               lta, 
                             0                               mdi 
                    FROM     ledger a, 
                             emplmast b 
                    WHERE    a.unitid = 1 
                             AND periodid = 13 
                             AND glcode IN ('94117','94128','94139') 
                             AND slcode IS NOT NULL 
                             AND a.unitid = b.unitid 
                             AND a.slcode = b.emplcode 
                             AND Upper(particulars) LIKE '%LEAVE ENCASH%' 
                    GROUP BY slcode, 
                             emplname 
                    UNION 
                    SELECT   slcode, 
                             emplname, 
                             0                               plncash, 
                             Sum(debitamount - creditamount) lta, 
                             0                               mdi 
                    FROM     ledger a, 
                             emplmast b 
                    WHERE    a.unitid = 1 
                             AND periodid = 13 
                             AND glcode IN ('94321') 
                             AND slcode IS NOT NULL 
                             AND a.unitid = b.unitid 
                             AND a.slcode = b.emplcode 
                    GROUP BY slcode, 
                             emplname 
                    UNION 
                    SELECT   slcode, 
                             emplname, 
                             0 plncash, 
                             0 lta, 
                             Sum(debitamount - creditamount) 
                    FROM     ledger a, 
                             emplmast b 
                    WHERE    a.unitid = 1 
                             AND periodid = 13 
                             AND glcode IN ('94310') 
                             AND slcode IS NOT NULL 
                             AND a.unitid = b.unitid 
                             AND a.slcode = b.emplcode 
                    GROUP BY slcode, 
                             emplname) 
          GROUP BY slcode, 
                   emplname) b 
WHERE    a.emplcode = b.slcode (+) 
ORDER BY emplcode_order);


If that doesn't work then please post the actual ORA message you're getting.

PS That's really not a very nice piece of SQL can you not re-write it?
Re: can't used the order by clause in Cursor? [message #387430 is a reply to message #387419] Thu, 19 February 2009 04:00 Go to previous message
mm_kanish05
Messages: 487
Registered: January 2007
Location: Chennai
Senior Member

Thanks anyway

I solved my issue.

just i removed "(" . it's working fine now.

first

cursor empgrs is 
[COLOR=red]([/COLOR]SELECT   a.emplcode emplcode_order, 


and final statment


ORDER BY emplcode_order[COLOR=red])[/COLOR];



kanish
Previous Topic: FRM-40564:RECORD HAS BEEN UPDATED BY ANOTHER USER-REQUERY TO SEE CHANGE
Next Topic: Framing a search query at run time, considering columns from diff tables.
Goto Forum:
  


Current Time: Sat Dec 10 22:52:23 CST 2016

Total time taken to generate the page: 0.08351 seconds