Having trouble using DISTINCT in LIST()

[NebbyOtt]

Some Background Info:
I'm using LibreOffice Base 25.2.1.2 Flatpak in Experimental mode using Firebird on Arch Linux
(Because I couldn't find a way to do string concatenation, or in any way nicely organize the data the way I needed in HSQLDB)
I've got two tables, one called "Job Details" which holds a Job ID (PK), and relevant info, and one called "Materials" With ItemID (PK), Job ID (Job Details.Job ID FK), Vendor (VARCHAR(100)), Description, and Cost Columns. For each Job, all purchases made for the job are to be entered into Materials, with the Job ID referencing the Job it was purchased for.

The Problem:
I'm working on a Query to nicely concatenate the data for a report, all material Descriptions and Cost under the same Job ID are turned into a nice list in one entry, and the Vendors, which can repeat, I want to be listed such that they do not repeat. Every scrap of documentation I can find says that I can use SELECT ... LIST(DISTINCT Vendor), but i've tried in every way I can think of and adding DISTINCT always gives me a generic non-specific error. If i switch it to COUNT(DISTINCT Vendor) It works, but List, documented as accepting DISTINCT in version 3, 4, and 5, doesn't seem to? How can I fix this, or circumvent the issue? Here is my SQL for this Query

SELECT "Job ID", LIST(DISTINCT "Vendor" , ASCII_CHAR( 10 ) ) AS "Vendors", LIST("Item Description" || ': ' ||'$'|| "Item Cost", ASCII_CHAR( 10 ) ) AS "Items"
FROM "Materials"
GROUP BY "Job ID"
ORDER BY "Job ID" ASC

EDIT: Just confirmed this is Firebird 3.0.7

[hvlad]

I see no column Vendor in any table described

[NebbyOtt]

3rd Column in Materials. Honestly context doesn't matter, Any instance I try to use LIST(DISTINCT x) in leads to the same result.

[mrotteveel]

What is the error you get? IIRC, LibreOffice Base does some syntax conversion itself for compatibility with the HSQLDB syntax, and this sometimes stumbles. IIRC, you may need to check the "Run SQL command directly" option to avoid this translation. But as Vlad indicated, your reproduction example is incomplete.

[NebbyOtt]

The Vendors Column in my post was actually missing a few hours ago but I quickly edited and updated the post to fix that. Either everyone is breezing by it or Github didn't update my post for anyone but me.

and one called "Materials" With ItemID (PK), Job ID (Job Details.Job ID FK), Vendor (VARCHAR(100)), Description, and Cost Columns.

[NebbyOtt]

I didn't consiter running the SQL directly, When run in directly and not as a query... it succeeds... Things that should work but don't have been the bane of my existance today. To be honest, I found a work around for this problem but now I have a problem of the lists loosing all their newlines (ASCII_CHAR(10)) when converting to VARCHAR, if I don't convert to VARCHAR, the CLOBs just display in the reports as their pointers? I think? I'm feeling pretty defeated.

[mrotteveel]

That "Syntax error in SQL statement" is not a Firebird error, but one from LibreOffice, so I guess it's from that compatibility layer. The same likely goes for it displaying the blob ids instead of the blob value in a report. You'll should report it to LibreOffice (see How to Report Bugs in LibreOffice)

[NebbyOtt]

Well, the thing is, I found an issue report for this exact problem... from 2018. They're not going to fix it, I don't know why. I can throw in my two cents but I highly doubt it'll get me anywhere.