Builder Solutions Inc. staff members are available when you need us. We are dedicated, motivated, thorough, and professional, providing services to exceed your expectations. We have been serving the LANSA community since 1997.
(1) select continue() endselect
(2) select where() endselect
(3) select_sql
(4) select_sql using()
(1) is the slowest method if LANSA is generating SQL statements based on the select command because the dataset must be generated first and then the code will traverse the entire dataset . (2) is faster than (1) when the fields in the where statement belong to the file and will not cause an Access Path to be built at runtime.
(3) and (4) are the fastest because they do not call the OAM after each record is read.
(4) is faster then (3) when you can utilize TOP(1) instead of FETCH (get 1 record), or using TOP(n) to minimize the dataset. Also (4) can be used to do very complex joins utilizing all the power of SQL (Group By, count , etc)
It is still horses for courses. Factors: existing access paths, complexity of where clause, call server function or just access from client, how many records are being read, is the OAM required when reading records .
Cost v Benefit = Code Complexity v Improved Performance .
For batch processing, reports and server functions (retrieving Instance lists ) we tend to use select_sql and select_sql using() .
For client side we just use standard select and fetch statements.
Its amazing how much faster a cleverly constructed SQL statement with Joins will perform versus using Selects and Fetches to collect the data.
This group was created on Yahoo as a club and was migrated to yahoo groups when clubs dissolved.
If your posts are slow to be acknowledge, please contact me and I will resolve any issues.
Cheers,
Mark Mason
877-LANSA20
********** Created by ..: MMASON
********** Created on ..: 01/03/06 at 10:26:48
********** Description .: String Parser returns List
********** Fields In ...: XXA256I Input String
********** XXDELIM Delimiter used ','
********** Out ..: W256_LST Working List
********** =====================================================
********** FUNCTION, OPEN and EXCHANGE(*ALWAYS) statments
********** =====================================================
FUNCTION OPTIONS(*DEFERWRITE *DIRECT) RCV_LIST(#W256_LST)
**********
********** =======================================================
********** Working fields(DEFINE statments)
********** =======================================================
DEFINE FIELD(#W_LENGTH) TYPE(*DEC) LENGTH(3) DECIMALS(0)
DEFINE FIELD(#W_FLDCNT) TYPE(*DEC) LENGTH(3) DECIMALS(0)
**********
DEF_ARRAY NAME(#ZST) INDEXES(#II) OVERLAYING(#XXA256I) TYPE(*CHAR
) TOT_ENTRY(256) ENTRY_LEN(1)
DEF_ARRAY NAME(#ZFL) INDEXES(#JJ) OVERLAYING(#XXA256J) TYPE(*CHAR
) TOT_ENTRY(256) ENTRY_LEN(1)
DEF_LIST NAME(#W256_LST) FIELDS((#XXA256F)) COUNTER(#W_FLDCNT) T
YPE(*WORKING) ENTRYS(256)
********** =======================================================
********** Screen Panel Groups(GROUP_BY statments)
********** =======================================================
********** =======================================================
********** Initalization(CHANGE and CLR_LIST statments)
********** =======================================================
CLR_LIST NAMED(#W256_LST)
**********
USE BUILTIN(TCONCAT) WITH_ARGS(#XXA256I #BLANKS) TO_GET(#XX
A256I #W_LENGTH)
********** do not allow blanks as delimeter
IF_NULL #XXDELIM
CHANGE #XXDELIM ''','''
ENDIF
BEGIN_LOOP USING(#II) FROM(1) TO(256) STEP(1)
**********
IF COND('(#ZST#II *EQ #XXDELIM) *OR (#JJ *EQ #W_LENGTH)')
CHANGE FIELD(#XXA256F) TO(#XXA256J)
ADD_ENTRY TO_LIST(#W256_LST)
CHANGE #XXA256J *BLANKS
LEAVE IF('#JJ *EQ #W_LENGTH')
CHANGE FIELD(#JJ) TO(*ZERO)
ELSE
CHANGE FIELD(#JJ) TO('#JJ + 1')
CHANGE FIELD(#ZFL#JJ) TO(#ZST#II)
ENDIF
END_LOOP
DEFINE FIELD(#W_LEN_A) TYPE(*DEC) LENGTH(3) DECIMALS(0)
DEFINE FIELD(#W_LEN_F) TYPE(*DEC) LENGTH(3) DECIMALS(0)
DEFINE FIELD(#W_LEN_R) TYPE(*DEC) LENGTH(3) DECIMALS(0)
DEFINE FIELD(#W_LEN_T) TYPE(*DEC) LENGTH(3) DECIMALS(0)
DEFINE FIELD(#W_STR_A) TYPE(*DEC) LENGTH(3) DECIMALS(0)
DEFINE FIELD(#W_STR_B) TYPE(*DEC) LENGTH(3) DECIMALS(0)
DEFINE FIELD(#W_STR_C) TYPE(*DEC) LENGTH(3) DECIMALS(0)
DEFINE FIELD(#W_STR_D) TYPE(*DEC) LENGTH(3) DECIMALS(0)
DEFINE FIELD(#W_STR_T1) TYPE(*DEC) LENGTH(3) DECIMALS(0)
DEFINE FIELD(#W_STR_T2) TYPE(*DEC) LENGTH(3) DECIMALS(0)
DEFINE FIELD(#W_FIND) TYPE(*CHAR) LENGTH(035)
DEFINE FIELD(#W_REPL) TYPE(*CHAR) LENGTH(035)
DEFINE FIELD(#W_STRING) TYPE(*CHAR) LENGTH(075)
DEFINE FIELD(#W_STRINGX) TYPE(*CHAR) LENGTH(075)
/* */
DEF_LIST NAME(#L_FINDRPL) FIELDS((#W_FIND) (#W_REPL))
/* */
INZ_LIST NAMED(#L_FINDRPL) NUM_ENTRYS(50) WITH_MODE(*ADD)
REQUEST FIELDS((#W_STRING)) BROWSELIST(#L_FINDRPL)
SELECTLIST #L_FINDRPL
IF_NULL FIELD(#W_FIND #W_REPL)
ELSE
EXECUTE SUBROUTINE(FINDREPLAC) WITH_PARMS(#W_FIND #W_REPL #W_S+
TRING)
ENDIF
ENDSELECT
DISPLAY FIELDS((#W_STRING))
SUBROUTINE NAME(FINDREPLAC) PARMS((#W_FIND) (#W_REPL) (#W_STRING))
USE BUILTIN(TCONCAT) WITH_ARGS(#W_FIND #BLANK) TO_GET(#W_F+
IND #W_LEN_F)
USE BUILTIN(TCONCAT) WITH_ARGS(#W_REPL #BLANK) TO_GET(#W_R+
EPL #W_LEN_R)
USE BUILTIN(TCONCAT) WITH_ARGS(#W_STRING #BLANK) TO_GET(#W+
_STRING #W_LEN_T)
CHANGE #W_STR_T1 1
BEGIN_LOOP
USE BUILTIN(SCANSTRING) WITH_ARGS(#W_STRING #W_FIND #W_STR+
_T1) TO_GET(#W_STR_T2)
CASE #W_STR_T2
WHEN '*EQ *ZERO'
LEAVE
WHEN '*GT *ZERO'
/* do something */
CHANGE #W_LEN_A '#W_STR_T2 - 1'
CHANGE #W_STR_A 1
CHANGE #W_STR_B #W_STR_T2
CHANGE #W_STR_C '#W_STR_T2 + #W_LEN_F'
CHANGE #W_STR_D '#W_STR_T2 + #W_LEN_R'
IF_NULL #W_LEN_A
ELSE
SUBSTRING FIELD(#W_STRING #W_STR_A #W_LEN_A) INTO_FIELD(#W_STRIN+
GX #W_STR_A #W_LEN_A)
ENDIF
SUBSTRING FIELD(#W_REPL #W_STR_A #W_LEN_R) INTO_FIELD(#W_STRING+
X #W_STR_B #W_LEN_R)
SUBSTRING FIELD(#W_STRING #W_STR_C) INTO_FIELD(#W_STRINGX #W_STR+
_D)
CHANGE FIELD(#W_STRING) TO(#W_STRINGX)
CHANGE #W_STR_T1 '#W_STR_T2 + 1'
WHEN '*LT *ZERO'
LEAVE
ENDCASE
END_LOOP
ENDROUTINE
SUBROUTINE NAME(AMT2TEXT) PARMS((#W_NUMBER1) (#W_TEXTOUT))
**********
DEFINE FIELD(#W_NUMBER1) TYPE(*DEC) LENGTH(11) DECIMALS(2)
DEFINE FIELD(#W_POS_X ) TYPE(*DEC) LENGTH(3) DECIMALS(0)
DEFINE FIELD(#W_POS_Y ) TYPE(*DEC) LENGTH(3) DECIMALS(0)
DEFINE FIELD(#W_TEXTOUT) TYPE(*CHAR) LENGTH(20)
DEFINE FIELD(#W_TEXTDFT) TYPE(*CHAR) LENGTH(20)
DEFINE FIELD(#W_CHAR1 ) TYPE(*CHAR) LENGTH(1)
DEFINE FIELD(#W_CHAR2 ) TYPE(*CHAR) LENGTH(1)
DEFINE FIELD(#W_CHAR3) TYPE(*CHAR) LENGTH(3) DEFAULT('.00')
DEFINE FIELD(#W_FIRST) TYPE(*DEC) LENGTH(3) DECIMALS(0)
**********
CHANGE #W_TEXTOUT *BLANKS
CHANGE #W_TEXTDFT '''000,000,000.00'''
CHANGE #W_POS_Y *ZERO
CHANGE #W_FIRST *ZERO
BEGIN_LOOP USING(#W_POS_X) FROM(1) TO(11)
CHANGE #W_POS_Y '#W_POS_Y + 1'
SUBSTRING (#W_NUMBER1 #W_POS_X 1) INTO_FIELD(#W_CHAR1 1 1)
**********
IF '(#W_FIRST *EQ 0) *AND (#W_CHAR1 *EQ ''0'')'
CHANGE #W_CHAR1 *BLANKS
ELSE
CHANGE #W_FIRST '#W_FIRST + 1'
ENDIF
**********
SUBSTRING (#W_TEXTDFT #W_POS_Y 1) INTO_FIELD(#W_CHAR2 1 1)
**********
IF '(#W_CHAR2 *EQ '','')'
IF '(#W_TEXTOUT *NE *BLANKS)'
SUBSTRING FIELD(#W_CHAR2 1 1) INTO_FIELD(#W_TEXTOUT #W_POS_Y 1)
ENDIF
CHANGE #W_POS_Y '#W_POS_Y + 1'
ENDIF
**********
IF '(#W_CHAR2 *EQ ''.'')'
CHANGE #W_FIRST '#W_FIRST + 1'
SUBSTRING FIELD(#W_CHAR3 1 3) INTO_FIELD(#W_TEXTOUT #W_POS_Y 3)
CHANGE #W_POS_Y '#W_POS_Y + 1'
IF_NULL #W_CHAR1
CHANGE #W_CHAR1 '''0'''
ENDIF
ENDIF
SUBSTRING FIELD(#W_CHAR1 1 1) INTO_FIELD(#W_TEXTOUT #W_POS_Y 1)
END_LOOP
**********
USE BUILTIN(LEFT) WITH_ARGS(#W_TEXTOUT) TO_GET(#W_TEXTOUT)
USE BUILTIN(TCONCAT) WITH_ARGS('''$''' #W_TEXTOUT) TO_GET(#
W_TEXTOUT)
IF '#W_NUMBER1 *LT *ZERO'
USE BUILTIN(TCONCAT) WITH_ARGS('''(''' #W_TEXTOUT ''')''')
TO_GET(#W_TEXTOUT)
ENDIF
USE BUILTIN(LEFT) WITH_ARGS(#W_TEXTOUT) TO_GET(#W_TEXTOUT)
**********
ENDROUTINE