Thought about that. Not viable. For one thing, I need this result set
available to other querys. For another thing, fumbling through all the
records and creating the strings in the client is slower in some cases. The
real catch is that doing it in the client combined with the "calculated
fields" method I'd have to use on every query using this stored procedure
would slow the whole program down considerably. I'm trying to avoid client
calculated fields altogether. Is there a better way?

R.

----- Original Message -----
From: davidalbiston
To: firebird-support@yahoogroups.com
Sent: Tuesday, May 10, 2005 12:53 PM
Subject: [firebird-support] Re: Record Collation


R

I would do that in the client.

Fire this SQL to get the data:

Select "LeaseKey", "FirstName" || ' ' || "LastName" "Tenant"
From "TenantActivity"
order by 1

In Delphi, create a TStringList, iterate through the dataset, adding
all the values of "Tenant" for a LeaseKey, then call the CommaText
property of the TStringList for the comma delimited list.

Dave

--- In firebird-support@yahoogroups.com, "Ranando King" <rk@m...> wrote:
> I've got a procedure defined as following
>
> SET TERM ^ ;
>
> /* Stored procedures */
>
> CREATE PROCEDURE "GetTenantNames"
> (
> "LKey" INTEGER
> )
> RETURNS
> (
> "Names" VARCHAR(128) CHARACTER SET WIN1251
> )
> AS
> BEGIN EXIT; END ^
>
>
> ALTER PROCEDURE "GetTenantNames"
> (
> "LKey" INTEGER
> )
> RETURNS
> (
> "Names" VARCHAR(128) CHARACTER SET WIN1251
> )
> AS
> Declare Variable name VarChar(32);
> Begin
> "Names" = Null;
> For
> Select "FirstName" || ' ' || "LastName"
> From "TenantActivity"
> Where "LeaseKey"=:"LKey"
> Into name
> Do
> Begin
> If ("Names" Is Not Null) Then
> Begin
> "Names" = "Names" || ', ';
> "Names" = "Names" || name;
> End
> Else
> "Names" = name;
> End
> End
> ^
>
> SET TERM ; ^
> COMMIT WORK;
>
> It works just fine save for the fact that its kinda slow when called
by the following procedure:
>
> SET TERM ^ ;
>
> /* Stored procedures */
>
> CREATE PROCEDURE "AllTenantNames"
> RETURNS
> (
> "LeaseKey" INTEGER,
> "Names" VARCHAR(128) CHARACTER SET WIN1251
> )
> AS
> BEGIN EXIT; END ^
>
>
> ALTER PROCEDURE "AllTenantNames"
> RETURNS
> (
> "LeaseKey" INTEGER,
> "Names" VARCHAR(128) CHARACTER SET WIN1251
> )
> AS
> Declare Variable name VarChar(32);
> Begin
> For
> Select L."LeaseKey"
> From "Leases" L
> Into "LeaseKey"
> Do
> Begin
> Execute Procedure "GetTenantNames" ("LeaseKey")
Returning_Values ("Names");
> Suspend;
> End
> End
> ^
>
> SET TERM ; ^
> COMMIT WORK;
>
> The idea is to get all the tenant names associated with a particular
lease and return them as a comma delimited list per lease. Is there a
better way of doing this that won't take 2-3 seconds to calculate over
600 tenant entries and 400 lease entries?
>
> R.
>
>
> [Non-text portions of this message have been removed]




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

Visit http://firebird.sourceforge.net 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

To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/

To unsubscribe from this group, send an email to:
firebird-support-unsubscribe@yahoogroups.com

Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.