I tried to migrate my SQL Server Project into VistaDB and I noticed that VistaDB COALESCE function does not behave like the SQL Server one.
I have the next function
DECLARE @InvoicesList VARCHAR(1024) SELECT @InvoicesList = COALESCE(@InvoicesList + ', ', '') + pay.[InvoiceNumber] FROM Payments AS pay WHERE pay.Order_id = @orderId ORDER BY pay.[InvoiceNumber] RETURN @InvoicesList
When I run it using SQL Server it returns a comma delimited string for example: 0001, 0002, 0003...
When I run it using VistaDB it just returns an empty string.
Is it a bug?
As a general rule, if we're doing something different than SQL it's a bug. That said, in this case I think the issue isn't COALESCE but you're updating a running variable, which is something VistaDB doesn't support at this time. Ideally this would be a feature request to add support for running variables in SELECT statements. I can recall one other person asking for that and we can see it fills a need, so a feature request is the best way to stand up and be counted for it!
Thank you for the clarification.
Hi. I have a real need for this functionality (or the FOR XML PATH('') variant) as well.
We have a database of animals. The animal can have 0..N titles, which are lined up before and or after the animal name.
So we have an animal table, ID and name (there are other fields but not relevant).
We have a Title table. ID , characters in title, before/after name flag.
We have an Animal Titles table, ID, Animal ID, Title ID,
This logic absolutely, in SQL Server 2005-2014, does retrieve a list of titles for an animal that go before the name, as a single field, separated by spaces:
declare @BefTitles nvarchar(1000) = '';
@beftitles = COALESCE(@BefTitles + ' ', '') + T.T_CHARS
from D_AN_TITLE AT
inner join D_TITLE T
on T.T_SER = AT.AT_T_ID
where AT.AT_AN_ID = 5
and T_POS = 'B'
order by AT_AN_ID, T_POS DESC, AT_ID;
The same logic in VDB5 yields only the last title in that list.
I see no workaround in VDB5. Any ETA on this functionality?
We've done some analysis, enough to determine this will be difficult to implement and so we haven't slipped it into 5.1 or 5.2 but it is something we want to do - because when you need it, you need it.
I'd appreciate it if you could author a feature request for allowing running totals in variables which you and others can vote up. That helps us keep track of what real customers are requesting and how many folks want it, plus lets us indicate what we're making progress on.
Certainly - Having a touch of trouble locating the Feature request page...
Feature Requests is a peer to this forum section, here's a direct link: