RE: SQL Query

["David Morris" <dmorris@xxxxxxxxxxxxx>]

Venu,

I missed your first post but here is a statement I have used.  It is cut 
from RPG source so it may not format very well.  Basically I merge 
source and object descriptions which are created using the dspobjd 
and dspfd commands.  Unmatched records will contain null values 
as indicated by a null indicator array.  The fields to select are in the 
program variable SelFld.

This first does a left join to get all records from the object description 
output file with their corresponding source if it exists.  A union is used to 
append all of the un-matched source back to this file.  The order by 
is then appended.

I have found this runs fairly fast.  About double the time it takes to run 
a strait Select over there files.

C                   EVAL      ObjMbrStm   = '+
C                                           Select ' +
C                                           %SUBST(SelFld:1:LenSelFld) +
C                                           ' +
C                                           From VRFOBJLST +
C                                           Left Join VRFSRCLST +
C                                            On ODSRCL = MBLIB +
C                                            And ODSRCF = MBFILE +
C                                            And ODSRCM = MBNAME +
C                                           Where +
C                                            Substr(ODOBNM,1,1) *= +
C                                            ''Q'' +
C                                           Union All +
C                                           Select ' +
C                                           %SUBST(SelFld:1:LenSelFld) +
C                                           ' +
C                                           From VRFSRCLST +
C                                           Exception Join VRFOBJLST +
C                                            On MBLIB = ODSRCL +
C                                            And MBFILE = ODSRCF +
C                                            And MBNAME = ODSRCM +
C                                           Where +
C                                            MBNAME *= ''          '''
...
C                   EVAL      ObjMbrStm   = %TRIMR(ObjMbrStm) + ' +
C                                           Order by ' +
C                                           %SUBST(OrdB:1:LenOrdB)

David Morris

>>> VENU YAMAJALA  <venu_yamajala@goodyear.com> 03/08/99 02:29PM >>>
 SELECT * FROM library/samesrc WHERE yosrcl||yosrcf||yosrcm
 IN(select yosrcl||yosrcf||yosrcm from library/samesrc group by
 yosrcl, yosrcf, yosrcm having count(*)>1) GROUP BY yosrcl, yosrcf,
 yosrcm, yolbnm, yoobnm, yoobat ORDER BY yosrcl, yosrcf, yosrcm

I got the results. Though not so well formatted., but I can manage and get a
report. The only difficulty is that this
query took a very long time to run!! Thx to all who helped me.

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