First of all, sorry for the post. I MUST have deleted the importantjoin
criteria so it didn't work correctly.
Although now I am glad I asked, because maybe there is a betterapproach.
I am trying to do the following:
1) Reduce records to one record for each set of key values (should Iuse
DISTINCT instead?) - for a small subset of key values
2) join file back to itself to extract all recs with those selectedkey
values (if I don't have the GROUP BY, the join would return multiplerecs
for each occurance which I don't want)
Here is the SQL that works well - is there a better way without theinner
& outer select & EXISTS clause?
Note: the specific select values are for test cases only - which willbe
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'))
(select ASHPNBR, ABLDNGD, AMSCHRG from MISCCHG b
where a.ASHPNBR = b.ashpnbr and a.ABLDNGD = b.ABLDNGD
(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 )
This mailing list archive is Copyright 1997-2014 by MIDRANGE dot 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 here. If you have questions about this, please contact