|
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 alarge file (ASHPNBR = 'BN123456' ).
only a few records - now it selects ALL values of ASHPNBR.
Then I changed a paren or something small and I cant get it to select
values).
The sub-select works great and only selects a few records.
When I add the outer select, the entire file is selected (all ASHPNBR
Do I have to explicitly state the join? Or does "EXISTS" make assumption
How does EXISTS know which keys to join between the inner & outer query?
about which fields to join?
Skyscan service.
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
For more information please visit http://www.symanteccloud.com
______________________________________________________________________
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.
________________________________________________________________________
This inbound email has been scanned for all viruses by the MessageLabs
SkyScan
service.
________________________________________________________________________
______________________________________________________________________
This outbound email has been scanned for all viruses by the MessageLabs
Skyscan service.
For more information please visit http://www.symanteccloud.com
______________________________________________________________________
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.
As an Amazon Associate we earn from qualifying purchases.
This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].
Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.