Tuesday, August 19, 2008 Tip of the Day: Using (LISTOF(colname)) Function for Large Data
-- Supported Versions: . R:BASE 7.6 for DOS (Build: 7.6.4.30819 or higher) . R:BASE 7.6 for Windows (Build: 7.6.4.30819 or higher) . R:BASE C/S:I 7.6 for Windows (Build: 7.6.4.30819 or higher) . R:BASE Turbo V-8 for Windows (Build: 8.0.18.30819 or higher) . R:BASE eXtreme for Windows (Build: 9.0.1.30819 or higher) Did you know that you can use VARCHAR data type to retrieve large dataset when using the (LISTOF(colname)) function? Syntax: SELECT (LISTOF(colname)) IN TableViewName SELECT (LISTOF(colname)) INTO varname INDIC ivarname FROM tblviewname Note: Use WHERE clause to limit the resulting data, if necessary. The (LISTOF(colname)) function creates a text string of the matched values separated by the current comma delimiter character. The LISTOF function can be used with the "SELECT ... INTO ..." to populate a variable with a list of values which can then be used in a CHOOSE command with the #LIST option. It can also be used in Forms, Reports or Labels to look up values from multiple rows. By default, the (LISTOF(colname)) function returns a NOTE data type and notes are limited to 4K. However, if you need to retrieve a large data set to build a dynamic CHOOSE command, Dynamic ListView, or even Dynamic ListBox, now you can use the VARCHAR data type to achieve your goal. -- Example 01: CONNECT RRBYW15 SET VARIABLE vResult VARCHAR = NULL SELECT (LISTOF(CompDesc)) IN Component -- Example 02: CONNECT RRBYW15 SET VARIABLE vResult VARCHAR = NULL SELECT (LISTOF(CompDesc)) IN Component WHERE CompDesc CONTAINS 'Box' -- Example 03: CONNECT RRBYW15 SET VARIABLE vResult VARCHAR = NULL SELECT (LISTOF(CompDesc)) INTO vResult INDIC ivResult FROM Component SHOW VARIABLE vResult -- Example 04: CONNECT RRBYW15 SET VARIABLE vResult VARCHAR = NULL SELECT (LISTOF(CompDesc)) INTO vResult INDIC ivResult FROM + Component WHERE CompDesc CONTAINS 'Box' SHOW VARIABLE vResult Enjoy the R:BASE you have always wanted! Very Best R:egards, Razzak.
