Jim,
SQL does not selects that build a list variable. Also the function LIMIT
will restrict the rows tested and not the frequency of the data appearing in
a table. You need to build a while loop to capture each individual value
and append it into a variable. This is the way I would approach it,
SET VAR vgl_match TEXT
SET VAR vg_gl_acct TEXT
DELCLARE c1 CURSOR FOR SELECT gl_acct +
FROM gl_pur_01 +
GROUP BY gl_acct
WHERE COUNT(gl_acct) = 1 AND +
gl_acct IS NOT NULL
OPEN c1
FETCH c1 INTO vgl_match
WHILE SQLCODE = 0 THEN
SET vg_gl_acct = (vg_gl_acct+','+.vgl_match)
FETCH c1 INTO gl_match
ENDWH
CLOSE c1
DROP CURSOR c1
to really make this code sizzle be sure your column gl_acct is indexed.
Hope this helps,
Ken
Jim Limburg wrote:
>
> G-Day all
>
> I started out this morning to do what I thought a simple thing. To build
> a sub-select statement that would return into a variable the values in a
> column that are not null and distinct even if it is in the column more
> than once..
>
> I want the variable to contain 6090,7010,4050,1320,1590 ... and so on...
>
> The data in the columns is text/12 and here is what I attempted so far.
>
> SELECT gl_acct INTO vg_gl_acct FROM gl_pur_01 WHERE gl_acct IN +
> (SELECT gl_acct FROM gl_pur_01 where gl_acct is not null and limit=1)
>
> Thanks
> Jim Limburg
--
Ken Burton
The Burton Consulting Group
P O Box 801450
Santa Clarita, CA 91380-1450
U.S.A.
Tel: 661.297.3090 Fax: 661.297.6406
E-mail: [EMAIL PROTECTED]
