Report Query [message #170520] |
Thu, 04 May 2006 04:13 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
amit_garg3
Messages: 22 Registered: January 2006 Location: bangalore
|
Junior Member |
|
|
Hi
My question is I want to select gross salary of a emp and gross salary is in number of table eg tab_01 for jan,tab_02 for feb & so on till tab_12 for dec.now i give the input empno,payperiod like 200406.as output i want empno, payperiod, gross salary from 200401 to 200406.How to make this report.
empno payperiod grosssal
1234 200401 10000
1234 200402 10000
1234 200403 10000
so on till 200406
Bye
|
|
|
Re: Report Query [message #170893 is a reply to message #170520] |
Fri, 05 May 2006 16:58 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/72104.gif) |
Littlefoot
Messages: 21818 Registered: June 2005 Location: Croatia, Europe
|
Senior Member Account Moderator |
|
|
If I understood you correctly, there are 12 different tables which store salaries for every month in a year.
To select employee's salary throughout the year, I'd suggest you to create a view which would be UNION of all 12 tables; something like this:
CREATE VIEW salaries AS
SELECT empno, payperiod, grossal FROM tab_01
UNION
SELECT empno, payperiod, grossal FROM tab_02
UNION
...
SELECT empno, payperiod, grossal FROM tab_12;
Now it should be quite simple to write a query you need.
|
|
|
Re: Report Query [message #170915 is a reply to message #170893] |
Sat, 06 May 2006 04:02 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
amit_garg3
Messages: 22 Registered: January 2006 Location: bangalore
|
Junior Member |
|
|
Hi
I can create view of table containing grosssal.But i have to give no of table as input.Suppose pay period 200405 have table tab_200405,200406 has table tab_200406.Now i give the input 200406.My query have to display gross salary of all the table before 200406(ie tab_200401,tab_200402-------to tab_200406).How can I do this.
Thanks & Bye
|
|
|
Re: Report Query [message #170950 is a reply to message #170915] |
Sat, 06 May 2006 10:18 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/72104.gif) |
Littlefoot
Messages: 21818 Registered: June 2005 Location: Croatia, Europe
|
Senior Member Account Moderator |
|
|
Would you mind to spend a few moments and take a piece of paper, write data you have and output you'd like to have. Then you'll see a solution.
Did you figure it out? Not yet? Hint:
- you have 200402
- you want TAB_200402
How will you get it? Another hint? Concatenation?SQL> create table tab_200401 (empno number, payperiod number, grossal number);
Table created.
SQL> insert into tab_200401 values (1234, 200401, 10000);
1 row created.
SQL> create table tab_200402 as select * From tab_200401;
Table created.
SQL> update tab_200402 set payperiod = 200402;
1 row updated.
SQL> create view salaries as
2 select empno, payperiod, grossal from tab_200401
3 union
4 select empno, payperiod, grossal from tab_200402;
View created.
SQL> select 'tab_' || payperiod, empno, grossal
2 from salaries
3 where empno = 1234
4 and payperiod <= 200402;
'TAB_'||PAYPERIOD EMPNO GROSSAL
-------------------------------------------- ---------- ----------
tab_200401 1234 10000
tab_200402 1234 10000
SQL>
|
|
|