Start a new topic

COALESCE bug report

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?

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.

1 person likes this

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


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;

select @beftitles;

The same logic in VDB5 yields only the last title in that list.

I see no workaround in VDB5.  Any ETA on this functionality?



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.

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:

VistaDB Feature Requests

Login to post a comment