PL/SQL - Combine Results of Several Select Queries
I have three SELECT queries with each one returning a result set such as:
Query 1: emp_id amt from table1.fieldname
Query 2: emp_id amt from table2.fieldname
Query 3: emp_id amt from table3.fieldname
The emp_id field is also in each of the three tables, but the way the SELECT statements are written, the result set of each query includes the same list of emp_id's.
I would like to combine the three queries into one so that I get a result set that looks like this:
emp_id amt table1.fieldname amt table2.fieldname amt table3.fieldname
If this is possible, then it is my preferred approach, but I don't know how to combine the output of multiple SELECT queries into a single result set. If not, then here are the others I've considered but don't know how to do:
I don't want to ALTER TABLE on any of the three tables to add a field for amt to them. If it has to be that I need to create a temporary table in order to get this to work, then I'd need to know the syntax to add the table (which I can then update with the result of each query accordingly) and then, after I've used the data, I'd want to remove the temporary table. I could also create a new table with the emp_id field and a field for each of the three amounts, then update that table with update statements included in each of the three queries, then update it again when I'm done, returning the fields to NULL. If this seems the most logical, I'd prefer to do it that way than to alter an existing table or create a temporary one, since I will be running this sequence once each week.
What's the best approach, and for that best approach, how would I write the SQL to do it?
Query 1: emp_id amt from table1.fieldname
Query 2: emp_id amt from table2.fieldname
Query 3: emp_id amt from table3.fieldname
The emp_id field is also in each of the three tables, but the way the SELECT statements are written, the result set of each query includes the same list of emp_id's.
I would like to combine the three queries into one so that I get a result set that looks like this:
emp_id amt table1.fieldname amt table2.fieldname amt table3.fieldname
If this is possible, then it is my preferred approach, but I don't know how to combine the output of multiple SELECT queries into a single result set. If not, then here are the others I've considered but don't know how to do:
I don't want to ALTER TABLE on any of the three tables to add a field for amt to them. If it has to be that I need to create a temporary table in order to get this to work, then I'd need to know the syntax to add the table (which I can then update with the result of each query accordingly) and then, after I've used the data, I'd want to remove the temporary table. I could also create a new table with the emp_id field and a field for each of the three amounts, then update that table with update statements included in each of the three queries, then update it again when I'm done, returning the fields to NULL. If this seems the most logical, I'd prefer to do it that way than to alter an existing table or create a temporary one, since I will be running this sequence once each week.
What's the best approach, and for that best approach, how would I write the SQL to do it?
UNOIN
You are looking for union.
sql example:
select emp_id form from table1
union
select emp_id form from table2
union
select emp_id form from table3
rgds
rumi
You are looking for union.
sql example:
select emp_id form from table1
union
select emp_id form from table2
union
select emp_id form from table3
rgds
rumi
What does the process look like now? Since you say 'result set' I'm guessing three disjointed queries executed one at a time by some program.
Can you combine all three into a single select using table joins or UNION?
Can you combine all three into a single select using table joins or UNION?
runi,
That doesn't show returning the 3 amounts as separate columns.
Since the UNION was already posted, you should be able to get there with something similar to (typed in, untested):
select empid, sum(amt1), sum(amt2), sum(amt3) from (
select empid, amt1, 0 amt2, 0 amt3 from table1
union
select empid, 0 amt1, amt2, 0 amt3 from table3
union
select empid, 0 amt1, 0 amt2,amt3 from table3
)
group by emp_id
That doesn't show returning the 3 amounts as separate columns.
Since the UNION was already posted, you should be able to get there with something similar to (typed in, untested):
select empid, sum(amt1), sum(amt2), sum(amt3) from (
select empid, amt1, 0 amt2, 0 amt3 from table1
union
select empid, 0 amt1, amt2, 0 amt3 from table3
union
select empid, 0 amt1, 0 amt2,amt3 from table3
)
group by emp_id
Dear slightwv,
you are right, I didnt understand the question, the solution is just select them as a columns :), only emp_id source could be the problem, but we can use union :)
select
id, (select table1.fieldname from table1 where table1.emp_id=id),(select table2.fieldname from table2 where table2.emp_id=id),(select table3.fieldname from table3 where table3.emp_id=id)
from (
select emp_id as id form from table1
union
select emp_id as id form from table2
union
select emp_id as id form from table3
)
rgds
rumi
you are right, I didnt understand the question, the solution is just select them as a columns :), only emp_id source could be the problem, but we can use union :)
select
id, (select table1.fieldname from table1 where table1.emp_id=id),(select table2.fieldname from table2 where table2.emp_id=id),(select table3.fieldname from table3 where table3.emp_id=id)
from (
select emp_id as id form from table1
union
select emp_id as id form from table2
union
select emp_id as id form from table3
)
rgds
rumi
Dear glennes,
if you have different emp_id source use it iso union, otherwise rows will be duplicated.
I don't know the schema so I used union here
rgds
rumi
if you have different emp_id source use it iso union, otherwise rows will be duplicated.
I don't know the schema so I used union here
rgds
rumi
sumi,
That union query you posted really doesn't make that much sense. You want to use an inline column from table1 then select that from an inline view using union?
I really don't think that will work.
That union query you posted really doesn't make that much sense. You want to use an inline column from table1 then select that from an inline view using union?
I really don't think that will work.
union is only source of emp_id, If you can assume that all emp_id are in table1 you can use:
select
id, (select table1.fieldname from table1 where table1.emp_id=id),(select table2.fieldname from table2 where table2.emp_id=id),(select table3.fieldname from table3 where table3.emp_id=id)
from (
select emp_id as id form from table1
)
rgds
rumi
select
id, (select table1.fieldname from table1 where table1.emp_id=id),(select table2.fieldname from table2 where table2.emp_id=id),(select table3.fieldname from table3 where table3.emp_id=id)
from (
select emp_id as id form from table1
)
rgds
rumi
if all of your three individual queries ( query 1 , 2 and 3 ) get the same
emp_id's in the output then it can be done with a simple join. i assume each query gets one empid only once, i mean a single record and the below should work for you :
select a.emp_id,
a.amt table1_amt,
b.amt table2_amt,
c.amt table3_amt
from ( query 1 ) a,
( query 2 ) b,
( query 3 ) c
where a.emp_id = b.emp_id
and b.emp_id = c.emp_id;
If there are duplicate records for each empid in any of your three queries
or each query brings its own set of emp_ids which may or may not be the same as the empid's given by other two queries, then we can use a union in this case to get everything...
There are already 2 union queries given by "rumi78" and one given by "sligthwv" and you can use anyone of them which ever suits your data...
Thanks
emp_id's in the output then it can be done with a simple join. i assume each query gets one empid only once, i mean a single record and the below should work for you :
select a.emp_id,
a.amt table1_amt,
b.amt table2_amt,
c.amt table3_amt
from ( query 1 ) a,
( query 2 ) b,
( query 3 ) c
where a.emp_id = b.emp_id
and b.emp_id = c.emp_id;
If there are duplicate records for each empid in any of your three queries
or each query brings its own set of emp_ids which may or may not be the same as the empid's given by other two queries, then we can use a union in this case to get everything...
There are already 2 union queries given by "rumi78" and one given by "sligthwv" and you can use anyone of them which ever suits your data...
Thanks
Checkout the attached code.
SQL> SELECT * FROM t1;
A EMPID
---------- ----------
50 10
100 99
SQL> ed
Wrote file afiedt.buf
1* SELECT * FROM t2
SQL> /
A EMPID
---------- ----------
25 99
15 10
SQL> ed
Wrote file afiedt.buf
1* SELECT * FROM t3
SQL> /
A EMPID
---------- ----------
45 10
85 99
SQL> SELECT empid, a, 't1' tab_alias FROM t1
2 UNION ALL
3 SELECT empid, a, 't2' FROM t2
4 UNION ALL
5 SELECT empid, a, 't3' FROM t3
6 /
EMPID A TA
---------- ---------- --
10 50 t1
99 100 t1
99 25 t2
10 15 t2
10 45 t3
99 85 t3
6 rows selected.
SQL> ed
Wrote file afiedt.buf
1 SELECT empid, decode(tab_alias,'t1',a) t1_val, decode(tab_alias,'t2',a) t2_val, decode(tab_alias,'t3',a) t3_val
2 FROM
3 (SELECT empid, a, 't1' tab_alias FROM t1
4 UNION ALL
5 SELECT empid, a, 't2' FROM t2
6 UNION ALL
7* SELECT empid, a, 't3' FROM t3)
SQL> /
EMPID T1_VAL T2_VAL T3_VAL
---------- ---------- ---------- ----------
10 50
99 100
99 25
10 15
10 45
99 85
6 rows selected.
SQL> ed
Wrote file afiedt.buf
1 SELECT empid, MAX(decode(tab_alias,'t1',a)) t1_val, MAX(decode(tab_alias,'t2',a)) t2_val, MAX(decode(tab_alias,'t3',a)) t3_val
2 FROM
3 (SELECT empid, a, 't1' tab_alias FROM t1
4 UNION ALL
5 SELECT empid, a, 't2' FROM t2
6 UNION ALL
7 SELECT empid, a, 't3' FROM t3)
8* GROUP BY empid
SQL> /
EMPID T1_VAL T2_VAL T3_VAL
---------- ---------- ---------- ----------
99 100 25 85
10 50 15 45
riazpk: I like the way your logic looks, but I'm not using SQL*PLUS to write the script. It looks like you are. I'm using Oracle SQL Developer, in which I'm writing SQL query statements directly against the tables in the database. Oracle Developer does not know what the "ed" command is.
How do I do the same thing you're doing in your code without using SQL*PLUS?
How do I do the same thing you're doing in your code without using SQL*PLUS?
ed is just a sqlplus command to 'edit' the sql buffer.
Just use the SELECT statements.
FYI: That code is very similar to what I posted.
Just use the SELECT statements.
FYI: That code is very similar to what I posted.
slightwv: I hear you, but still am not real clear on how to do this. I did download a new tool this morning that lets me run these scripts in a SQL*PLUS environment, so that issue is no longer a problem. I think, in order to help you (and any of the other experts) understand what I'm attempting to do, I've included below the separate SELECT sections, one for each result set that I want to UNION. As you see, the result set for each one is 'emp_id' and an amount. What I'm not clear on is how to write the result sets to the file "afieldt.buf" so that I can then run the part of riazpk's script (beginning on line 51 in his code snippet) that will actually provide the final result set I need.
Because of the way our Oracle database is built, these three SELECTs are different to a minor degree so as to enable each one to return the correct amounts in their result sets. Even though each SELECT returns an 'emp_id' and amount, the result set for each of them may not include ALL, or the same number of, emp_id records in the individual result sets. However, because of the way the code (beginning with the lines following "WHERE pr_year = 2010) is written, all the emp_id's that are a part of that particular SELECTwill be included. But, some 'emp_id's might not have had a REIMBURSEMENT AMOUNT, for example, but will still have an amount for FIT earnings and Section 125 deductions. In those cases, the final result set will still include all the selected 'emp_id's, but the 'amount' field for REIMBURSEMENT for that particular 'emp_id in the final result set would be 0, because that emp_id was not included in the emp_id's in the result set returned by the SELECT script for REIMBURSEMENT AMOUNT. Recall, in the final result set, there is only one record for each emp_id, with that record looking like:
emp_id amt table1.fieldname amt table2.fieldname amt table3.fieldname
Maybe by having my actual code, one of you can tell me how to get to that final point with it.
Here's the code:
--------------------------
--Gets YTD Reimbursement Amount
ACCEPT SAL_PR_PD_END_DATE PROMPT 'Enter SALARIED PR Period End Date as YYYY/MM/DD:';
SELECT f.emp_id, sum(n.amt) over (partition by f.emp_id)
FROM lab_tran l, deduction d, pr_chk_hist p, emp_ded e, employee f, emp_ytd_reimb n
WHERE n.pr_year = 2010
AND (f.alpha_6 = 'NORM' OR f.alpha_6 = 'IMPR')
AND l.ded_num = d.ded_num
AND l.pr_chk_sa_num = p.pr_chk_sa_num
AND n.emp_id = f.emp_id
AND n.reimb_num <> 20
AND ((l.ded_num = '553' AND e.pct IS NOT NULL) OR (l.ded_num = '567' AND e.pct IS NOT NULL))
AND ((p.pay_group_num IN ('200','250','325','625','
AND p.prd_end_date = to_char(to_date('&SAL_PR_P
OR (p.prd_end_date = to_char(to_date('&&sal_pr_
AND p.pay_group_num IN ('100','110','300','400','
AND f.emp_id = p.emp_id
GROUP BY f.emp_id, n.emp_id, n.amt
UNION
--Gets YTD FIT Earnings
SELECT f.emp_id, v.fit_earn
FROM lab_tran l, deduction d, pr_chk_hist p, emp_ded e, employee f, emp_ytd_fed v
WHERE v.pr_year = 2010
AND (f.alpha_6 = 'NORM' OR f.alpha_6 = 'IMPR')
AND l.ded_num = d.ded_num
AND l.pr_chk_sa_num = p.pr_chk_sa_num
AND v.emp_id = f.emp_id
AND ((l.ded_num = '553' AND e.pct IS NOT NULL) OR (l.ded_num = '567' AND e.pct IS NOT NULL))
AND ((p.pay_group_num IN ('200','250','325','625','
AND p.prd_end_date = to_char(to_date('&SAL_PR_P
OR (p.prd_end_date = to_char(to_date('&&sal_pr_
AND p.pay_group_num IN ('100','110','300','400','
AND f.emp_id = p.emp_id
GROUP BY f.emp_id, p.emp_id, p.d_prd_end_date, f.alpha_6, v.emp_id, v.fit_earn
UNION
--Gets Section 125 Total Amount
SELECT a.emp_id, sum(a.amt)
FROM emp_ytd_ded a, deduction b, pr_chk_hist p
where a.pr_year = 2010
and a.ded_num = b.ded_num
AND ((b.ded_num = '553' ) OR (b.ded_num = '567'))
and b.acr_acct_num in ('2111','2112','2126', '2128','2129')
and a.ded_num IN ('353','354','355','356','
'474','512','513','517','5
AND a.pr_year = to_number(to_char(to_date(
AND ((p.pay_group_num IN ('200','250','325','625','
AND p.prd_end_date = to_char(to_date('&SAL_PR_P
OR (p.prd_end_date = to_char(to_date('&&sal_pr_
AND p.pay_group_num IN ('100','110','300','400','
AND ((p.emp_id = a.emp_id AND b.ded_num = '553') OR (p.emp_id = a.emp_id AND b.ded_num = '567'))
GROUP BY a.emp_id
--------------------------
Because of the way our Oracle database is built, these three SELECTs are different to a minor degree so as to enable each one to return the correct amounts in their result sets. Even though each SELECT returns an 'emp_id' and amount, the result set for each of them may not include ALL, or the same number of, emp_id records in the individual result sets. However, because of the way the code (beginning with the lines following "WHERE pr_year = 2010) is written, all the emp_id's that are a part of that particular SELECTwill be included. But, some 'emp_id's might not have had a REIMBURSEMENT AMOUNT, for example, but will still have an amount for FIT earnings and Section 125 deductions. In those cases, the final result set will still include all the selected 'emp_id's, but the 'amount' field for REIMBURSEMENT for that particular 'emp_id in the final result set would be 0, because that emp_id was not included in the emp_id's in the result set returned by the SELECT script for REIMBURSEMENT AMOUNT. Recall, in the final result set, there is only one record for each emp_id, with that record looking like:
emp_id amt table1.fieldname amt table2.fieldname amt table3.fieldname
Maybe by having my actual code, one of you can tell me how to get to that final point with it.
Here's the code:
--------------------------
--Gets YTD Reimbursement Amount
ACCEPT SAL_PR_PD_END_DATE PROMPT 'Enter SALARIED PR Period End Date as YYYY/MM/DD:';
SELECT f.emp_id, sum(n.amt) over (partition by f.emp_id)
FROM lab_tran l, deduction d, pr_chk_hist p, emp_ded e, employee f, emp_ytd_reimb n
WHERE n.pr_year = 2010
AND (f.alpha_6 = 'NORM' OR f.alpha_6 = 'IMPR')
AND l.ded_num = d.ded_num
AND l.pr_chk_sa_num = p.pr_chk_sa_num
AND n.emp_id = f.emp_id
AND n.reimb_num <> 20
AND ((l.ded_num = '553' AND e.pct IS NOT NULL) OR (l.ded_num = '567' AND e.pct IS NOT NULL))
AND ((p.pay_group_num IN ('200','250','325','625','
AND p.prd_end_date = to_char(to_date('&SAL_PR_P
OR (p.prd_end_date = to_char(to_date('&&sal_pr_
AND p.pay_group_num IN ('100','110','300','400','
AND f.emp_id = p.emp_id
GROUP BY f.emp_id, n.emp_id, n.amt
UNION
--Gets YTD FIT Earnings
SELECT f.emp_id, v.fit_earn
FROM lab_tran l, deduction d, pr_chk_hist p, emp_ded e, employee f, emp_ytd_fed v
WHERE v.pr_year = 2010
AND (f.alpha_6 = 'NORM' OR f.alpha_6 = 'IMPR')
AND l.ded_num = d.ded_num
AND l.pr_chk_sa_num = p.pr_chk_sa_num
AND v.emp_id = f.emp_id
AND ((l.ded_num = '553' AND e.pct IS NOT NULL) OR (l.ded_num = '567' AND e.pct IS NOT NULL))
AND ((p.pay_group_num IN ('200','250','325','625','
AND p.prd_end_date = to_char(to_date('&SAL_PR_P
OR (p.prd_end_date = to_char(to_date('&&sal_pr_
AND p.pay_group_num IN ('100','110','300','400','
AND f.emp_id = p.emp_id
GROUP BY f.emp_id, p.emp_id, p.d_prd_end_date, f.alpha_6, v.emp_id, v.fit_earn
UNION
--Gets Section 125 Total Amount
SELECT a.emp_id, sum(a.amt)
FROM emp_ytd_ded a, deduction b, pr_chk_hist p
where a.pr_year = 2010
and a.ded_num = b.ded_num
AND ((b.ded_num = '553' ) OR (b.ded_num = '567'))
and b.acr_acct_num in ('2111','2112','2126', '2128','2129')
and a.ded_num IN ('353','354','355','356','
'474','512','513','517','5
AND a.pr_year = to_number(to_char(to_date(
AND ((p.pay_group_num IN ('200','250','325','625','
AND p.prd_end_date = to_char(to_date('&SAL_PR_P
OR (p.prd_end_date = to_char(to_date('&&sal_pr_
AND p.pay_group_num IN ('100','110','300','400','
AND ((p.emp_id = a.emp_id AND b.ded_num = '553') OR (p.emp_id = a.emp_id AND b.ded_num = '567'))
GROUP BY a.emp_id
--------------------------
>>What I'm not clear on is how to write the result sets to the file "afieldt.buf"
There's no need to do this. I think the confusion here is in the tool syntax not the SQL itself.
If you have the select, save it to a file and execute the file in sql*plus using '@'.
SQL> @myscript
in any other tool just copy and paste the SELECT itself.
There's no need to do this. I think the confusion here is in the tool syntax not the SQL itself.
If you have the select, save it to a file and execute the file in sql*plus using '@'.
SQL> @myscript
in any other tool just copy and paste the SELECT itself.
nav_kum_v:
I applied your code structure to my code as follows. When I run it, SQL stops at line 2 and says "ORA-00904: "m"."amt": invalid identifier ". Just to check, I changed the "m" table references inside the SQL script for (query 1) m to another letter to see if SQL didn't like having the "m" used inside query 1 and then naming the entire query "(SELECT... ) m", but I still got the same error. If I run query 1 (or any of the individual queries) independently as queries, they all run and return the correct results, so I know these individual queries work. But, when I turn them into sub-queries using your construct, I get this error before the process even gets to the first sub-query. What have I done wrong in applying your construct to cause the 00904 error?
(BTW - the only one of the four sub-queries where the full set of emp_id's will be included is sub-query "v". The other three sub-queries will not contain all emp_id's in "v"; however, the emp_id's in the result sets of the other three sub-queries (m, n, and b) will not have any emp_id's that are not returned by sub-query "v" - if that matters.)
--------------------------
ACCEPT SAL_PR_PD_END_DATE PROMPT 'Enter SALARIED PR Period End Date as YYYY/MM/DD:';
SELECT m.emp_id, m.amt table1_amt,
n.amt table2_amt,
v.fit_earn table3_amt,
b.amt table4_amt
FROM
(SELECT f.emp_id, sum(m.amt) over (partition by f.emp_id)
FROM lab_tran l, deduction d, pr_chk_hist p, emp_ded e, employee f, emp_ytd_other m
WHERE m.pr_year = 2010
AND (f.alpha_6 = 'NORM' OR f.alpha_6 = 'IMPR')
AND l.ded_num = d.ded_num
AND l.pr_chk_sa_num = p.pr_chk_sa_num
AND m.emp_id = f.emp_id
AND m.comp_num IN (5,40)
AND ((l.ded_num = '553' AND e.pct IS NOT NULL) OR (l.ded_num = '567' AND e.pct IS NOT NULL))
AND ((p.pay_group_num IN ('200','250','325','625','
AND p.prd_end_date = to_char(to_date('&SAL_PR_P
OR (p.prd_end_date = to_char(to_date('&&sal_pr_
AND p.pay_group_num IN ('100','110','300','400','
AND f.emp_id = p.emp_id
GROUP BY f.emp_id, m.amt) m,
(SELECT f.emp_id, n.amt
FROM lab_tran l, deduction d, pr_chk_hist p, emp_ded e, employee f, emp_ytd_reimb n
WHERE n.pr_year = 2010
AND (f.alpha_6 = 'NORM' OR f.alpha_6 = 'IMPR')
AND l.ded_num = d.ded_num
AND l.pr_chk_sa_num = p.pr_chk_sa_num
AND n.emp_id = f.emp_id
AND n.reimb_num <> 20
AND ((l.ded_num = '553' AND e.pct IS NOT NULL) OR (l.ded_num = '567' AND e.pct IS NOT NULL))
AND ((p.pay_group_num IN ('200','250','325','625','
AND p.prd_end_date = to_char(to_date('&SAL_PR_P
OR (p.prd_end_date = to_char(to_date('&&sal_pr_
AND p.pay_group_num IN ('100','110','300','400','
AND f.emp_id = p.emp_id
GROUP BY f.emp_id, n.amt) n,
(SELECT f.emp_id, v.fit_earn
FROM lab_tran l, deduction d, pr_chk_hist p, emp_ded e, employee f, emp_ytd_fed v
WHERE v.pr_year = 2010
AND (f.alpha_6 = 'NORM' OR f.alpha_6 = 'IMPR')
AND l.ded_num = d.ded_num
AND l.pr_chk_sa_num = p.pr_chk_sa_num
AND v.emp_id = f.emp_id
AND ((l.ded_num = '553' AND e.pct IS NOT NULL) OR (l.ded_num = '567' AND e.pct IS NOT NULL))
AND ((p.pay_group_num IN ('200','250','325','625','
AND p.prd_end_date = to_char(to_date('&SAL_PR_P
OR (p.prd_end_date = to_char(to_date('&&sal_pr_
AND p.pay_group_num IN ('100','110','300','400','
AND f.emp_id = p.emp_id
GROUP BY f.emp_id, v.fit_earn) v,
(SELECT f.emp_id, b.amt
FROM lab_tran l, deduction d, pr_chk_hist p, emp_ded e, employee f, emp_ytd_ded b
WHERE b.pr_year = 2010
AND (f.alpha_6 = 'NORM' OR f.alpha_6 = 'IMPR')
AND l.ded_num = d.ded_num
AND l.pr_chk_sa_num = p.pr_chk_sa_num
AND f.emp_id = b.emp_id
AND ((l.ded_num = '553' AND e.pct IS NOT NULL) OR (l.ded_num = '567' AND e.pct IS NOT NULL))
AND ((p.pay_group_num IN ('200','250','325','625','
AND p.prd_end_date = to_char(to_date('&SAL_PR_P
OR (p.prd_end_date = to_char(to_date('&&sal_pr_
AND p.pay_group_num IN ('100','110','300','400','
and b.ded_num IN ('353','354','355','356','
'423','429','437','446','4
'546','553','561','562','5
and d.acr_acct_num in ('2111','2112','2126', '2128','2129')
AND f.emp_id = p.emp_id
GROUP BY f.emp_id, b.amt) b
WHERE m.emp_id = n.emp_id
AND n.emp_id = v.emp_id
AND v.emp_id = b.emp_id;
I applied your code structure to my code as follows. When I run it, SQL stops at line 2 and says "ORA-00904: "m"."amt": invalid identifier ". Just to check, I changed the "m" table references inside the SQL script for (query 1) m to another letter to see if SQL didn't like having the "m" used inside query 1 and then naming the entire query "(SELECT... ) m", but I still got the same error. If I run query 1 (or any of the individual queries) independently as queries, they all run and return the correct results, so I know these individual queries work. But, when I turn them into sub-queries using your construct, I get this error before the process even gets to the first sub-query. What have I done wrong in applying your construct to cause the 00904 error?
(BTW - the only one of the four sub-queries where the full set of emp_id's will be included is sub-query "v". The other three sub-queries will not contain all emp_id's in "v"; however, the emp_id's in the result sets of the other three sub-queries (m, n, and b) will not have any emp_id's that are not returned by sub-query "v" - if that matters.)
--------------------------
ACCEPT SAL_PR_PD_END_DATE PROMPT 'Enter SALARIED PR Period End Date as YYYY/MM/DD:';
SELECT m.emp_id, m.amt table1_amt,
n.amt table2_amt,
v.fit_earn table3_amt,
b.amt table4_amt
FROM
(SELECT f.emp_id, sum(m.amt) over (partition by f.emp_id)
FROM lab_tran l, deduction d, pr_chk_hist p, emp_ded e, employee f, emp_ytd_other m
WHERE m.pr_year = 2010
AND (f.alpha_6 = 'NORM' OR f.alpha_6 = 'IMPR')
AND l.ded_num = d.ded_num
AND l.pr_chk_sa_num = p.pr_chk_sa_num
AND m.emp_id = f.emp_id
AND m.comp_num IN (5,40)
AND ((l.ded_num = '553' AND e.pct IS NOT NULL) OR (l.ded_num = '567' AND e.pct IS NOT NULL))
AND ((p.pay_group_num IN ('200','250','325','625','
AND p.prd_end_date = to_char(to_date('&SAL_PR_P
OR (p.prd_end_date = to_char(to_date('&&sal_pr_
AND p.pay_group_num IN ('100','110','300','400','
AND f.emp_id = p.emp_id
GROUP BY f.emp_id, m.amt) m,
(SELECT f.emp_id, n.amt
FROM lab_tran l, deduction d, pr_chk_hist p, emp_ded e, employee f, emp_ytd_reimb n
WHERE n.pr_year = 2010
AND (f.alpha_6 = 'NORM' OR f.alpha_6 = 'IMPR')
AND l.ded_num = d.ded_num
AND l.pr_chk_sa_num = p.pr_chk_sa_num
AND n.emp_id = f.emp_id
AND n.reimb_num <> 20
AND ((l.ded_num = '553' AND e.pct IS NOT NULL) OR (l.ded_num = '567' AND e.pct IS NOT NULL))
AND ((p.pay_group_num IN ('200','250','325','625','
AND p.prd_end_date = to_char(to_date('&SAL_PR_P
OR (p.prd_end_date = to_char(to_date('&&sal_pr_
AND p.pay_group_num IN ('100','110','300','400','
AND f.emp_id = p.emp_id
GROUP BY f.emp_id, n.amt) n,
(SELECT f.emp_id, v.fit_earn
FROM lab_tran l, deduction d, pr_chk_hist p, emp_ded e, employee f, emp_ytd_fed v
WHERE v.pr_year = 2010
AND (f.alpha_6 = 'NORM' OR f.alpha_6 = 'IMPR')
AND l.ded_num = d.ded_num
AND l.pr_chk_sa_num = p.pr_chk_sa_num
AND v.emp_id = f.emp_id
AND ((l.ded_num = '553' AND e.pct IS NOT NULL) OR (l.ded_num = '567' AND e.pct IS NOT NULL))
AND ((p.pay_group_num IN ('200','250','325','625','
AND p.prd_end_date = to_char(to_date('&SAL_PR_P
OR (p.prd_end_date = to_char(to_date('&&sal_pr_
AND p.pay_group_num IN ('100','110','300','400','
AND f.emp_id = p.emp_id
GROUP BY f.emp_id, v.fit_earn) v,
(SELECT f.emp_id, b.amt
FROM lab_tran l, deduction d, pr_chk_hist p, emp_ded e, employee f, emp_ytd_ded b
WHERE b.pr_year = 2010
AND (f.alpha_6 = 'NORM' OR f.alpha_6 = 'IMPR')
AND l.ded_num = d.ded_num
AND l.pr_chk_sa_num = p.pr_chk_sa_num
AND f.emp_id = b.emp_id
AND ((l.ded_num = '553' AND e.pct IS NOT NULL) OR (l.ded_num = '567' AND e.pct IS NOT NULL))
AND ((p.pay_group_num IN ('200','250','325','625','
AND p.prd_end_date = to_char(to_date('&SAL_PR_P
OR (p.prd_end_date = to_char(to_date('&&sal_pr_
AND p.pay_group_num IN ('100','110','300','400','
and b.ded_num IN ('353','354','355','356','
'423','429','437','446','4
'546','553','561','562','5
and d.acr_acct_num in ('2111','2112','2126', '2128','2129')
AND f.emp_id = p.emp_id
GROUP BY f.emp_id, b.amt) b
WHERE m.emp_id = n.emp_id
AND n.emp_id = v.emp_id
AND v.emp_id = b.emp_id;
To resolve this error, is very simple and all you need to do is to give an alias name amt to the below... Give it a try :
SELECT f.emp_id, sum(m.amt) over (partition by f.emp_id) should be changed as
SELECT f.emp_id, sum(m.amt) over (partition by f.emp_id) amt
Just to reiterate, you need to have emp id's in all your m , n , b, and v queries and only then your join will return data.. i mean only those which are found in all the 4 queries ( m,n,b,v ) are returned by the join.
If you still need to have those emp id's which are present in one of the table but not in one of the other queries, then you need to keep in mind that you can use outer joins in the query instead of an equi join. Your join query is using an equi join.
Thanks
SELECT f.emp_id, sum(m.amt) over (partition by f.emp_id) should be changed as
SELECT f.emp_id, sum(m.amt) over (partition by f.emp_id) amt
Just to reiterate, you need to have emp id's in all your m , n , b, and v queries and only then your join will return data.. i mean only those which are found in all the 4 queries ( m,n,b,v ) are returned by the join.
If you still need to have those emp id's which are present in one of the table but not in one of the other queries, then you need to keep in mind that you can use outer joins in the query instead of an equi join. Your join query is using an equi join.
Thanks
nav_kum_v...thanks SO much for your help!
Adding 'amt' solved that problem!
There's still a couple of things I don't fully understand how to do after reading all the expert's postings above. (Please, other experts, feel free to reply along with nav_kum_v):
1. The result set from running the above query returns the correct list of employees, with correct data in emp_id and in all four of the 'amt' fields (m.amt table1_amt, n.amt table2_amt, v.fit_earn table3_amt, and b.amt table4_amt). I did have to change the SELECTs to SELECT DISTINCT in all but the table 'v' query in order to get rid of duplicate records.
What I need to do is to get a single total number from these four amt fields for each emp_id using this formula: (v.fit_earn - m.amt - n.amt) + b.amt = ttlamt. So, the record would look like " emp_id v.amt m.amt n.amt b.amt ttlamt ". I've tried all kinds of constructs this morning to get that to work, but I'm not having any luck with it. How do you get this new field, ttlamt, as the result of this formula worked into this script? Actually, when it's all done, all I need in the final result set is emp_id and ttlamt. I just wanted to include the others temporarily so I can ensure the calculation is correct.
2. I'm still confused a little on how to work in the UNION statements and where the JOIN statements go in the UNION script. Reading statement #5 in your reply above appears the way the JOINs would have be done.
All required emp_id's are in v.fit_earn, but, while emp_id's for the other three, m.amt, n.amt, and b.amt, will be included in v.fit_earn, not all emp_id's in v.fit_earn will be included in m.amt, n.amt and/or b.amt. So, yes, I agree, I'll have to use outer joins.
The objective would be, in those cases where emp_id's in m.amt, n.amt and/or b.amt are not found for an emp_id in v.fit_earn, then the value for the these emp_ids not found in m.amt, n.amt and/or b.amt fields would need to be 0 in the result set record, such as:
v.emp_id v.fit_earn m.amt n.amt b.amt ttlamt
--------------------------
12345 35,000 30 3,450 3,300 34,820
23456 24,500 0 1,250 1,100 24,350
34567 30,000 40 0 2,900 32,860
And the ultimate result set for these (after I verify the calculations are correct and remove the clauses that are returning v.fit_earn, m.amt, n.amt and b.amt) is this:
v.emp_id ttlamt
--------------------------
12345 34,820
23456 24,340
34567 32,860
Adding 'amt' solved that problem!
There's still a couple of things I don't fully understand how to do after reading all the expert's postings above. (Please, other experts, feel free to reply along with nav_kum_v):
1. The result set from running the above query returns the correct list of employees, with correct data in emp_id and in all four of the 'amt' fields (m.amt table1_amt, n.amt table2_amt, v.fit_earn table3_amt, and b.amt table4_amt). I did have to change the SELECTs to SELECT DISTINCT in all but the table 'v' query in order to get rid of duplicate records.
What I need to do is to get a single total number from these four amt fields for each emp_id using this formula: (v.fit_earn - m.amt - n.amt) + b.amt = ttlamt. So, the record would look like " emp_id v.amt m.amt n.amt b.amt ttlamt ". I've tried all kinds of constructs this morning to get that to work, but I'm not having any luck with it. How do you get this new field, ttlamt, as the result of this formula worked into this script? Actually, when it's all done, all I need in the final result set is emp_id and ttlamt. I just wanted to include the others temporarily so I can ensure the calculation is correct.
2. I'm still confused a little on how to work in the UNION statements and where the JOIN statements go in the UNION script. Reading statement #5 in your reply above appears the way the JOINs would have be done.
All required emp_id's are in v.fit_earn, but, while emp_id's for the other three, m.amt, n.amt, and b.amt, will be included in v.fit_earn, not all emp_id's in v.fit_earn will be included in m.amt, n.amt and/or b.amt. So, yes, I agree, I'll have to use outer joins.
The objective would be, in those cases where emp_id's in m.amt, n.amt and/or b.amt are not found for an emp_id in v.fit_earn, then the value for the these emp_ids not found in m.amt, n.amt and/or b.amt fields would need to be 0 in the result set record, such as:
v.emp_id v.fit_earn m.amt n.amt b.amt ttlamt
--------------------------
12345 35,000 30 3,450 3,300 34,820
23456 24,500 0 1,250 1,100 24,350
34567 30,000 40 0 2,900 32,860
And the ultimate result set for these (after I verify the calculations are correct and remove the clauses that are returning v.fit_earn, m.amt, n.amt and b.amt) is this:
v.emp_id ttlamt
--------------------------
12345 34,820
23456 24,340
34567 32,860
Can you try the attached for the calculation part. If some records are not returned as part of the equi join, then we can later modify it to have it an outer join.
SELECT m.emp_id,
m.amt table1_amt, --> if we don't need later, we can just remove this column
n.amt table2_amt, --> if we don't need later, we can just remove this column
v.fit_earn table3_amt, --> if we don't need later, we can just remove this column
b.amt table4_amt, --> if we don't need later, we can just remove this column
(v.fit_earn - m.amt - n.amt) + b.amt ttlamt
FROM
(SELECT f.emp_id, sum(m.amt) over (partition by f.emp_id)
FROM lab_tran l, deduction d, pr_chk_hist p, emp_ded e, employee f, emp_ytd_other m
WHERE m.pr_year = 2010
AND (f.alpha_6 = 'NORM' OR f.alpha_6 = 'IMPR')
AND l.ded_num = d.ded_num
AND l.pr_chk_sa_num = p.pr_chk_sa_num
AND m.emp_id = f.emp_id
AND m.comp_num IN (5,40)
AND ((l.ded_num = '553' AND e.pct IS NOT NULL) OR (l.ded_num = '567' AND e.pct IS NOT NULL))
AND ((p.pay_group_num IN ('200','250','325','625','
AND p.prd_end_date = to_char(to_date('&SAL_PR_P
OR (p.prd_end_date = to_char(to_date('&&sal_pr_
AND p.pay_group_num IN ('100','110','300','400','
AND f.emp_id = p.emp_id
GROUP BY f.emp_id, m.amt) m,
(SELECT f.emp_id, n.amt
FROM lab_tran l, deduction d, pr_chk_hist p, emp_ded e, employee f, emp_ytd_reimb n
WHERE n.pr_year = 2010
AND (f.alpha_6 = 'NORM' OR f.alpha_6 = 'IMPR')
AND l.ded_num = d.ded_num
AND l.pr_chk_sa_num = p.pr_chk_sa_num
AND n.emp_id = f.emp_id
AND n.reimb_num <> 20
AND ((l.ded_num = '553' AND e.pct IS NOT NULL) OR (l.ded_num = '567' AND e.pct IS NOT NULL))
AND ((p.pay_group_num IN ('200','250','325','625','
AND p.prd_end_date = to_char(to_date('&SAL_PR_P
OR (p.prd_end_date = to_char(to_date('&&sal_pr_
AND p.pay_group_num IN ('100','110','300','400','
AND f.emp_id = p.emp_id
GROUP BY f.emp_id, n.amt) n,
(SELECT f.emp_id, v.fit_earn
FROM lab_tran l, deduction d, pr_chk_hist p, emp_ded e, employee f, emp_ytd_fed v
WHERE v.pr_year = 2010
AND (f.alpha_6 = 'NORM' OR f.alpha_6 = 'IMPR')
AND l.ded_num = d.ded_num
AND l.pr_chk_sa_num = p.pr_chk_sa_num
AND v.emp_id = f.emp_id
AND ((l.ded_num = '553' AND e.pct IS NOT NULL) OR (l.ded_num = '567' AND e.pct IS NOT NULL))
AND ((p.pay_group_num IN ('200','250','325','625','
AND p.prd_end_date = to_char(to_date('&SAL_PR_P
OR (p.prd_end_date = to_char(to_date('&&sal_pr_
AND p.pay_group_num IN ('100','110','300','400','
AND f.emp_id = p.emp_id
GROUP BY f.emp_id, v.fit_earn) v,
(SELECT f.emp_id, b.amt
FROM lab_tran l, deduction d, pr_chk_hist p, emp_ded e, employee f, emp_ytd_ded b
WHERE b.pr_year = 2010
AND (f.alpha_6 = 'NORM' OR f.alpha_6 = 'IMPR')
AND l.ded_num = d.ded_num
AND l.pr_chk_sa_num = p.pr_chk_sa_num
AND f.emp_id = b.emp_id
AND ((l.ded_num = '553' AND e.pct IS NOT NULL) OR (l.ded_num = '567' AND e.pct IS NOT NULL))
AND ((p.pay_group_num IN ('200','250','325','625','
AND p.prd_end_date = to_char(to_date('&SAL_PR_P
OR (p.prd_end_date = to_char(to_date('&&sal_pr_
AND p.pay_group_num IN ('100','110','300','400','
and b.ded_num IN ('353','354','355','356','
'423','429','437','446','4
'546','553','561','562','5
and d.acr_acct_num in ('2111','2112','2126', '2128','2129')
AND f.emp_id = p.emp_id
GROUP BY f.emp_id, b.amt) b
WHERE m.emp_id = n.emp_id
AND n.emp_id = v.emp_id
AND v.emp_id = b.emp_id;
SELECT m.emp_id,
m.amt table1_amt, --> if we don't need later, we can just remove this column
n.amt table2_amt, --> if we don't need later, we can just remove this column
v.fit_earn table3_amt, --> if we don't need later, we can just remove this column
b.amt table4_amt, --> if we don't need later, we can just remove this column
(v.fit_earn - m.amt - n.amt) + b.amt ttlamt
FROM
(SELECT f.emp_id, sum(m.amt) over (partition by f.emp_id)
FROM lab_tran l, deduction d, pr_chk_hist p, emp_ded e, employee f, emp_ytd_other m
WHERE m.pr_year = 2010
AND (f.alpha_6 = 'NORM' OR f.alpha_6 = 'IMPR')
AND l.ded_num = d.ded_num
AND l.pr_chk_sa_num = p.pr_chk_sa_num
AND m.emp_id = f.emp_id
AND m.comp_num IN (5,40)
AND ((l.ded_num = '553' AND e.pct IS NOT NULL) OR (l.ded_num = '567' AND e.pct IS NOT NULL))
AND ((p.pay_group_num IN ('200','250','325','625','
AND p.prd_end_date = to_char(to_date('&SAL_PR_P
OR (p.prd_end_date = to_char(to_date('&&sal_pr_
AND p.pay_group_num IN ('100','110','300','400','
AND f.emp_id = p.emp_id
GROUP BY f.emp_id, m.amt) m,
(SELECT f.emp_id, n.amt
FROM lab_tran l, deduction d, pr_chk_hist p, emp_ded e, employee f, emp_ytd_reimb n
WHERE n.pr_year = 2010
AND (f.alpha_6 = 'NORM' OR f.alpha_6 = 'IMPR')
AND l.ded_num = d.ded_num
AND l.pr_chk_sa_num = p.pr_chk_sa_num
AND n.emp_id = f.emp_id
AND n.reimb_num <> 20
AND ((l.ded_num = '553' AND e.pct IS NOT NULL) OR (l.ded_num = '567' AND e.pct IS NOT NULL))
AND ((p.pay_group_num IN ('200','250','325','625','
AND p.prd_end_date = to_char(to_date('&SAL_PR_P
OR (p.prd_end_date = to_char(to_date('&&sal_pr_
AND p.pay_group_num IN ('100','110','300','400','
AND f.emp_id = p.emp_id
GROUP BY f.emp_id, n.amt) n,
(SELECT f.emp_id, v.fit_earn
FROM lab_tran l, deduction d, pr_chk_hist p, emp_ded e, employee f, emp_ytd_fed v
WHERE v.pr_year = 2010
AND (f.alpha_6 = 'NORM' OR f.alpha_6 = 'IMPR')
AND l.ded_num = d.ded_num
AND l.pr_chk_sa_num = p.pr_chk_sa_num
AND v.emp_id = f.emp_id
AND ((l.ded_num = '553' AND e.pct IS NOT NULL) OR (l.ded_num = '567' AND e.pct IS NOT NULL))
AND ((p.pay_group_num IN ('200','250','325','625','
AND p.prd_end_date = to_char(to_date('&SAL_PR_P
OR (p.prd_end_date = to_char(to_date('&&sal_pr_
AND p.pay_group_num IN ('100','110','300','400','
AND f.emp_id = p.emp_id
GROUP BY f.emp_id, v.fit_earn) v,
(SELECT f.emp_id, b.amt
FROM lab_tran l, deduction d, pr_chk_hist p, emp_ded e, employee f, emp_ytd_ded b
WHERE b.pr_year = 2010
AND (f.alpha_6 = 'NORM' OR f.alpha_6 = 'IMPR')
AND l.ded_num = d.ded_num
AND l.pr_chk_sa_num = p.pr_chk_sa_num
AND f.emp_id = b.emp_id
AND ((l.ded_num = '553' AND e.pct IS NOT NULL) OR (l.ded_num = '567' AND e.pct IS NOT NULL))
AND ((p.pay_group_num IN ('200','250','325','625','
AND p.prd_end_date = to_char(to_date('&SAL_PR_P
OR (p.prd_end_date = to_char(to_date('&&sal_pr_
AND p.pay_group_num IN ('100','110','300','400','
and b.ded_num IN ('353','354','355','356','
'423','429','437','446','4
'546','553','561','562','5
and d.acr_acct_num in ('2111','2112','2126', '2128','2129')
AND f.emp_id = p.emp_id
GROUP BY f.emp_id, b.amt) b
WHERE m.emp_id = n.emp_id
AND n.emp_id = v.emp_id
AND v.emp_id = b.emp_id;
Premium Content
You need an Expert Office subscription to comment.Start Free Trial