RE: SQL help with EXISTS

["Richard Casey" <casey_r@xxxxxxxxxxxxxxxx>]

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         )