1

In Firebird 2.5 or 3.0, and Delphi 10.2: In a master-detail configuration, where there's always at least 1 and and not more than 4 or 5 detail rows per master, I need to get a single row that has certain fields from the master row and a field that concatenates 1 field from each detail row into a single CSV-format string. I need to do this all in SQL and not have to create a stored proc or user function, because I need to do this against customer databases and don't want to be adding objects to databases I don't own; I also don't want to use a calculated field in code if I don't have to, because our support guys need to be able to do this by a script in a DB browser rather than building it in into the program.

To model this I'm using the RDB$INDICES and RDB$INDEX_SEGMENTS system tables. Getting one or more rows per index, each with one segments detail field, is straightforward:

  SELECT
    r.RDB$INDEX_NAME AS "Index",
    f.RDB$FIELD_POSITION AS "Order",
    f.RDB$FIELD_NAME AS "Fields"
  FROM RDB$INDICES r
    LEFT JOIN RDB$INDEX_SEGMENTS f ON r.RDB$INDEX_NAME = f.RDB$INDEX_NAME
  WHERE r.RDB$FOREIGN_KEY IS NULL
  AND UPPER(r.RDB$RELATION_NAME) = UPPER(:TBLNAME)
  ORDER BY r.RDB$RELATION_NAME, f.RDB$FIELD_POSITION

But what I need is return one row per index, with "Fields" holding one or more segment names - e.g. run against a table with SECTIONID and KEYID fields and a compound index on those two fields, the "Fields" column in the above query would hold SECTIONID,KEYID. I can't for the life of me figure out how to do this short of adding a calculated field that runs a separate query and walks that result set building the string, which seems unnecessarily complex and top-heavy.

Is there a way to do this purely in SQL in Firebird?

SteveS
  • 335
  • 1
  • 13
  • You could try the [`LIST`](https://firebirdsql.org/file/documentation/reference_manuals/fblangref25-en/html/fblangref25-functions-aggfuncs.html#fblangref25-functions-aggfuncs-list) aggregate function, but make sure to read the caveat about order. – Mark Rotteveel Oct 21 '19 at 14:07
  • Yep, that's it! I wasn't aware of that function. Thanks, Mark. – SteveS Oct 21 '19 at 17:33

1 Answers1

1

The LIST() function is indeed the way to go, but the syntax can be a bit tricky:

SELECT
  r.RDB$INDEX_NAME AS "Index",
  CAST((SELECT LIST(TRIM(RDB$FIELD_NAME), ',')
        FROM (SELECT RDB$FIELD_NAME
              FROM RDB$INDEX_SEGMENTS
              WHERE RDB$INDEX_NAME = r.RDB$INDEX_NAME
              ORDER BY RDB$FIELD_POSITION)) AS VARCHAR(48)) AS "Fields",
FROM RDB$INDICES r
WHERE r.RDB$FOREIGN_KEY IS NULL
  AND UPPER(r.RDB$RELATION_NAME) = UPPER(':TBLNAME')
ORDER BY r.RDB$RELATION_NAME;

The CAST() wrapper is just to convert the Fields output from a text blob to simple varchar so that it's readily visible in the SQL browser. The TRIM() function call in the LIST() clause is necessary to remove excess whitespace in the output, otherwise you can get a SECTIONID ,KEYID return.

SteveS
  • 335
  • 1
  • 13
  • I would suggest casting to something a bit wider than `VARCHAR(48)`. In Firebird 3 and earlier, object names can be 31 characters and in Firebird 4 63 characters. With two or more columns in an index, it is easy to run out of characters quickly that way. – Mark Rotteveel Oct 21 '19 at 18:08
  • Mark, In a general-purpose sense, you're exactly right. In the case of the DBs that I'll be using this with, a varchar(48) is plenty of space. – SteveS Oct 21 '19 at 20:38