Alternative to recursive queries

[wraith808]

I've been evaluating/using VistaDB on a project I'm writing, and ran into a problem, as I detailed in the ongoing review.

I wanted to use a function to return the result of a query on a pivot table as a comma delimited list.

Code: Text [Select]

1. DECLARE @r VARCHAR(200)
2. SELECT @r = ISNULL(@r+',', '')
3.         + gt.description
4. FROM GameTags gt JOIN Game2Tag g2g ON
5.         gt.keygametag = g2g.keygametag  
6. WHERE g2g.keygame = 1
7.  
8. SELECT @r

But it only returned the last row. 

The (relevant) structure of the tables is as follows:

Games

KeyGame int

GameTags

KeyGameTag int

Description varchar(50)

Game2Tags

KeyGame2Tag int

KeyGame int

KeyGameTag int

So, I wanted to return the a comma-delimited string of the Description fields for all of the matching tags, but it returned a string with only the description of the final record.  First, does anyone see anything wrong with the SQL?  I'm pretty sure it's right, but don't have any quick way to test it currently against any other dbs.  And second, does anyone know of another way to do the same thing without cursors?

[Ath]

I'm a bit worried about this part: @r = ISNULL(@r+',', '') + gt.description

Doing a lookup of the syntax leads me to this.
I'd be thinking more in the line of

Code: Text [Select]

1. DECLARE @r VARCHAR(200)
2.     SELECT @r = CASE WHEN @r is NULL THEN ''
3.            ELSE  @r + ','
4.            END,
5.          @r = @r + gt.description
6.     FROM GameTags gt JOIN Game2Tag g2g ON
7.     gt.keygametag = g2g.keygametag  
8.     WHERE g2g.keygame = 1
9.      
10.     SELECT @r

Tested with 3 records gave: Game 1,Game 2,Third game

[Ath]

Hm, did some more testing (using SQL 2008 ) and indeed your original query works just OK.
So it's probably an anomaly of VistaDB that it won't work there. Maybe my alternative syntax does?

[wraith808]

No... VistaDB doesn't allow the use of recursive queries, so yours wouldn't work either.  The IsNull just packages up your case statement, but works fine.  The reason for that is that anything + null = null, so the first time through, it just adds an empty string to the query.

But thanks for verifying that for me

[Ath]

VistaDB doesn't allow the use of recursive queries

-wraith808 (May 29, 2011, 10:59 AM)

Oh, well, not so great Transact-SQL compatibility then

[wraith808]

VistaDB doesn't allow the use of recursive queries

-wraith808 (May 29, 2011, 10:59 AM)

Oh, well, not so great Transact-SQL compatibility then

-Ath (May 29, 2011, 12:11 PM)

Oh, yes... It actually *does* have great T-SQL compatibility... read the review for more on that.  It just has a few incompatibilities, and they're straightforward* with what isn't compatible, and why.  Sometimes, things just haven't been tested, but so far, a quick search on their forums or a post, and they've responded.  

*Ref:
1. Unsupported SQL Server Features and Syntax
2. Differences from SQL Server
3. Unsupported syntax
4. SQL Server Foreign Key Differences