RE: SQL Query & SQL EXISTS

[Vanya Jovic <jovic@xxxxxxxxxxxx>]

Hi ppl.

here is an oportunity to answer both questions within one example:


SELECT * FROM file x WHERE EXISTS 
        (SELECT SrcLib, SrcFile, SrcMbr FROM file 
        WHERE SrcLib = x.SrcLib AND SrcFile = x.SrcFile AND SrcMbr = x.SrcMbr
        GROUP BY SrcLib, SrcFile, SrcMbr 
        HAVING count(*) > 1)

that can be modified as:

SELECT * FROM file x WHERE EXISTS 
        (SELECT SrcLib, SrcFile, SrcMbr FROM file 
        GROUP BY SrcLib, SrcFile, SrcMbr 
        HAVING count(*) > 1 AND
        SrcLib = x.SrcLib AND SrcFile = x.SrcFile AND SrcMbr = x.SrcMbr)

Few remarks:

1. Syntax:
        This can be written in a few ways using keywords OUTER JOIN, LEFT
JOIN etc. I usualy use this one because it shows logic itself. I don't
think there would be performance diference among various notations; it's
up to query manager and query optimizer to chose how to perform the query.
Most often it would be run as joined query.

2. Semantics:
        If there was only one field to connect query to subquery, then
keyword IN would be more appropriate than EXISTS. But SQL still don't
alow vectors to be compared, so we are stuck with EXISTS and row to row
comparison.

3. Performance:
        As you noticed, query like this doesn't perform too well. But,
that goes with the teritory. Nice and simple notation doesn't mean simple
task for query manager. If you wrote RPG or COBOL program, you could get
better performance, but think about time and efort to write whole thing
yourself. There are ways to improve query performance, and they were
discussed here few times, so I wouldn't repeat it. If performance is
really an issue, I would suggest modifying subquery and running it into
temp file, then running main query agaist that tempfile.

Hope this helps,

Vanja Jovic

Canada
(just a few more months, and spring will come)

> > 
> > ObjLib, ObjName, ObjAttr, SrcLib, SrcFile, SrcMbr
> > 
> > I have cases like 1) same source but created as two different objects
> > and the
> > normal src-obj relationship. I want to list out only the records that
> > hae case
> > 1). i.e, different objects created from same src.
> > 
> > Not able to break this.. Any help/suggestions... TIA.
> > 
> > Rgds
> > Vg
> > +---
> > | 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
> > +---
> +---
> | 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
> +---
> 


+---
| 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
+---