Just goes to show that a problem like this will generate the same solution
many times over.My version of Chuck's XSELECT is called BSELECT.
e.g.
GET-LIST MYLIST (list of customer numbers)
BSELECT INVOICES CUST.NO (cust.no is an indexed field in INVOICES file)
Once you get used to it, it is indispensable. (I have a D3 version too...)
/Scott Ballinger
Pareto Corporation
Edmonds WA USA
206 713 6006
BSELECT
* select records with indexed field matching a select list
* 09-12-06 asb: UV version
* usage: BSELECT target-file indexed-field
**$OPTIONS DEFAULT
TOT = @SELECTED
IF TOT ELSE STOPM "this process requires an active select list"
PROMPT ""
BUF = TRIM(@SENTENCE)
TARGETFILE = ""
FIELDNAME = ""
IF FIELD(BUF," ",1) EQ "RUN" THEN
BUF = OCONV(BUF,"G2 99")
END
TARGETFILE = FIELD(BUF," ",2)
FIELDNAME = FIELD(BUF," ",3)
IF TARGETFILE EQ "" THEN
PRINT "Target file to select against: ":
INPUT TARGETFILE
CALL INPUT.TEST(TARGETFILE) ;* test for standard quit type responses
IF TARGETFILE EQ "" OR TARGETFILE EQ "QUIT" THEN STOP
END
OPEN TARGETFILE TO FV ELSE STOP 201,TARGETFILE
IF FIELDNAME EQ "" THEN
PRINT "Indexed field on ":TARGETFILE:": ":
INPUT FIELDNAME
CALL INPUT.TEST(FIELDNAME)
IF FIELDNAME EQ "" OR FIELDNAME EQ "QUIT" THEN STOP
END
C = 0 ;* original list ids processed
C1 = 0 ;* original list ids found in target file via fieldname btree
C2 = 0 ;* new ids to target file returned
T = TIME()
D = DATE()
CLR = @(0):@(-4)
LIST = ""
10 *
READNEXT ID ELSE GO 999
BSCAN CID,REC FROM FV,ID USING FIELDNAME THEN
IF CID EQ ID THEN
LIST<-1> = REC
C1 += 1
C2 += DCOUNT(REC,@AM)
END
END
C += 1
IF MOD(C,1000) EQ 0 THEN
CALL COUNTER.SUB(C,T,D,TOT,TXT) ;* calc percent done & estimated time
until finished
PRINT CLR:C:" ":C1:" ":C2:TXT:
END
GO 10
999 *
CALL FINAL.COUNT(C,T,D) ;* nnn records processed in yyy seconds = zzz.z per
second.
PRINT C1:" ids found in '":FIELDNAME:"' field on '":TARGETFILE:"' file."
PRINT C2:" '":TARGETFILE:"' records selected."
IF C2 EQ 0 THEN
STOP 401
END ELSE
OPEN "&SAVEDLISTS&" TO SL ELSE STOP 201,"&SAVEDLISTS&"
LID = "BSELECT":SYSTEM(22)
WRITE LIST ON SL,LID
CHAIN "GET-LIST ":LID
END
On Fri, Jan 30, 2009 at 2:24 PM, Charles Stevenson <[email protected]
> wrote:
> I'm entering the fray late here, but I think I have a good, fast,
> general solution, provided you're willing to index a field. I should
> probably post it in U2UG's wiki the source code to the XSELECT utility
> below.
>
> Using your example, SELECT CUSTOMERS WITH CLIENTNO = "1""2""3""4"..... :
> 1. Maintain an index on COSTOMERS CLIENTNO.
> 2. Create a saved or active select list of the 300 client numbers.
> Others have suggested how via QSELECT, FORM.LIST etc.
> 3. Use XSELECT utility verb that uses basic SELECTINDEX under the covers to
> build a select list of all CUSTOMERS keys that are indexed by those 300
> client numbers.
>
> >GET.LIST A.BUNCH.OF.CLIENTS
> 300 records selected.
> >>XSELECT CUSTOMERS CLIENTNO
> 12,345 records selected <--- 12,345 CUSTOMER IDs
> >>LIST CUSTOMERS REQUIRE.SELECT ... ( or however you generate the
> report.)
>
>
> The nut of XSELECT pretty much does this:
> outlist = ''
> loop while readnext idxval
> selectindex idx, idxval from fvar to 0
> readlist temp from 0 then outlist<-1> = temp
> repeat
> but there's logic for oconv, duplicates, saving/getting lists, existing
> with
> active select list, error handling, etc. that you'll need if you roll your
> own.
> Chuck Stevenson
>
>
>
> On Thu, Jan 29, 2009 at 11:43 AM, Bill Haskett wrote:
>
> > We have a report writer that allows users to select the client they
> want
> > to
> > print any report for. This subroutine builds a list of clients
> available
> > to
> > any particular user and uses this list to either compare against the
> > clients
> > entered or of all clients are selected then the entire list is used.
> > When
> > this list is placed in an ECL query and executed, e.g....
> > SELECT CUSTOMERS WITH CLIENTNO = "1""2""3""4".....
> > ...all has been fine for years. We picked up a new customer who has
> over
> > 300 clients. When a user tries to run a report for all clients
> > accessible
> > by them the query aborts with...
> > WITH condition stack overflow
> -------
> u2-users mailing list
> [email protected]
> To unsubscribe please visit http://listserver.u2ug.org/
-------
u2-users mailing list
[email protected]
To unsubscribe please visit http://listserver.u2ug.org/
