RE: ** SQL Insert Question **

[Ian Scotson <IanS@xxxxxxxxxxxx>]

Thks for the info,


1st screen came up and i asked for "b"ottom record.


          4     USA         47   20,020,101   BWU457
          4     USA         48   20,020,101   BWU458
                                                                     Mo
F3=Exit      F12=Cancel      F19=Left      F20=Right      F21=Split
Query running. 101 records selected, 48379 processed.

Counted to over 80,000 records before it stopped.
I changed the "select *" to "select count(*) and after ignoring
the qry limiter (300 secs), it did come back with 690 records
 - my expected figure.


I thought it might be clever somehow with indexes, but it seems
that with these temp tables and the IN it ends up sequential search.
VERY expensive way to do it.


Makes record level access look pretty good.




Ian.


-----Original Message-----
From: Finucci Domenico [mailto:Domenico.Finucci@Fiditalia.it]
Sent: Friday, 10 May 2002 17:11
To: 'midrange-l@midrange.com'
Subject: R: ** SQL Insert Question **


==> "I tried one way ( using IN ) and ended up with >60000 records
( and still going..) using."
Shure ?
If the statement
        SELECT  distinct zcdke1
        FROM IAN.CUSPD00F a, pilotshr.ARTMS00F b
                        WHERE    BRECTY = ZRECTY AND
                              BCDKE1 = ZCDKE1
works as you expect, i think that
        INSERT INTO IAN.CUSPD00F2
        select * from IAN.CUSPD00F Where bcdke1 in
(        SELECT  distinct zcdke1
        FROM IAN.CUSPD00F a, pilotshr.ARTMS00F b
                        WHERE    BRECTY = ZRECTY AND
                              BCDKE1 = ZCDKE1 )
should do the trick.
Sincerely
Domenico
_______________________________________________
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@midrange.com
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/cgi-bin/listinfo/midrange-l
or email: MIDRANGE-L-request@midrange.com
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.