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 )
______________________________________________________________________
This outbound email has been scanned for all viruses by the MessageLabs Skyscan service.
For more information please visit http://www.symanteccloud.com
______________________________________________________________________
midrange.com
