You should be able to use DISTINCT instead of GROUP BY. Also using a common
table expression may work better.
-- reduce MISCCHG recs to one per key
insert into MISCCHG2
with mysubset as (
select distinct ASHPNBR, ABLDNGD, AMSCHRG
from MISCCHG
where (ASHPNBR = 'BN460619' and ABLDNGD = '20120817'
or ASHPNBR = 'WASH223305' and ABLDNGD = '20121015'
or ASHPNBR = '130000' and ABLDNGD = '20120803'
or ASHPNBR = '2162821' and ABLDNGD = '20120801'
or ASHPNBR = 'BN460619' and ABLDNGD = '20120817'
or ASHPNBR = 'BNSF472050' and ABLDNGD = '20120818'
or ASHPNBR = 'CC95585B' and ABLDNGD = '20120711'
or ASHPNBR = '2297051' and ABLDNGD = '20120831')
)
select a.* from miscchg A
join mysubset b
on a.ASHPNBR = b.ashpnbr and a.ABLDNGD = b.ABLDNGD
where (a.AMSCHGC in ('S','B') or a.AMSCHGA in ('S','B'))
Have fun!
-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Stone, Joel
Sent: Thursday, December 06, 2012 11:52 AM
To: Midrange Systems Technical Discussion
Subject: RE: SQL help with EXISTS
First of all, sorry for the post. I MUST have deleted the important join
criteria so it didn't work correctly.
Although now I am glad I asked, because maybe there is a better approach.
I am trying to do the following:
1) Reduce records to one record for each set of key values (should I use
DISTINCT instead?) - for a small subset of key values
2) join file back to itself to extract all recs with those selected key
values (if I don't have the GROUP BY, the join would return multiple recs
for each occurance which I don't want)
Here is the SQL that works well - is there a better way without the inner &
outer select & EXISTS clause?
Note: the specific select values are for test cases only - which will be
replaced by other criteria in prod
Thanks in advance!
-- reduce MISCCHG recs to one per key
insert into MISCCHG2
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'
or ASHPNBR = 'WASH223305' and ABLDNGD = '20121015'
or ASHPNBR = '130000' and ABLDNGD = '20120803'
or ASHPNBR = '2162821' and ABLDNGD = '20120801'
or ASHPNBR = 'BN460619' and ABLDNGD = '20120817'
or ASHPNBR = 'BNSF472050' and ABLDNGD = '20120818'
or ASHPNBR = 'CC95585B' and ABLDNGD = '20120711'
or ASHPNBR = '2297051' and ABLDNGD = '20120831')
group by ASHPNBR,ABLDNGD, AMSCHRG )
-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Vernon Hamberg
Sent: Thursday, December 06, 2012 10:42 AM
To: Midrange Systems Technical Discussion
Subject: Re: SQL help with EXISTS
Hi Joel
Exists returns "true" if there are any records in the inner SELECT at all.
In this case, it seems there is at least one record there, so the WHERE is
always satisfied, hence, you get all the records from MISCCHG.
Usually you would not do an EXISTS against the same table in the outer
SELECT. What is the point? You could use a simple WHERE clause to do that?
You use it best when you have some correlation - take a value from the outer
SELECT for the comparison within the inner SELECT - correlation is the term
you can look up. And this outer reference would be used as a test value for
a column in the table in the inner SELECT.
Your inner SELECT typically would also never have GROUP BY - why would you,
there is no selectivity done in a GROUP BY without a HAVING clause as well.
HTH
Vern
On 12/6/2012 10:23 AM, Stone, Joel wrote:
I am pretty sure that the following selected only a few records from a
large file (ASHPNBR = 'BN123456' ).
Then I changed a paren or something small and I cant get it to select only
a few records - now it selects ALL values of ASHPNBR.
The sub-select works great and only selects a few records.
When I add the outer select, the entire file is selected (all ASHPNBR
values).
How does EXISTS know which keys to join between the inner & outer query?
Do I have to explicitly state the join? Or does "EXISTS" make assumption
about which fields to join?
Thanks
select * from miscchg A
where
exists
(select ASHPNBR, ABLDNGD, AMSCHRG from MISCCHG
where
ASHPNBR = 'BN123456'
group by ASHPNBR,ABLDNGD, AMSCHRG )
midrange.com
