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 )

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.