Oninit cdi / Archive
Talk to an expert →

Recursive SQL?

Posted in 2008

Summary

User asked whether Informix IDS 11 supports recursive SQL queries for building tree structures from hierarchical data, noting that DB2 supports this feature. Madison Pruet responded (in base64) suggesting DataBlade node handles this as a freebie in IDS 11. No detailed syntax or further resolution was provided in the thread.

Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.

Topics: General Discussion

BRYCE STENBERG — — source: IIUG Forums & Mailing Lists

Hi, Can I do recursive SQL queries in IDS11, as each row in my table can reference a child row, and I’m trying to build a tree to a flat file? (when searching I see that db2 can handle recursive sql’s, hoping Informix does as well). If it is possible, what does the syntax look like? Regards, Bryce Stenberg

↪ replying to BRYCE STENBERG
Madison Pruet — — source: IIUG Forums & Mailing Lists

Doesn't the node Datablade handle this? I think that's a freebie in IDS11. alan? ------------------------------------- Madison Pruet, STSM IDS Replication Architect "BRYCE STENBERG" <bryce@hrnz.co.nz > To Sent by: ids@iiug.org ids-bounces@iiug. cc org Subject Recursive SQL? [10919] 01/15/2008 04:01 PM Please respond to ids@iiug.org Hi, Can I do recursive SQL queries in IDS11, as each row in my table can reference a child row, and I’m trying to build a tree to a flat file? (when searching I see that db2 can handle recursive sql’s, hoping Informix does as well). If it is possible, what does the syntax look like? Regards, Bryce Stenberg ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum. See you at the IIUG Informix 2008 Conference The Power Conference for Informix Professionals April 27 - 30, 2008 Marriott Overland Park (Kansas City), Kansas http://www.iiug.org/conf Registration Now Open!!

↪ replying to Madison Pruet
Robert Roussey(IT) — — source: IIUG Forums & Mailing Lists

Oh man that's ugly! :) Bob Roussey Unix / Informix Administration Spirit Airlines Robert.Roussey@SpiritAir.com -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Madison Pruet Sent: Tuesday, January 15, 2008 5:08 PM To: ids@iiug.org Subject: Re: Recursive SQL? [10920] Doesn't the node Datablade handle this? I think that's a freebie in IDS11. alan? ------------------------------------- Madison Pruet, STSM IDS Replication Architect "BRYCE STENBERG" <bryce@hrnz.co.nz > To Sent by: ids@iiug.org ids-bounces@iiug. cc org Subject Recursive SQL? [10919] 01/15/2008 04:01 PM Please respond to ids@iiug.org Hi, Can I do recursive SQL queries in IDS11, as each row in my table can reference a child row, and I’m trying to build a tree to a flat file? (when searching I see that db2 can handle recursive sql’s, hoping Informix does as well). If it is possible, what does the syntax look like? Regards, Bryce Stenberg ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum. See you at the IIUG Informix 2008 Conference The Power Conference for Informix Professionals April 27 - 30, 2008 Marriott Overland Park (Kansas City), Kansas http://www.iiug.org/conf Registration Now Open!! ************************************************************************ ******* Forum Note: Use "Reply" to post a response in the discussion forum. See you at the IIUG Informix 2008 Conference The Power Conference for Informix Professionals April 27 - 30, 2008 Marriott Overland Park (Kansas City), Kansas http://www.iiug.org/conf Registration Now Open!!

↪ replying to Robert Roussey(IT)
Scott Mackenzie — — source: IIUG Forums & Mailing Lists

curs-ive, not re-curs-ive. or was that curs-ed? - ScottM -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Robert Roussey(IT) Sent: Tuesday, January 15, 2008 3:26 PM To: ids@iiug.org Subject: RE: Recursive SQL? [10921] Oh man that's ugly! :) Bob Roussey Unix / Informix Administration Spirit Airlines Robert.Roussey@SpiritAir.com -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Madison Pruet Sent: Tuesday, January 15, 2008 5:08 PM To: ids@iiug.org Subject: Re: Recursive SQL? [10920] Doesn't the node Datablade handle this? I think that's a freebie in IDS11. alan? ------------------------------------- Madison Pruet, STSM IDS Replication Architect "BRYCE STENBERG" <bryce@hrnz.co.nz > To Sent by: ids@iiug.org ids-bounces@iiug. cc org Subject Recursive SQL? [10919] 01/15/2008 04:01 PM Please respond to ids@iiug.org Hi, Can I do recursive SQL queries in IDS11, as each row in my table can reference a child row, and I’m trying to build a tree to a flat file? (when searching I see that db2 can handle recursive sql’s, hoping Informix does as well). If it is possible, what does the syntax look like? Regards, Bryce Stenberg ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum. See you at the IIUG Informix 2008 Conference The Power Conference for Informix Professionals April 27 - 30, 2008 Marriott Overland Park (Kansas City), Kansas http://www.iiug.org/conf Registration Now Open!! ************************************************************************ ******* Forum Note: Use "Reply" to post a response in the discussion forum. See you at the IIUG Informix 2008 Conference The Power Conference for Informix Professionals April 27 - 30, 2008 Marriott Overland Park (Kansas City), Kansas http://www.iiug.org/conf Registration Now Open!! ************************************************************************ ******* Forum Note: Use "Reply" to post a response in the discussion forum. See you at the IIUG Informix 2008 Conference The Power Conference for Informix Professionals April 27 - 30, 2008 Marriott Overland Park (Kansas City), Kansas http://www.iiug.org/conf Registration Now Open!!

