I can not see any information in pdf document about built-in LIST function

>>Btw, this SP isn't necessary with FB 2.1 anymore, because there is a
>>built-in LIST function which does exactly this. For example:

>>select list(initial, ',') from resources where allocation_id = 1

-----Original Message-----
From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] On Behalf Of Thomas Steinmaurer
Sent: Tuesday, April 03, 2007 9:44 AM
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] Trigger Syntax

Hi Myles,

>> In order to improve performance on a query, I would like to add a trigger

to

>> a table to automatically update a 'disply' field with some values. The
>> values would look like this:
>>
>> 'XX,YY,ZZ'
>>
>> And are based on the number of related resources that are allocated to a
>> slot. My table structure looks kinda like this:
>>
>> <ALLOCATION> 1 --- N <RESOURCES>
>>
>> (but it's a bit more complex in real life). Each resource has Initials,

and

>> this is what I want to show in the XX, YY, ZZ field. In order to do

this, I

>> believe I will need a small stored procedure that can be called from

within

>> a trigger, that will select all of the allocated resources, and then loop
>> through the rows returned, extracting the Initials and creating a string
>> variable, seperated by commas for display.
>>
>> I can see the syntax for a WHILE loop in PSQL, however how do you

associate

>> this with the number of rows returned from a SELECT statement? I need to
>> loop through each row returned and add to the string variable I'm
>> constructing.
>>
>> All pointers greatly appreciated.
>
> Not bullet-proof, but the following might get you onto the right track.
>
> A selectable SP for getting a list of resource initials for a given
> allocation.
>
> SET TERM ^^ ;
> CREATE PROCEDURE P_RESOURCELIST (
> ALLOCATION_ID Integer)
> returns (
> RES_LIST VarChar(100))
> AS
> declare variable vInitial varchar(5);
> begin
> res_list = '';
> for select initial from resources where allocation_id =
> :allocation_id into :vInitial do
> begin
> if (res_list = '') then
> begin
> res_list = vInitial;
> end else
> begin
> res_list = res_list || ',' || vInitial;
> end
> end
> if (res_list = '') then
> begin
> res_list = null;
> end
> suspend;
> end
> ^^
> SET TERM ; ^^

Btw, this SP isn't necessary with FB 2.1 anymore, because there is a
built-in LIST function which does exactly this. For example:

select list(initial, ',') from resources where allocation_id = 1


--
Best Regards,
Thomas Steinmaurer
LogManager Series - Logging/Auditing Suites supporting
InterBase, Firebird, Advantage Database, MS SQL Server and
NexusDB V2
Upscene Productions
http://www.upscene.com


++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Visit http://www.firebirdsql.org and click the Resources item
on the main (top) menu. Try Knowledgebase and FAQ links !

Also search the knowledgebases at http://www.ibphoenix.com

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Yahoo! Groups Links




__________ NOD32 2165 (20070403) Information __________

This message was checked by NOD32 antivirus system.
http://www.eset.com