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  |
mm_kanish05
Messages: 493
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  |
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?
|
|
|
|
|
|
Goto Forum:
Current Time: Mon Feb 03 20:41:32 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
