I have a table "Customers" and related "CustomerLocations" like this:

Customers
=========================
CustNo   CompanyName
1   Company1
2   Company2
3   Company3
...

CustomerLocations
=========================
CustNo   Location
1   Berlin
1   Frankfurt
2   Berlin
2   Munich
2   Stuttgart
3   Hamburg
...

For several reasons I need a "summary" of all locations of a customer as a string (elements separated by a comma or semicolon). I think best would be as a user defined function to get the summary from within stored procedures.

The result from a stored procedure should be:

CustNo   CompanyName   Locations
1   "Company1"   "Berlin, Frankfurt"
2   "Company2"   "Berlin, Munich, Stuttgart"
3   "Company3"   "Hamburg"

I think of a function like this:
getCustomerLocations(CustNo)

...and the stored procedure:
CREATE PROCEDURE "CustomerSelect" ()
BEGIN
  DECLARE procCur CURSOR WITH RETURN FOR procStmt;
  PREPARE procStmt FROM
  'SELECT Customers.CustNo, Customers.CompanyName, getCustomerLocations(Customers.CustNo) AS Locations,
  FROM Customers';
  OPEN procCur;

END

How should the getCustomerLocations() function be designed / which program code is necessary? Is it possible at all?

Thank you for any help.

Joe

Joe


Unless I've misunderstood there's no need for anything complex - a simple query will do

Here's one using my companies and sites tables

SELECT
_Name,
(SELECT LIST(DISTINCT ORDERED _TOWN, ' | ') FROM Sites WHERE _fkCompanies = Companies._ID)
FROM Companies


Roy Lambert

WOW !!!!!!!!!!

I was not aware of the LIST function. That works great!

Thank you, Roy.

Joe

Joe

>I was not aware of the LIST function. That works great!

It is neat, especially since Tim added DISTINCT and ORDERED. The one thing you may need to watch out for is when the company doesn't have a location. If that might be a problem you'll need to COALESCE the location column with something like COALESCE(Location,'*UNKNOWN*') just to emphasise that you have one without the location.

On the other hand just produce a report from CustomerLocations where Location is NULL and say - fill these in please.

Roy

Another useful thing to realize with LIST is that you can list any data-type, provided that you CAST it first.

Table: "Addresses"
ID INTEGER,
HouseNumber INTEGER NOT NULL,
RoadName VARCHAR,
TownCity VARCHAR,
ZIPCode INTEGER NOT NULL

SELECT
 LIST(CAST(HouseNumber as VARCHAR)) + ' ' + COALESCE(RoadName, 'Unknown') + #13 +
          COALESCE(TownCity, 'Unknown') + ' ' + CAST(ZIPCode as VARCHAR)  + #13 + #13 ) as Address
FROM Addresses
WHERE ID = 1234

It makes for ugly SQL, but gives nice, well formatted results after a bit of testing.

Adam


You have totally ignored the fact that most SQL is FUGLY

Roy Lambert

Roy,

<< You have totally ignored the fact that most SQL is FUGLY >>

Amen, brother...

I'm extremely tempted to port the Object Pascal runtime that we have in EWB to EDB and offer it as an option for writing SQL/PSM, but it's a tough decision because of the usage of standard SQL terms/concepts is one of the core design ideas in EDB, and such a change would be a reversion back to more like DBISAM and it's Object Pascal-centric approach.

I'll have to see how the new EWB 2.07 Web Server shapes up before thinking about this further...

Tim Young
Elevate Software
www.elevatesoft.com

Tim

>Amen, brother...

I always wanted a younger brother I can boss about - yippee I now have one

>I'm extremely tempted to port the Object Pascal runtime that we have in EWB to EDB and offer it as an option for writing SQL/PSM, but it's a tough decision because of the usage of standard SQL terms/concepts is one of the core design ideas in EDB, and such a change would be a reversion back to more like DBISAM and it's Object Pascal-centric approach.

I have an approach where if I can't do it easily using SQL/PSM I'll happilly write an external function in Delphi. Its not to difficult but any way that could be made more easy would, in my big headed view, give more bangs for your buck.

Roy

Tim  & Roy,

<< You have totally ignored the fact that most SQL is FUGLY >>

Agreed.


<<I'm extremely tempted to port the Object Pascal runtime that we have in EWB to EDB and offer it as an option for writing SQL/PSM, but it's a tough decision because of the usage of standard SQL terms/concepts is one of the core design ideas in EDB>>

Oh now that is exciting. I agree however that it is nice to keep EDB as a "clean" SQL platform (even if SQL is not a great language). When I am selling my solutions, it is really useful that DB Admin's I have to work with can be immediately bought off with "it is fully SQL compliant". For a product like EDB that is really helpful in getting through the door.

I have written DLLs (about 4 in 10 years or so!) and agree with Roy that making this process more transparent could be useful. Perhaps you could extend the MODULE paradigm in EDB by having a tool to write pas-based modules that could interact directly with EDB?

I realise this could create a external injection security threats.

Adam

> Perhaps you could extend the MODULE paradigm in EDB by having a tool to write pas-based modules that could interact directly with EDB?

Can you expand on this because I don't understand.

Roy