MIDRANGE dot COM Mailing List Archive



Home » MIDRANGE-L » December 2012

RE: SQL help with EXISTS



fixed

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 )



If I understand you correctly you are trying to select only 1 entry from
MISCCHG where the key can have multiples.



Try something like this if you don't care which record you get from
MISCCHG for each key and you need multiple fields.



insert into MISCCHG2



/* Pull out the "First" entry for each key from t1

* AND insert it into MISCCHG2.

*/

SELECT FLD1, FLD2, FLD3, FLD4, ..., LASTFIELD

FROM (

/* Run through all the records in MISCCHG and pull out the

* records that match to something in the "EXISTS" subselect.

* Then number each instance found 1,2,3 using ROW_NUMBER()

*/

SELECT ROW_NUMBER() OVER(PARTITION BY ASHPNBR,ABLDNGD

ORDER BY ASHPNBR,ABLDNGD, AMSCHRG) AS RSEQ

, FLD1, FLD2, FLD3, FLD4,...,LASTFIELD

FROM miscchg A

where (a.AMSCHGC in ('S','B') or a.AMSCHGA in ('S','B'))

and exists

(select 1 from MISCCHG b

/* See if record a.ASHPNBR, a.ABLDNGD is in this

* subselect and return true if its found

*/

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')

)

) T1

WHERE RSEQ = 1



You will need to key each field you want to add to MISCCHG2 in place of
FLD1,FLD2...,LASTFIELD



If you don't need multiple fields and only need the key fields then you
can just use a simple group by or distinct over the fields you want to
return.





Chris Hiebert
Programmer/Analyst

Disclaimer: Any views or opinions presented are solely those of the
author and do not necessarily represent those of the company.








Return to Archive home page | Return to MIDRANGE.COM home page

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