↪ replying to BRYCE STENBERG
Art S. Kagel (Oninit LLC) — — source: IIUG Forums & Mailing Lists

BRYCE STENBERG wrote: > Hi, > > Can I do recursive SQL queries in IDS11, as each row in my table can reference > a child row, and I’m trying to build a tree to a flat file? > Not natively. But, with the node datablade, it's possible. Well, I've done it in pure Informix, but using a programming language to implement the retrieval. You can't do it in pure SQL in Informix without the node datablade. Node datablade, IB is a free download. Art S. Kagel > (when searching I see that db2 can handle recursive sql’s, hoping Informix > does as well). > > If it is possible, what does the syntax look like? > > Regards, > > Bryce Stenberg > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > > ------------------------------------------------------------------------ > > See you at the IIUG Informix 2008 Conference > The Power Conference for Informix Professionals > April 27 - 30, 2008 Marriott Overland Park (Kansas City), Kansas > http://www.iiug.org/conf > Registration Now Open!! ================================================================================ =========== Please access the attached hyperlink for an important electronic communications disclaimer: http://www.oninit.com/home/disclaimer.php ================================================================================ ===========

↪ replying to Art S. Kagel (Oninit LLC)
BRYCE STENBERG — — source: IIUG Forums & Mailing Lists

Thanks Art, I'll go and find that datablade and give it a go. Regards, Bryce.

↪ replying to Art S. Kagel (Oninit LLC)
Mark Jamison — — source: IIUG Forums & Mailing Lists

Just FYI, with version 11, it's not a free download, its a free blade that comes with the engine. ----- Original Message ---- From: Art S. Kagel (Oninit LLC) <art@oninit.com> To: ids@iiug.org Sent: Tuesday, January 15, 2008 5:39:13 PM Subject: Re: Recursive SQL? [10923] BRYCE STENBERG wrote: > Hi, > > Can I do recursive SQL queries in IDS11, as each row in my table can reference > a child row, and I¢m trying to build a tree to a flat file? > Not natively. But, with the node datablade, it's possible. Well, I've done it in pure Informix, but using a programming language to implement the retrieval. You can't do it in pure SQL in Informix without the node datablade. Node datablade, IB is a free download. Art S. Kagel > (when searching I see that db2 can handle recursive sql¢s, hoping Informix > does as well). > > If it is possible, what does the syntax look like? > > Regards, > > Bryce Stenberg > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > > ------------------------------------------------------------------------ > > See you at the IIUG Informix 2008 Conference > The Power Conference for Informix Professionals > April 27 - 30, 2008 Marriott Overland Park (Kansas City), Kansas > http://www.iiug.org/conf > Registration Now Open!! ================================================================================ =========== Please access the attached hyperlink for an important electronic communications disclaimer: http://www.oninit.com/home/disclaimer.php ================================================================================ =========== ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

↪ replying to Mark Jamison
Paul Watson — — source: IIUG Forums & Mailing Lists

If you use the blade from IIUG site it will core at a depth of 16 unless you tweak the code, I don't believe the fix is the IIUG code Paul Watson Tel: +1 913-400-2620 Mob: +1 913-387-7529 Web: www.oninit.com Failure is not as frightening as regret. If you want to improve, be content to be thought foolish and stupid. > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of > Mark Jamison > Sent: 15 January 2008 22:06 > To: ids@iiug.org > Subject: Re: Recursive SQL? [10925] > > Just FYI, with version 11, it's not a free download, its a free blade > that comes with the engine. > > ----- Original Message ---- > From: Art S. Kagel (Oninit LLC) <art@oninit.com> > To: ids@iiug.org > Sent: Tuesday, January 15, 2008 5:39:13 PM > Subject: Re: Recursive SQL? [10923] > > BRYCE STENBERG wrote: > > Hi, > > > > Can I do recursive SQL queries in IDS11, as each row in my table can > reference > > a child row, and I"m trying to build a tree to a flat file? > > > > Not natively. But, with the node datablade, it's possible. Well, I've > done it in pure Informix, but using a programming language to implement > > the retrieval. You can't do it in pure SQL in Informix without the node > > datablade. Node datablade, IB is a free download. > > Art S. Kagel > > > (when searching I see that db2 can handle recursive sql"s, hoping > Informix > > does as well). > > > > If it is possible, what does the syntax look like? > > > > Regards, > > > > Bryce Stenberg > > > > > > > > *********************************************************************** > ******** > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > > ----------------------------------------------------------------------- > - > > > > See you at the IIUG Informix 2008 Conference The Power Conference for > > Informix Professionals April 27 - 30, 2008 Marriott Overland Park > > (Kansas City), Kansas http://www.iiug.org/conf Registration Now > Open!! > > > ======================================================================= > ==================== > Please access the attached hyperlink for an important electronic > communications disclaimer: > > http://www.oninit.com/home/disclaimer.php > > > ======================================================================= > ==================== > > > *********************************************************************** > ******** > Forum Note: Use "Reply" to post a response in the discussion forum. > > > *********************************************************************** > ******** > Forum Note: Use "Reply" to post a response in the discussion forum. ================================================================================ =========== Please access the attached hyperlink for an important electronic communications disclaimer: http://www.oninit.com/home/disclaimer.php ================================================================================ ===========