--- In firebird-support@yahoogroups.com, "SoftTech" <miket@...> wrote:

>
> Greetings All,
>
> Firebird 1.53
>
> Inside a stored procedure I need to build a comma delimited string list
> based on a custom notice ID and return it as a field on each record.
>
> Let's say there are 500 records in the result set and I only want to place
> unique values in the string list like 12,243,50,62,129 but of course there
> are many records in the result set that could have the same custom notice
> ID.
>
> Here is an example of the first 6 records.
> Notice records 1 and 3 are identical and records 5 and 6 have the same
> NOTICE_ID but different CUSTOM_NOTICE_ID's
>
> Record 1
> NOTICE_ID: 255
> CUSTOM_NOTICE_ID: 12
>
> Record 2
> NOTICE_ID: 267
> CUSTOM_NOTICE_ID: 243
>
> Record 3
> NOTICE_ID: 255
> CUSTOM_NOTICE_ID: 12
>
> Record 4
> NOTICE_ID: 288
> CUSTOM_NOTICE_ID: 50
>
> Record 5
> NOTICE_ID: 301
> CUSTOM_NOTICE_ID: 62
>
> Record 6
> NOTICE_ID: 301
> CUSTOM_NOTICE_ID: 129
>
> Currently the NOTICE_CUSTOM_ID_LIST field returned for the first result
> would contain 12,243,12,50,62,129 and I would prefer 12,243,50,62,129
> instead.
>
> Does anyone know of a way to check if the string I'm building already
> contains a value so it is not duplicated in the string list?
>
> In other words by the time I process record 3 if '12' already exist in the
> string '12,243', exclude it.
>
> Any help truly appreciated,
>
> Thanks,
> Mike
>

May propusual is to store the results first in a "temporary" table and then select distinct (and perhabs sorted) from this table to build the string list.

To emulate a temporary table, you can store a session_id from a generator with the Notice_ID and delete the rows with this session_id before leaving the procedure.

Or upgrade to 2.1 to use the global temporary tables ;-)

Good luck
Christian