Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 10 of 11 total |
How to convert row values to a single concatenated string |
Mon, Oct 23 2017 2:54 AM | Permanent Link |
Joe Mainusch | 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 |
Mon, Oct 23 2017 5:06 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
Mon, Oct 23 2017 6:30 AM | Permanent Link |
Joe Mainusch | WOW !!!!!!!!!!
I was not aware of the LIST function. That works great! Thank you, Roy. Joe |
Mon, Oct 23 2017 8:25 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
Mon, Oct 23 2017 10:26 AM | Permanent Link |
Adam Brett Orixa Systems | 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. |
Mon, Oct 23 2017 11:15 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Adam
You have totally ignored the fact that most SQL is FUGLY Roy Lambert |
Mon, Oct 23 2017 12:40 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
Tue, Oct 24 2017 2:28 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
Thu, Oct 26 2017 7:47 AM | Permanent Link |
Adam Brett Orixa Systems | 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. |
Thu, Oct 26 2017 10:46 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
Page 1 of 2 | Next Page » | |
Jump to Page: 1 2 |
This web page was last updated on Thursday, May 23, 2024 at 03:39 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |