We help IT Professionals succeed at work.

PL/SQL - Combine Results of Several Select Queries

Glenn Stearns
on
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?
Comment
Watch Question

Commented:
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
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
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?
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
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

Commented:
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

Commented:
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
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
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.

Commented:
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
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
Got you.  I understand now.  I probably wouldn't do it that way but it should work.
Naveen KumarProduction Manager / Application Support Manager

Commented:
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
Ora_TechieDatabase Administrator

Commented:
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

Open in new window

Glenn StearnsAnalyst

Author

Commented:
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?
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
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.
Glenn StearnsAnalyst

Author

Commented:
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','750')
AND p.prd_end_date = to_char(to_date('&SAL_PR_PD_END_DATE','YYYY/MM/DD')-7,'YYYY/MM/DD'))
OR (p.prd_end_date = to_char(to_date('&&sal_pr_pd_end_date','YYYY/MM/DD'),'YYYY/MM/DD')
AND p.pay_group_num IN ('100','110','300','400','410','600','610','700','710')))
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','750')
AND p.prd_end_date = to_char(to_date('&SAL_PR_PD_END_DATE','YYYY/MM/DD')-7,'YYYY/MM/DD'))
OR (p.prd_end_date = to_char(to_date('&&sal_pr_pd_end_date','YYYY/MM/DD'),'YYYY/MM/DD')
AND p.pay_group_num IN ('100','110','300','400','410','600','610','700','710')))
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','357','358','359','363','412','413','417','418','420','421','423','429','437','446','461','462','473',
'474','512','513','517','518','520','521','523','529','537','546','553','561','562','567','573','574')
AND a.pr_year = to_number(to_char(to_date('&sal_pr_pd_end_date','YYYY/MM/DD'),'YYYY'))
AND ((p.pay_group_num IN ('200','250','325','625','750')
AND p.prd_end_date = to_char(to_date('&SAL_PR_PD_END_DATE','YYYY/MM/DD')-7,'YYYY/MM/DD'))
OR (p.prd_end_date = to_char(to_date('&&sal_pr_pd_end_date','YYYY/MM/DD'),'YYYY/MM/DD')
AND p.pay_group_num IN ('100','110','300','400','410','600','610','700','710')))
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

------------------------------------------------------------------------------------------------
 
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
>>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.
Glenn StearnsAnalyst

Author

Commented:
OK, will give it a try.
Glenn StearnsAnalyst

Author

Commented:
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','750')
AND p.prd_end_date = to_char(to_date('&SAL_PR_PD_END_DATE','YYYY/MM/DD')-7,'YYYY/MM/DD'))
OR (p.prd_end_date = to_char(to_date('&&sal_pr_pd_end_date','YYYY/MM/DD'),'YYYY/MM/DD')
AND p.pay_group_num IN ('100','110','300','400','410','600','610','700','710')))
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','750')
AND p.prd_end_date = to_char(to_date('&SAL_PR_PD_END_DATE','YYYY/MM/DD')-7,'YYYY/MM/DD'))
OR (p.prd_end_date = to_char(to_date('&&sal_pr_pd_end_date','YYYY/MM/DD'),'YYYY/MM/DD')
AND p.pay_group_num IN ('100','110','300','400','410','600','610','700','710')))
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','750')
AND p.prd_end_date = to_char(to_date('&SAL_PR_PD_END_DATE','YYYY/MM/DD')-7,'YYYY/MM/DD'))
OR (p.prd_end_date = to_char(to_date('&&sal_pr_pd_end_date','YYYY/MM/DD'),'YYYY/MM/DD')
AND p.pay_group_num IN ('100','110','300','400','410','600','610','700','710')))
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','750')
AND p.prd_end_date = to_char(to_date('&SAL_PR_PD_END_DATE','YYYY/MM/DD')-7,'YYYY/MM/DD'))
OR (p.prd_end_date = to_char(to_date('&&sal_pr_pd_end_date','YYYY/MM/DD'),'YYYY/MM/DD')
AND p.pay_group_num IN ('100','110','300','400','410','600','610','700','710')))
and b.ded_num IN ('353','354','355','356','357','358','359','363','412','413','417','418','420','421',
'423','429','437','446','461','462','473','474','512','513','517','518','520','521','523','529','537',
'546','553','561','562','567','573','574')
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;
Naveen KumarProduction Manager / Application Support Manager

Commented:
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
Glenn StearnsAnalyst

Author

Commented:
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
Production Manager / Application Support Manager
Commented:
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','750')
AND p.prd_end_date = to_char(to_date('&SAL_PR_PD_END_DATE','YYYY/MM/DD')-7,'YYYY/MM/DD'))
OR (p.prd_end_date = to_char(to_date('&&sal_pr_pd_end_date','YYYY/MM/DD'),'YYYY/MM/DD')
AND p.pay_group_num IN ('100','110','300','400','410','600','610','700','710')))
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','750')
AND p.prd_end_date = to_char(to_date('&SAL_PR_PD_END_DATE','YYYY/MM/DD')-7,'YYYY/MM/DD'))
OR (p.prd_end_date = to_char(to_date('&&sal_pr_pd_end_date','YYYY/MM/DD'),'YYYY/MM/DD')
AND p.pay_group_num IN ('100','110','300','400','410','600','610','700','710')))
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','750')
AND p.prd_end_date = to_char(to_date('&SAL_PR_PD_END_DATE','YYYY/MM/DD')-7,'YYYY/MM/DD'))
OR (p.prd_end_date = to_char(to_date('&&sal_pr_pd_end_date','YYYY/MM/DD'),'YYYY/MM/DD')
AND p.pay_group_num IN ('100','110','300','400','410','600','610','700','710')))
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','750')
AND p.prd_end_date = to_char(to_date('&SAL_PR_PD_END_DATE','YYYY/MM/DD')-7,'YYYY/MM/DD'))
OR (p.prd_end_date = to_char(to_date('&&sal_pr_pd_end_date','YYYY/MM/DD'),'YYYY/MM/DD')
AND p.pay_group_num IN ('100','110','300','400','410','600','610','700','710')))
and b.ded_num IN ('353','354','355','356','357','358','359','363','412','413','417','418','420','421',
'423','429','437','446','461','462','473','474','512','513','517','518','520','521','523','529','537',
'546','553','561','562','567','573','574')
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;
Glenn StearnsAnalyst

Author

Commented:
Thanks nav_kum-v...I got it to work.
Glenn StearnsAnalyst

Author

Commented:
Thanks!