Great enhancement. I hope the development team will consider adding
this as a new SELECT function "LIST()" similar to present "AVG(),
SUM(), MIN(), MAX(), COUNT()" functions. I know several major
competitors offer such a function. Your example seems to be inconsistent
with SQL 92 syntax.
--
Jim Bentley
American Celiac Society
[EMAIL PROTECTED] - email
(973) 776-3900 x5029 - voicemail/fax
---- "A. Razzak Memon" <[EMAIL PROTECTED]> wrote:
>
> At 01:58 PM 8/31/2001 -0700, Ron Rose wrote:
>
> >How does one get a valuelist (delimited string) from
> >a SELECT command.
>
> Ron,
>
> Very simple!
>
> Option 01:
>
> Using TGRB2000 version 6.5++, build:1.843 and higher:
>
> SET ERROR MESSAGE 2441 OFF
> SET VAR vValueList TEXT = NULL
> SELECT ColumnName INTO vValueList INDIC IvValueList +
> FROM TableName WHERE ... ORDER BY ...
>
> The variable vValueList will include the list of
> delimited string values!
>
> If you would like to see it, you just have to wait
> until October 20th, 2001 for that bag of tricks <g>.
>
> Option 02:
>
> CONNECT Concomp
> SET CAPTION ' '
> SET VAR vPickItem TEXT = NULL
> SET VAR vValueList TEXT = NULL
> SET VAR vLastName TEXT = NULL
> SET VAR vLines INTEGER = 0
> SET VAR vTitle TEXT = 'Select Employee Last Name'
> SET VAR vCaption TEXT = 'Using #LIST Option in CHOOSE Command!'
> CLS
> PAUSE 3 USING 'Building vValueList ...' CAPTION .vCaption AT
> 16 30
> SELECT COUNT(*) INTO vLines INDIC IvLines FROM Employee
> IF vLines > 18 THEN
> SET VAR vLines = 18
> ENDIF
> SET ERROR MESS 705 OFF
> DROP CURSOR c#1
> DECLARE c#1 CURSOR FOR SELECT Emplname FROM Employee +
> ORDER BY Emplname
> OPEN c#1
> FETCH c#1 INTO vLastName INDIC IvLastName
> WHILE SQLCODE <> 100 THEN
> SET VAR vValueList = +
> (IFNULL(.vValueList,(.vLastName),(.vValueList+','+.vLastName)))
> FETCH c#1 INTO vLastName INDIC IvLastName
> ENDWHILE
> DROP CURSOR c#1
> CLEAR VAR IvLines, IvValueList, vLastName, IvLastName
>
> CLS
> CHOOSE vPickItem FROM #LIST .vValueList AT 6 30 +
> TITLE .vTitle CAPTION .vCaption LINES .vLines FORMATTED
> IF vPickItem IS NULL OR vPickItem = '[Esc]' THEN
> GOTO Done
> ELSE
> CLEAR VAR vTitle, vCaption, vLines, IvLines
> ENDIF
>
> -- Do what you have to do here ...
>
> LABEL Done
> -- CLEAR ALL VAR
> -- or CLEAR ALL VAR EXCEPT ListOfVariables
> -- QUIT TO MainMenu.RMD
>
> There you have it.
>
> The variable vValueList will include the list of delimited
> string values!
>
> Notes:
>
> Option 02 has the advantage of collecting data from various
>
> tables DECLARing different CURSORS and then building a very
>
> complex vValueList!
>
> Option 02 is a sample code. You could achieve the simple
> list by using the CHOOSE ... #VALUES option also.
>
> Have Fun!
>
> Very Best Regards,
>
> Razzak.
>
> ===================================-============================
> R:BASE Developers's Conference: http://www.rbase.com/conference
> Official R:BASE List Server: mailto:[EMAIL PROTECTED]
> RBTI Events/Training: http://www.rbase2000.com/events
> R:DCC Members: http://www.rbase2000.com/rdcc
> ================================================================
> R:BASE, Oterro & R:Tango are registered trademarks of RBTI.
> ==================================-=============================
>
__________________________________________________
FREE voicemail, email, and fax...all in one place.
Sign Up Now! http://www.onebox.com
