i want to list only those employees records who got "A10" this month.
SQL> DESC PROLL
Name
--------------------------------------------------
PRECODE
PRALWCODE
PRALWAMT
PRMNTH
SQL> seLECT * FROM PROLL;
PRECOD PRALWC PRALWAMT PRMNTH
------ ------ ------------- -------------
1001 a01 200.000 200811.000
1001 a02 150.000 200811.000
1001 a03 100.000 200811.000
1001 a04 50.000 200811.000
1001 d01 50.000 200811.000
1002 a01 400.000 200811.000
1002 a02 250.000 200811.000
1002 a03 200.000 200811.000
1002 a10 150.000 200811.000
1002 d01 100.000 200811.000
1003 a01 400.000 200811.000
1003 a02 250.000 200811.000
1003 a03 200.000 200811.000
1003 a10 250.000 200811.000
1003 d01 100.000 200811.000
1004 a01 300.000 200811.000
1004 a02 200.000 200811.000
1004 a03 50.000 200811.000
1005 a01 500.000 200811.000
1005 a02 300.000 200811.000
1005 a03 100.000 200811.000
1005 a10 500.000 200811.000
1005 d01 70.000 200811.000
23 rows selected.
SQL> ed
Wrote file afiedt.buf
1 SELECT precode,ENAME,SUBSTR(TO_CHAR(prmnth),1,6) MNTH,
2 SUM(DECODE(pralwCODE,'a01',PRALWAMT)) "BASICA01",
3 SUM(DECODE(pralwCODE,'a02',PRALWAMT)) "HRAA02",
4 SUM(DECODE(pralwCODE,'a03',PRALWAMT)) "TRPTA03",
5 SUM(DECODE(pralwCODE,'a04',PRALWAMT)) "TELA04",
6 SUM(DECODE(pralwCODE,'a10',PRALWAMT)) "leaveA04",
7 SUM(DECODE(pralwCODE,'d01',PRALWAMT*-1)) "Advd01"
8 FROM proll,EmP_masT
9 WHERE precode = ECODE
10 AND PRMNTH = 200811
11 AND EXISTS (SELECT 'X' FROM proll
12 where PRALWCODE = 'a10'
13 AND PRMNTH = 200811)
14 GROUP BY precode,ENAME,SUBSTR(TO_CHAR(prmnth),1,6)
15* ORDER BY precode
SQL> /
i want to keep SUM function in the query, so that i could run report for range of months.
PRECOD ENAME MNTH BASICA01 HRAA02 TRPTA03 TELA04 leaveA04 Advd01
------ --------------- ------ ------------- ------------- ------------- ------------- ------------- -------------
1001 aaa 200811 200.000 150.000 100.000 50.000 -50.000
1002 bbb 200811 400.000 250.000 200.000 150.000 -100.000
1003 ccc 200811 400.000 250.000 200.000 250.000 -100.000
1004 ddd 200811 300.000 200.000 50.000
1005 eee 200811 500.000 300.000 100.000 500.000 -70.000
the query should actually give me only records for empno '1002','1003' & '1005';
ecode ename basicA01 hraA02 trptA03 telA04 leaveA10 advD01
1002 bbb 400 250 200 150 -100
1003 ccc 400 250 200 250 -100
1005 eee 500 300 100 500 -70