LSELECT
It is easy to select data from a file when a field equals a known value. e.g.
>LIST CUSTOMER WITH CUSTNO = "12345"
It is also easy to select data from a file when a field equals one of a number of known value. e.g.
>LIST CUSTOMER WITH CUSTNO = "12345""12346""12347""12348"
The query processor is very powerful and offers many ways to easily accommodate queries. Using selects before processing a query is a standard method to filter data. e.g.
>SELECT CUSTOMER WITH CUSTTYPE = "1"
14 items selected.
>>SELECT CUSTOMER WITH CUSTNO = "12345"
3 items selected.
>>LIST CUSTOMER
On occasion, we have a list of data we'd like to compare a select to to see if any data exists in the list. We might have a list of non-primary key values of colors and we want to select the items in a product file whose color matches one of the colors in this list.
It would be a very nice feature if we could do something like:
>LSELECT PRODUCTS WITH COLOR IN MYLIST
Somewhere we created MYLIST based on various colors with certain properties. e.g.
>SELECT COLORS WITH COLORTYPE = "POPULAR"
20 items selected.
>SAVE.LIST MYLIST Overwriting existing saved list. 20 key(s) saved to 1 record(s).
Now we want to use our fantasized query to select all our products that have this popular color.
>LSELECT PRODUCTS WITH COLOR IN MYLIST 750 items selected.
>>SORT PRODUCTS ....
Rocket Software, and IBM before this, had a tech tip that allowed us to create this new verb (LSELECT).
https://u2devzone.rocketsoftware.com/accelerate/articles/u2-select/u2-select
The following is a basic program I created to use on UniData.
!
** Select file items using keys in select list
** (C) Copyright 1985-2012, Pacific Mgmt Software, Inc. All Rights Reserved.
!
** Last Modified: 07 Aug 2012, wph
** First Created: 01 Oct 2009, wph
** Program Type-: Utility
!
** Notes:
**
** This process selects the items in [[FileName]] whose keys are in the
** defined select (saved) list.
**
**----------------------------------------------------------------------**
** **
** I N I T I A L I Z A T I O N **
** **
**----------------------------------------------------------------------**
*
** Initialization
NULL$ = ''
SP1 = ' '
*
* Initialize command variables
FILE.NAME = NULL$
DICT.NAME = NULL$
OPER = NULL$
SOURCE.LIST = NULL$
TO.OP = NULL$
DEST.LIST = NULL$
*
** Initialize other variables
SOURCE.LIST.NAMED = 1
DEST.LIST.NAMED = 1
DISPLAY.HELP = 0
VERBOSE = 0
*
**----------------------------------------------------------------------**
** **
** S T A R T P R O C E S S I N G D A T A **
** **
**----------------------------------------------------------------------**
*
** Run the command program
GOSUB GET.COMMAND
GOSUB GET.SOURCE.SELECT.LIST
GOSUB CREATE.TEMP.FILE
GOSUB LOAD.TEMP.FILE
GOSUB BUILD.LIST2
GOSUB FINISH.UP
GOTO END.OF.PROGRAM
*
**----------------------------------------------------------------------**
** **
** S U B R O U T I N E ( S ) **
** **
**----------------------------------------------------------------------**
*
** Get the command and process
***************
GET.COMMAND:
***************
*
** Gather and parse input from command line
INPUT.PARAMS = TRIM(FIELD(@SENTENCE, " ", 2, 999))
SWAP ' ' WITH ';' IN INPUT.PARAMS
POS = 1
FILE.NAME = TRIM(FIELD(INPUT.PARAMS, ";", POS))
POS += 1
WITH.OP = UPCASE(TRIM(FIELD(INPUT.PARAMS, ";", POS)))
IF WITH.OP = "WITH" THEN POS += 1
DICT.NAME = TRIM(FIELD(INPUT.PARAMS, ";", POS))
POS += 1
OPER = UPCASE(TRIM(FIELD(INPUT.PARAMS, ";", POS)))
IF OPER = "IN" THEN
POS += 1
SOURCE.LIST= TRIM(FIELD(INPUT.PARAMS, ";", POS))
POS += 1
TO.OP = UPCASE(TRIM(FIELD(INPUT.PARAMS, ";", POS)))
IF TO.OP = "TO" THEN POS += 1
DEST.LIST = TRIM(FIELD(INPUT.PARAMS, ";", POS))
IF DEST.LIST = "VERBOSE" OR DEST.LIST = "-V" THEN
VERBOSE = 1
DEST.LIST = NULL$
END
END ELSE
IF OPER = "TO" THEN
POS += 1
DEST.LIST = OCONV(TRIM(FIELD(INPUT.PARAMS, ";", POS)), 'MCU')
IF DEST.LIST = "VERBOSE" OR DEST.LIST = "-V" THEN
VERBOSE = 1
DEST.LIST = NULL$
END
END ELSE
IF OPER # NULL$ THEN
ERROR.MSG = 'Expecting "IN" or "TO" clause. Found ':OPER
GOSUB SHOW.MESSAGE
DISPLAY.HELP = 1
END
END
END
*
LAST.COMMAND = UPCASE(FIELD(INPUT.PARAMS, ";", DCOUNT(INPUT.PARAMS, ";")))
IF (LAST.COMMAND = "VERBOSE") OR (LAST.COMMAND = "-V") THEN VERBOSE = 1
*
** Display Usage if parameters not supplied or help is requested
IF FILE.NAME = NULL$ OR FILE.NAME = "?" OR FILE.NAME = "HELP" OR DICT.NAME = NULL$ THEN DISPLAY.HELP = 1
IF DISPLAY.HELP THEN
PRINT
PRINT 'This will create a list of {[[FileName]]} keys for records whose'
PRINT 'field definition value appears in a defined list.'
PRINT
PRINT 'Syntax:'
PRINT ' LSELECT [[File_Name]] WITH [[Dict_Name]] IN LIST1 TO LIST2 [VERBOSE| -V]'
PRINT
PRINT 'Example: SELECT CUSTOMERS SAMPLE 500'
PRINT ' SAVE.LIST MY.LIST'
PRINT ' LSELECT SALES.ORDERS WITH CUST.NBR IN MY.LIST TO MY.LIST.2'
PRINT
PRINT 'Notes:'
PRINT 'If a destination list is not supplied then keys will be left as the active'
PRINT 'select list. If a source list is not supplied then the current active select'
PRINT 'list will be used. Lists may be specified as select buffers (0-8) or as named'
PRINT 'lists from SAVEDLISTS.'
PRINT
STOP
END
*
** Validate Dictionary
DR.REC = XLATE("DICT ":FILE.NAME, DICT.NAME, -1, "X")
IF DR.REC = NULL$ THEN
ERROR.MSG = "Unable to read dictionary ":DICT.NAME:" for file ":FILE.NAME
GOSUB SHOW.MESSAGE
STOP
END ELSE
IF DR.REC<6> # "S" THEN
ERROR.MSG = "Warning, ":DICT.NAME:" is not a single valued field. Results may be unexpected."
GOSUB SHOW.MESSAGE
END
END
*
** Complete defaulting behavior
IF SOURCE.LIST = NULL$ THEN
SOURCE.LIST = 0
END
*
** Make sure the destination list has a value
IF DEST.LIST = NULL$ THEN DEST.LIST = 0
*
** Make sure source list name has a value (under misc circumstances)
IF NUM(SOURCE.LIST) AND LEN(SOURCE.LIST) = 1 AND (SOURCE.LIST NE 9) THEN SOURCE.LIST.NAMED = 0
*
** Make sure destination list name has a value (under misc circumstances)
IF NUM(DEST.LIST) AND LEN(DEST.LIST)=1 AND (DEST.LIST # 9) THEN DEST.LIST.NAMED = 0
*
** Display verbose information
IF VERBOSE THEN
PRINT '(Parsed Command)'
PRINT 'LSELECT ':FILE.NAME:' WITH ':DICT.NAME:' IN ':SOURCE.LIST:' TO ':DEST.LIST
PRINT
END
RETURN
!
** Get the list of values to match
***************
GET.SOURCE.SELECT.LIST:
***************
*
SOURCE.ID.LIST = NULL$
*
** Process a named list
IF SOURCE.LIST.NAMED THEN
EXECUTE \GET-LIST \ : SOURCE.LIST CAPTURING OUTPUT
[[NoOfItems]] = SYSTEM(11)
IF NOT([[NoOfItems]]) THEN
ERROR.MSG = "Error reading saved list ":SOURCE.LIST : SP1 : OUTPUT
GOSUB SHOW.MESSAGE
STOP
END
READSELECT SOURCE.ID.LIST ELSE SOURCE.ID.LIST = NULL$
END ELSE
READSELECT SOURCE.ID.LIST FROM SOURCE.LIST ELSE SOURCE.ID.LIST = NULL$
END
*
** Display verbose information
IF VERBOSE THEN
PRINT '(Selected List)'
PRINT 'List Source ':SOURCE.LIST:' found ':DCOUNT(SOURCE.ID.LIST, @AM):' items.'
PRINT
END
RETURN
!
** Create temporary file to support TRANS selection
***************
CREATE.TEMP.FILE:
***************
*
** determine appropriate file size based on size of list of values to match
TBYTES = SUM(LENS(SOURCE.ID.LIST))
TEMP.MOD = INT(TBYTES/1024)
IF TEMP.MOD < 1 THEN
TEMP.MOD = 5
END
PROCESS.NBR = @UDTNO + 0
PROCESS.ID = ('0000' : PROCESS.NBR : @LEVEL) "R#4"
TEMP.NAME = 'LSELECT' : PROCESS.ID
UDT.COMMAND = 'CREATE.FILE ' : TEMP.NAME : SP1 : TEMP.MOD
PERFORM UDT.COMMAND CAPTURING UDT.RESPONSE
*
** Now open the temporary file
OPEN '', TEMP.NAME TO TEMP.HANDLE ELSE
ERROR.MSG = "Error opening ":TEMP.NAME:" file"
GOSUB SHOW.MESSAGE
STOP
END
OPEN 'DICT', TEMP.NAME TO TEMP.DICT ELSE
ERROR.MSG = "Error opening DICT ":TEMP.NAME:" file"
GOSUB SHOW.MESSAGE
STOP
END
*
** Display verbose information
IF VERBOSE THEN
PRINT "(Create Temp File)"
PRINT TEMP.NAME:' file created with MOD = ' : TEMP.MOD
PRINT
END
RETURN
!
** Put selection values into temp file
***************
LOAD.TEMP.FILE:
***************
*
EMPTY.STRING = ''
MORE.IDS = 1
[[LoadCnt]] = 0
LOOP
REMOVE ID FROM SOURCE.ID.LIST SETTING MORE.IDS
WRITEVU EMPTY.STRING ON TEMP.HANDLE, ID, 0
[[LoadCnt]] += 1
WHILE MORE.IDS DO
REPEAT
*
** Reset internal udt REMOVE pointer
SOURCE.ID.LIST = SOURCE.ID.LIST
*
** Display verbose information
IF VERBOSE THEN
PRINT "(Loaded List to Temp File) - " : [[LoadCnt]] : " items."
PRINT
END
RETURN
*
** Create the resulting list
***************
BUILD.LIST2:
***************
*
TRANS.STMT = "TRANS('" : TEMP.NAME : "'," : DICT.NAME : ",'@ID','X')"
IF DEST.LIST.NAMED THEN
UDT.COMMAND = 'select ':FILE.NAME:' WITH EVAL "':TRANS.STMT:'" GT "" '
PERFORM UDT.COMMAND CAPTURING UDT.RESPONSE
PERFORM 'SAVE.LIST ':DEST.LIST
END ELSE
UDT.COMMAND = 'select ':FILE.NAME:' WITH EVAL "':TRANS.STMT:'" GT "" TO ':DEST.LIST
PERFORM UDT.COMMAND CAPTURING UDT.RESPONSE
IF NOT(VERBOSE) THEN HUSH ON
PERFORM 'SAVE.LIST ':TEMP.NAME
IF NOT(VERBOSE) THEN HUSH OFF
END
*
** Display verbose information
IF VERBOSE THEN
PRINT "(Selected Result List)"
PRINT "UDT.COMMAND = ":UDT.COMMAND
PRINT "UDT.RESPONSE = ":UDT.RESPONSE
PRINT "Destination ":DEST.LIST
PRINT "Named List ":DEST.LIST.NAMED
PRINT
END
RETURN
!
** Remove temporary key list file
***************
FINISH.UP:
***************
*
** Close and delete the temporary file
CLOSE TEMP.HANDLE
CLOSE TEMP.DICT
UDT.COMMAND = 'DELETE.FILE ':TEMP.NAME
DATA 'Y'
PERFORM UDT.COMMAND CAPTURING UDT.RESPONSE
*
** Display verbose information
IF VERBOSE THEN
PRINT "(Delete Temp File)"
PRINT TEMP.NAME:' file deleted...'
PRINT
END
*
** Create a currently active select list (if no to-list defined)
IF NOT(DEST.LIST.NAMED) THEN
PERFORM 'GET.LIST ':TEMP.NAME CAPTURING UDT.RESPONSE
READSELECT SOURCE.ID.LIST ELSE SOURCE.ID.LIST = NULL$
PERFORM 'DELETE.LIST ':TEMP.NAME CAPTURING UDT.RESPONSE
FORMLIST SOURCE.ID.LIST
END
RETURN
!
** Display error messages
***************
SHOW.MESSAGE:
***************
*
PRINT ERROR.MSG
PRINT "Press <cr> To Continue":
INPUT DUMMY
RETURN
*
**----------------------------------------------------------------------**
** **
** E N D O F P R O G R A M **
** **
**----------------------------------------------------------------------**
*
***************
END.OF.PROGRAM:
***************
*
END
A use of the program would look like:
2 Dev (0)-> SELECT MASTER WITH YREND NE "12"
4 records selected to list 0.
2 Dev (0)-> SAVE-LIST MY.LIST Overwriting existing saved list. 4 key(s) saved to 1 record(s). 2 Dev (0)-> LSELECT ARTMASTER WITH CLIENTNO IN MY.LIST VERBOSE (Parsed Command) LSELECT ARTMASTER WITH CLIENTNO IN MY.LIST TO 0
(Selected List) List Source MY.LIST found 4 items.
(Create Temp File) LSELECT0021 file created with MOD = 5
(Loaded List to Temp File) - 4 items.
235 key(s) saved to 1 record(s). (Selected Result List) UDT.COMMAND = select ARTMASTER WITH EVAL "TRANS('LSELECT0021',CLIENTNO,'@ID','X') " GT "" TO 0 UDT.RESPONSE = ■235 records selected to list 0.■■ Destination 0 Named List 0
(Delete Temp File) LSELECT0021 file deleted...
2 Dev (0)-> SSCROLL ARTMASTER
Command => SORT ARTMASTER 09:11:36 Oct 18 2012 ARTMASTER... CUSTOMER NAME (LFM)............ BT MOVE-IN. PRICHG$... BAL DUE... 120*1 ROBINSON, JAMES 05-17-07 210.00 0.00 120*2 ROBINSON, KATHRYN J 06-18-08 210.00 210.00 120*3 GARCIA, SAMANTHA L 08-13-09 210.00 -210.00 120*4 ROBINSON, CAROL A 03-15-95 210.00 210.00 120*5 ROBINSON, AARON L 09-19-06 210.00 210.00 . .
I hope this is of some help.
