Home | Blog | Software | Reviews and Features | Forum | Help | Donate | About us
topbanner_forum
  *

avatar image

Welcome, Guest. Please login or register.
Did you miss your activation email?

Login with username, password and session length
  • December 08, 2016, 06:04:46 AM
  • Proudly celebrating 10 years online.
  • Donate now to become a lifetime supporting member of the site and get a non-expiring license key for all of our programs.
  • donate

Author Topic: Alternative to recursive queries  (Read 2354 times)

wraith808

  • Supporting Member
  • Joined in 2006
  • **
  • Posts: 8,406
  • "In my dreams, I always do it right."
    • View Profile
    • Donate to Member
Alternative to recursive queries
« on: May 28, 2011, 03:32:06 PM »
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

  • Supporting Member
  • Joined in 2006
  • **
  • Posts: 2,783
    • View Profile
    • Donate to Member
Re: Alternative to recursive queries
« Reply #1 on: May 29, 2011, 04:26:55 AM »
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

  • Supporting Member
  • Joined in 2006
  • **
  • Posts: 2,783
    • View Profile
    • Donate to Member
Re: Alternative to recursive queries
« Reply #2 on: May 29, 2011, 04:48:06 AM »
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

  • Supporting Member
  • Joined in 2006
  • **
  • Posts: 8,406
  • "In my dreams, I always do it right."
    • View Profile
    • Donate to Member
Re: Alternative to recursive queries
« Reply #3 on: May 29, 2011, 10:59:14 AM »
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

  • Supporting Member
  • Joined in 2006
  • **
  • Posts: 2,783
    • View Profile
    • Donate to Member
Re: Alternative to recursive queries
« Reply #4 on: May 29, 2011, 12:11:40 PM »
VistaDB doesn't allow the use of recursive queries
Oh, well, not so great Transact-SQL compatibility then :(

wraith808

  • Supporting Member
  • Joined in 2006
  • **
  • Posts: 8,406
  • "In my dreams, I always do it right."
    • View Profile
    • Donate to Member
Re: Alternative to recursive queries
« Reply #5 on: May 29, 2011, 12:32:15 PM »
VistaDB doesn't allow the use of recursive queries
Oh, well, not so great Transact-SQL compatibility then :(

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