Today's Classic Tip
Treating Arrays as Tables
Often, it may be better to store data in an
array or a series of arrays than in a temporary table. A few examples of when this would be true
could be the following:
Performance
is improved by reading from memory (array)
than from disk (table)
No
need to clean up temporary files after execution
CCL provides a simple way to use arrays as
if they were tables. The key element of
this approach is the use of the DUMMYT table in conjunction with the Range
command. To read an array in the Select
clause and to Join from it to another table, all you have to do is set the “Range”
value to the Dummyt Table with “SEQ=<count>” where count is equal to the
number of elements in the array.
The
code snippet below demonstrates this approach:
RANGE
OF D IS DUMMYT WITH SEQ=VALUE(TMP_ARR_CNT)
RANGE
OF T1 IS TD000_1
SELECT
DISTINCT
PHRASE_FLAG=TMP_PHRASE_FLAG_ARR[D.SEQ],
PHRASE_NBR=TMP_PHRASE_NBR_ARR[D.SEQ],
ISOLATE_NBR=TMP_ISOLATE_NBR_ARR[D.SEQ],
ISOLATE_MNE=TMP_ISOLATE_MNE_ARR[D.SEQ],
SOURCE_STR=TMP_SOURCE_STR_ARR[D.SEQ],
COL_DATE=TMP_COL_DATE_ARR[D.SEQ],
COL_TIME=TMP_COL_TIME_ARR[D.SEQ],
REC_DATE=TMP_REC_DATE_ARR[D.SEQ],
REC_TIME=TMP_REC_TIME_ARR[D.SEQ],
OBSERV_DT_TM=TMP_OBSERV_DT_TM_ARR[D.SEQ],
OBSERV_DATE=TMP_OBSERV_DATE_ARR[D.SEQ],
OBSERV_TIME=TMP_OBSERV_TIME_ARR[D.SEQ],
FREETEXT_SRC=TMP_FREETEXT_SRC_ARR[D.SEQ],
REPORT_LINE=TMP_REPORT_LINE_ARR[D.SEQ],
ENBR=TMP_ENBR_ARR[D.SEQ],
RCODE=TMP_RCODE_ARR[D.SEQ],
ACCESSION=TMP_ACCESSION_ARR[D.SEQ],
PROCEDURE_NBR=TMP_PROCEDURE_NBR_ARR[D.SEQ],
ORD_TEST_NBR=TMP_ORD_TEST_NBR_ARR[D.SEQ],
ENTRY_NAME=TMP_ENTRY_NAME_ARR[D.SEQ],
ORD_TEST_MNE=T1.MNEMONIC
PLAN
D
WHERE
TMP_ACCESSION_ARR[D.SEQ]>" "
AND TMP_PHRASE_FLAG_ARR[D.SEQ]>0
AND TMP_REPORT_LINE_ARR[D.SEQ]>"
"
JOIN
T1
WHERE
TMP_ORD_TEST_NBR_ARR[D.SEQ]=T1.TEST_NBR
If
you have any questions about this approach, please contact us.