| query [message #373312] |
Wed, 11 April 2001 04:48  |
Jey
Messages: 4
Registered: February 2001
|
Junior Member |
|
|
I have two tables like
process, saa_output.
In process i have a columns like employee_no, process_yrmth, saa_amt
In saa_output i have a columns like co_emp_no, proc_yr_mth, saa_amount
i have a query like
select sum(saa_amount) from saa_output s where s.proc_yr_mth = '200010'
group by co_emp_no having sum(saa_amount)
<> (select saa_amt from process where
process_yrmth = '200010' and rtrim(s.co_emp_no) = rtrim(employee_no))
i want to know the saa_amount not equal to process table saa_amt .
is this query is correct? . but i couldnt get the answer.
jey
|
|
|
|
| Re: query [message #373313 is a reply to message #373312] |
Wed, 11 April 2001 06:04  |
Neal Hawman
Messages: 14
Registered: April 2001
|
Junior Member |
|
|
I think it will work, but the more common method would be to join the tables in the main query. I suspect this will be a lot more efficient.
select s.co_emp_no, sum(s.saa_amount), p.saa_amt
from saa_output s, process p
where s.proc_yr_mth = '200010'
and p.proc_yr_mth = '200010'
and s.co_emp_no = p.employee_no
group by s.co_emp_no, p.saa_amt
having sum(saa_amount) <> p.saa_amt
If at all possible, I would avoid using RTRIM on the employee_no fields as this will suppress any index you have on those columns. It would be best to do the RTRIM when you load the data.
Neal
|
|
|
|
| Re: query [message #373342 is a reply to message #373312] |
Wed, 11 April 2001 19:22 |
George
Messages: 68
Registered: April 2001
|
Member |
|
|
HI,
I think the problem is with your subquery.
Select both the tables in the subquery like -
(select saa_amt from process p, saa_output s where
p.process_yrmth = '200010' and rtrim(s.co_emp_no) = rtrim(p.employee_no)).
Good luck.
GD
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
