Re: SQL Query

[VENU YAMAJALA <venu_yamajala@xxxxxxxxxxxx>]

Well, "Select srclib, srcfil, srcmbr from listfile group by srclib, srcfil,
srcmbr having count(*)>1" resulted in 8 hits. Now  when we have the sub-query
as -

"Select  * from listfile where srclib||srcfil||srcmbr in(Select
srclib||srcfil||srcmbr from listfile group by srclib, srcfil, srcmbr having
count(*) > 1)"

Suppose the listfile contains 100,000 records, the above query is reading thru
each and every row and trying to match it with the subquery ( 8 hits!!). I
think this is where it is taking time in looping thru all of the 100,000
records. Though this is a single query solution, it is taking lot of time.

As someone suggested - I wrote multiple queries and multiple outfiles. This
also gave me same result but much faster (ofcourse.. not a single query
solution!!).

Is there anyway, we can make the first query faster?? TIA.

Rgds
Venu
+---
| This is the Midrange System Mailing List!
| To submit a new message, send your mail to MIDRANGE-L@midrange.com.
| To subscribe to this list send email to MIDRANGE-L-SUB@midrange.com.
| To unsubscribe from this list send email to MIDRANGE-L-UNSUB@midrange.com.
| Questions should be directed to the list owner/operator: david@midrange.com
+---