I think I see it. Unless I'm missing something, the statement below is the same as (note: I removed the GROUP BY clause in the EXISTS test because it doesn't contribute anything -- I think we're all agreed on that ;-):
select * from miscchg A
where (a.AMSCHGC in ('S','B') or a.AMSCHGA in ('S','B'))
and a.ASHPNBR = 'BN460619' and a.ABLDNGD = '20120817'
and exists
(select ASHPNBR, ABLDNGD, AMSCHRG from MISCCHG b
where a.ASHPNBR = b.ashpnbr and a.ABLDNGD = b.ABLDNGD)
order by ASHPNBR,ABLDNGD,APLSQNB,AMSCHRG
The tests for ASHPNBR and ABLDNGD can happen anywhere, since you're joining on those two fields, and it seems easier (and I think more efficient) to make them as early as possible. But after moving them out of the EXISTS clause, the EXISTS clause really
does do nothing (or, actually, adds nothing -- it's always testing to see if theres at least one row with ASHPNBR='BN460619' and ABLDNGD='20120817', and it's going to get the same answer every time, because either there is or there isn't).
If you leave the tests inside the EXISTS clause, then removing it will return all rows that satisfy the AMSCHGC and AMSCHGA tests, which it sounds like a lot do. But if you take them out and remove the EXISTS clause, I think you'll get what you're
looking for:
select * from miscchg A
where (a.AMSCHGC in ('S','B') or a.AMSCHGA in ('S','B'))
and a.ASHPNBR = 'BN460619' and a.ABLDNGD = '20120817'
order by ASHPNBR,ABLDNGD,APLSQNB,AMSCHRG
Hope this helps,
Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx> writes:
OK please explain:
The following returns the 6 rows that I want and works perfectly.
select * from miscchg A
where (a.AMSCHGC in ('S','B') or a.AMSCHGA in ('S','B'))
and
exists
(select ASHPNBR, ABLDNGD, AMSCHRG from MISCCHG b
where a.ASHPNBR = b.ashpnbr and a.ABLDNGD = b.ABLDNGD
and
ashPNBR = 'BN460619' and ABLDNGD = '20120817'
group by ASHPNBR,ABLDNGD, AMSCHRG )
order by ASHPNBR,ABLDNGD,APLSQNB,AMSCHRG
I removed the GROUP BY, you are correct, same result.
If I remove the entire EXISTS clause, millions of rows are returned.
How can what you and others are stating be accurate if the result set with the EXISTS returns 6 rows, and without EXISTS returns millions?
Are you suggesting that the EXISTS "does nothing to change the final result" ? But yet it does change the final result.
What am I missing?
Mike Naughton
Senior Programmer/Analyst
Judd Wire, Inc.
124 Turnpike Road
Turners Falls, MA 01376
413-676-3144
Internal: x 444
mnaughton@xxxxxxxxxxxx
****************************************
NOTICE: This e-mail and any files transmitted with it are confidential and solely for the use of the intended recipient. If you are not the intended recipient or the person responsible for delivering to the intended recipient, be advised that any use is
strictly prohibited. If you have received this e-mail in error, please notify us immediately by replying to it and then delete it from your computer.
midrange.com
