Calculating subtotals in informix
The poster wanted Red Brick-style subtotal/ROLLUP output (per the Red Brick docs) in Informix and found the syntax didn't work. Replies explained that the subtotal syntax is a non-standard Red Brick extension not available in IDS. Two workarounds were offered: write a stored procedure that loops through the rows, keeps a running total and emits a subtotal row with RETURN...WITH RESUME when the grouping column changes; or use a UNION of the detail rows with a GROUP BY SUM query, adding a rec_type column and ordering by group then rec_type. The poster confirmed both suggestions solved his problem.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hi, I wanna calculate subtotals in informix to have some results like that link http://publib.boulder.ibm.com/infocenter/rbhelp/v6r3/index.jsp?topic=/com.ibm.re dbrick.doc6.3/ssg/ssg30.htm i tried the same sintax but didnt work. Anyone can help me?
Maybe you should use Informix syntax, instead of Redbrick: http://publib.boulder.ibm.com/infocenter/idshelp/v115/index.jsp > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of > BRUNO DINIZ > Sent: Wednesday, August 11, 2010 3:34 PM > To: ids@iiug.org > Subject: Calculating subtotals in informix [20851] > > Hi, > I wanna calculate subtotals in informix to have some results like that > link > > > http://publib.boulder.ibm.com/infocenter/rbhelp/v6r3/index.jsp?topic=/c > om.ibm.redbrick.doc6.3/ssg/ssg30.htm > > i tried the same sintax but didnt work. > > Anyone can help me? > > > *********************************************************************** > ******** > Forum Note: Use "Reply" to post a response in the discussion forum.
yeah. Thanx, but i knew about that link and didn´t find any of subtotals.
BRUNO DINIZ wrote: > yeah. Thanx, but i knew about that link and didn´t find any of subtotals. I'd like to define a Management Class in Informix, as per this link: http://publib.boulder.ibm.com/infocenter/tivihelp/v1r1/topic/com.ibm.itsm390n.do c/anrzgd5502.htm#ToC_482 The fact that that it's a totally different piece of software to Informix is surely not my problem. -- Cheers, Obnoxio The Clown http://obotheclown.blogspot.com I will now proceed to pleasure myself with this fish. -- This message has been scanned for viruses and dangerous content by OpenProtect(http://www.openprotect.com), and is believed to be clean.
Can't do that in Informix. The syntax in Red Brick is not standard SQL, itis a Red brick extension. You could use a stored procedure to effect the desired result however. Fetch the data in a loop, calculate the sub-total as a running value, when the group column(s) change return the sub-total row with resume before resetting the sub-total to the value of the new row and returning the new first row of the new group. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Wed, Aug 11, 2010 at 4:34 PM, BRUNO DINIZ < brunodiniz@regionalmolas.com.br> wrote: > Hi, > I wanna calculate subtotals in informix to have some results like that link > > > > http://publib.boulder.ibm.com/infocenter/rbhelp/v6r3/index.jsp?topic=/com.ibm.re dbrick.doc6.3/ssg/ssg30.htm > > i tried the same sintax but didnt work. > > Anyone can help me? > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --e0cb4e8878e31e5679048d92ea91
A simple subtotal example: SELECT 1 rec_type, column1, column2, column3, column4 FROM my_table UNION SELECT 2 rec_type, SUM(column1), SUM(column2), SUM(column3), column4 FROM my_table GROUP BY column4 ORDER BY column4, rec_type > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of > Obnoxio The Clown > Sent: Wednesday, August 11, 2010 4:08 PM > To: ids@iiug.org > Subject: Re: Calculating subtotals in informix [20854] > > BRUNO DINIZ wrote: > > yeah. Thanx, but i knew about that link and didn´t find any of > subtotals. > > I'd like to define a Management Class in Informix, as per this link: > > http://publib.boulder.ibm.com/infocenter/tivihelp/v1r1/topic/com.ibm.it > sm390n.doc/anrzgd5502.htm#ToC_482 > > The fact that that it's a totally different piece of software to > Informix is surely not my problem. > > -- > Cheers, > Obnoxio The Clown > > http://obotheclown.blogspot.com > I will now proceed to pleasure myself with this fish. > > -- > This message has been scanned for viruses and > dangerous content by OpenProtect(http://www.openprotect.com), and is > believed to be clean. > > > *********************************************************************** > ******** > Forum Note: Use "Reply" to post a response in the discussion forum.
thnx, solved!! yours and ark answers, worked for me