Re: SQL help with EXISTS

with tbl 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 tbl using(ASHPNBR, ABLDNGD, AMSCHRG)


if at v6.1+, IBM fixed the USING clause so you'll need
select ASHPNBR, ABLDNGD, AMSCHRG, a.*
from MISCCHG A
join tbl using(ASHPNBR, ABLDNGD, AMSCHRG)

Or you you can use the JOIN ON
select a.*
from MISCCHG A
join tbl B on a.ASHPNBR = b.ashpnbr and a.ABLDNGD = b.ABLDNGD

Charles



On Thu, Dec 6, 2012 at 11:51 AM, Stone, Joel <Joel.Stone@xxxxxxxxxx> wrote:

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
______________________________________________________________________

--
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.