Fyi this was a TEST case. For PROD, I am going to take a small set of distinct keys from a journal file, and join to the orig file.
Hope that makes sense.
-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Charles Wilt
Sent: Friday, December 07, 2012 9:58 AM
To: Midrange Systems Technical Discussion
Subject: Re: SQL help with EXISTS
Chuck,
Given that I think mine was the other, I wanted to say I never meant to
imply that the end results would be distinct...
I was only giving him what he asked for, a small distinct set of keys
joined back to the original table...
Granted, now that I think a little more about it, the request doesn't make
much sense...
This would seem to give him what he wants...
select (distinct?) *
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')
I've used statements similar to my original post in situations like so:
with tbl (select key, count(*) from mytable group by key having count(*) >
2)
select * from mytable join tbl using(key)
ie. figure out the keys that match some criteria. Then get a list of all
records with those keys.
But since the OP's request has hard coded keys, there's no need for the CTE.
Charles
On Fri, Dec 7, 2012 at 10:34 AM, CRPence <CRPbottle@xxxxxxxxx> wrote:
This is the second response offered in this thread, implying that a
result set that is made distinct across some subset of columns which is
then joined to the original full\non-distinct TABLE [as result set] on
those same columns [although the quoted example removed one column for
join-on] somehow will also become distinct.?
midrange.com
