Re: [firebird-support] How write a query with a progressive sum field
Hallo, Il 08/03/2016 20.36, setysvar setys...@gmail.com [firebird-support] ha scritto: > This will not work properly if more than one row of DC are joined to > the same DT (but then your ORDER BY isn't 100% deterministic). If you > change the ordering of your query, you also have to change the > subselect (e.g. if you add DESC you have to change from > to <). If > this doesn't fit, is too slow or too difficult to understand, I'd > recommend EXECUTE BLOCK (as Sean already wrote). HTH, Thank You, the query suggested works but because of the primary index of DOC_TESTA the value calculated of SALDO is not compliant with the result of the rows of the query. I solved by creating a stored procedure like this: AS BEGIN SALDO = 0; FOR SELECT DT.DATA, DT.DOCUMENTO_ID, DT.NUMERO, DT.SERIE, DC.CARICO, DC.SCARICO, DC.CARICO - DC.SCARICO + :SALDO, from DOC_TESTA DT JOIN DOC_CORPO DC on DT.ID = DC.DOC_TESTA_ID WHERE DC.ARTICOLO_ID = :ID ORDER BY DT.DATA, DT.DOCUMENTO_ID, DT.NUMERO, DT.SERIE INTO :DATADOCUMENTO, :DOCUMENTO_ID, :NUMERO, :SERIE, :CARICO, :SCARICO, :SALDO, DO SUSPEND; END^ Thanks to all :) -- Luigi Siciliano -- ++ Visit http://www.firebirdsql.org and click the Documentation item on the main (top) menu. Try FAQ and other links from the left-side menu there. Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ ++ Yahoo Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com <*> To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com <*> Your use of Yahoo Groups is subject to: https://info.yahoo.com/legal/us/yahoo/utos/terms/
Re: [firebird-support] How write a query with a progressive sum field
Den 07.03.2016 11:16, skrev Luigi Siciliano luigi...@tiscalinet.it [firebird-support]: > Hallo, > I need to write a query with a computed field that contain a > progressive sum like this table: > > DATA DOCUMENTO_ID NUMERO SERIE CARICO SCARICO SALDO > 01/01 A 1 A > 10 1 > 02/01 A 2 A > 30 4 > 02/01 V 33 B > 02 2 > 02/01 V 35 C > 01 1 > > I need to modify this query by adding SALDO field to get progressive sum > of CARICO - SCARICO: > select > DT.DATA, > DT.DOCUMENTO_ID, > DT.NUMERO, > DT.SERIE, > DC.CARICO, > DC.SCARICO > from > DOC_TESTA DT > JOIN DOC_CORPO DC on DT.ID = DC.DOC_TESTA_ID > WHERE > DC.ARTICOLO_ID = :ID > ORDER BY > DATA, > DOCUMENTO_ID, > NUMERO, > SERIE > > > How I do? > > Thanks Maybe this can be solved like this: select DT.DATA, DT.DOCUMENTO_ID, DT.NUMERO, DT.SERIE, DC.CARICO, DC.SCARICO, (SELECT SUM(DC2.CARICO-DC2.SCARICO) FROM DOC_TESTA DT2 JOIN DOC_CORPO DC2 on DT2.ID = DC2.DOC_TESTA_ID WHERE DC.ARTICOLO_ID = DC2.ARTICOLO_ID AND (DT.DATA > DT2.DATA OR (D2.DATA = DT2.DATA AND (DT.DOCUMENTO_ID > DT2.DOCUMENTO_ID OR (DT.DOCUMENTO_ID = DT2.DOCUMENTO_ID AND (DT.NUMERO > DT2.NUMERO OR (DT.NUMERO = DT2.NUMERO AND DT.SERIE >= DT2.SERIE))) SALDO from DOC_TESTA DT JOIN DOC_CORPO DC on DT.ID = DC.DOC_TESTA_ID WHERE DC.ARTICOLO_ID = :ID ORDER BY DT.DATA, DT.DOCUMENTO_ID, DT.NUMERO, DT.SERIE This will not work properly if more than one row of DC are joined to the same DT (but then your ORDER BY isn't 100% deterministic). If you change the ordering of your query, you also have to change the subselect (e.g. if you add DESC you have to change from > to <). If this doesn't fit, is too slow or too difficult to understand, I'd recommend EXECUTE BLOCK (as Sean already wrote). HTH, Set ++ Visit http://www.firebirdsql.org and click the Documentation item on the main (top) menu. Try FAQ and other links from the left-side menu there. Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ ++ Yahoo Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com <*> To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com <*> Your use of Yahoo Groups is subject to: https://info.yahoo.com/legal/us/yahoo/utos/terms/
Re: [firebird-support] How write a query with a progressive sum field
LSSBcfs> It has not! LSSBcfs> RC releases should not be used for production purposes. Just a note: FB 3 RC2 will become the final release, unless someone finds a critical bug or regression that would require another RC. []s Carlos www.firebirdnews.org - www.FireBase.com.br
Re: [firebird-support] How write a query with a progressive sum field
On 07/03/16 17:10, 'Leyne, Sean' s...@broadviewsoftware.com [firebird-support] wrote: >> I simply noted that V3 RC should >> > be treated as not production-ready (like any other RC in Firebird's >> > history), >> > but perhaps the Firebird project philosophy changed. > It has not! > > RC releases should not be used for production purposes. That 'progressive sum field' is a new feature coming up in FB3 is a useful piece of information. Assuming that people are already using it is the part that was not helpful here, so including the statement that it required FB3 would have helped? Certainly any FB3 related problems should be reported on the development list until such time as FB3 has been formally released. As for pre-FB3 installations ... I have a few mechanisms that rely on CTE queries to produce results, but I don't think they produce a clean progressive sum ... that is handled in the display routine in my PHP applications, so I don't need the query to produce it directly anyway. -- Lester Caine - G8HFL - Contact - http://lsces.co.uk/wiki/?page=contact L.S.Caine Electronic Services - http://lsces.co.uk EnquirySolve - http://enquirysolve.com/ Model Engineers Digital Workshop - http://medw.co.uk Rainbow Digital Media - http://rainbowdigitalmedia.co.uk ++ Visit http://www.firebirdsql.org and click the Documentation item on the main (top) menu. Try FAQ and other links from the left-side menu there. Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ ++ Yahoo Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com <*> To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com <*> Your use of Yahoo Groups is subject to: https://info.yahoo.com/legal/us/yahoo/utos/terms/
Re: [firebird-support] How write a query with a progressive sum field
Il 07/03/2016 17.28, Thomas Steinmaurer t...@iblogmanager.com [firebird-support] ha scritto: > Although not that flexible as an ad-hoc query, but perhaps your > particular use case can be achieved with a selectable stored procedure > ... Thank you Thomas, I modified my query like this: select DT.DATA, DT.DOCUMENTO_ID, DT.NUMERO, DT.SERIE, DC.CARICO, DC.SCARICO, SUM(dc2.carico - DC2.SCARICO) AS saldo from DOC_TESTA DT JOIN DOC_CORPO DC on DT.ID = DC.DOC_TESTA_ID left JOIN DOC_CORPO DC2 ON (DC.DOC_TESTA_ID >= DC2.DOC_TESTA_ID) AND (DC.ARTICOLO_ID = DC2.ARTICOLO_ID) WHERE DC.ARTICOLO_ID = :ID GROUP BY DATA, DOCUMENTO_ID, NUMERO, SERIE ORDER BY DATA, DOCUMENTO_ID, NUMERO, SERIE but I have an issue: if I have a row with CARICO = 0 and SCARICO = 0, SALDO is added or subtracted by the number present in last row with CARICO > 0 or SCARICO > 0 if I add "(AND(DC.CARICO + DC.SCARICO) > 0) IN a LEFT JOIN I obtain almost correct result but the rows that contain "CARICO = 0 and SCARICO = 0" now contain SALDO = NULL, the others contains correct progressive sum. What I'm wrong? Thanks -- Luigi Siciliano -- -- Luigi Siciliano -- ++ Visit http://www.firebirdsql.org and click the Documentation item on the main (top) menu. Try FAQ and other links from the left-side menu there. Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ ++ Yahoo Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com <*> To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com <*> Your use of Yahoo Groups is subject to: https://info.yahoo.com/legal/us/yahoo/utos/terms/
RE: [firebird-support] How write a query with a progressive sum field
> I'm sure, IBReplicator does its job very well. I simply noted that V3 RC > should > be treated as not production-ready (like any other RC in Firebird's history), > but perhaps the Firebird project philosophy changed. It has not! RC releases should not be used for production purposes. > > If he is able to write queries to the database, he knows for sure > > which tables he needs. Extract definitions of these tables in > > FlameRobin or IBExpert is easy. Create a new database with these > > tables inside and copy data for these tables is also easy. After that > > setting > up replication will take less than an hour, including reading of manual. > > Na, replication is an additional (complex) layer, which needs to be > maintained, monitored etc ... Exactly my point! "Hacks" don't require consideration of these issue, "solutions" need to consider all factors (including, for many like BroadView, deployment/configuration considerations for client/install sites). This mailing list is about providing input/answers for solutions, not hacks. Sean
RE: [firebird-support] How write a query with a progressive sum field
> > I need add SALDO fields in the result of the query to get a > > progressive sum of CARICO - SCARICO. > > Although not that flexible as an ad-hoc query, but perhaps your particular > use case can be achieved with a selectable stored procedure ... A similar solution, one which could support some degree of "ad-hoc'ness" would be EXECUTE BLOCK. It would follow the same basic approach of the selectable SP, but with the ability to modify some of the logic/grouping. Sean
Re: [firebird-support] How write a query with a progressive sum field
On 07.03.2016 17:40, Dimitry Sibiryakov s...@ibphoenix.com [firebird-support] wrote: > 07.03.2016 17:21, Thomas Steinmaurer t...@iblogmanager.com [firebird-support] > wrote: Nothing prevents him from installing of an additional server for report purposes and setting up replication to it from production one. >> >> Yeah. Sure. ;-) >> > > Well, if setting up standby server for reporting purposes with > IBLogManager is too had > task, he always can try IBPhoenix Replicator which is easy to install and > configure. Oh, product flame ware. Sorry to disappoint you, but I haven't joined the thread to place product plugs. I'm getting older, hopefully more wisely and luckily, something else is feeding my family. ;-) As you started the product plug stuff, I guess it is different at your side. I'm sure, IBReplicator does its job very well. I simply noted that V3 RC should be treated as not production-ready (like any other RC in Firebird's history), but perhaps the Firebird project philosophy changed. > If he is able to write queries to the database, he knows for sure which > tables he > needs. Extract definitions of these tables in FlameRobin or IBExpert is easy. > Create a new > database with these tables inside and copy data for these tables is also > easy. After that > setting up replication will take less than an hour, including reading of > manual. Na, replication is an additional (complex) layer, which needs to be maintained, monitored etc ... -- With regards, Thomas Steinmaurer http://www.upscene.com/ Professional Tools and Services for Firebird FB TraceManager, IB LogManager, Database Health Check, Tuning etc. ++ Visit http://www.firebirdsql.org and click the Documentation item on the main (top) menu. Try FAQ and other links from the left-side menu there. Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ ++ Yahoo Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com <*> To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com <*> Your use of Yahoo Groups is subject to: https://info.yahoo.com/legal/us/yahoo/utos/terms/
Re: [firebird-support] How write a query with a progressive sum field
07.03.2016 17:21, Thomas Steinmaurer t...@iblogmanager.com [firebird-support] wrote: >> > Nothing prevents him from installing of an additional server for >> > report purposes and >> >setting up replication to it from production one. > > Yeah. Sure. ;-) > Well, if setting up standby server for reporting purposes with IBLogManager is too had task, he always can try IBPhoenix Replicator which is easy to install and configure. If he is able to write queries to the database, he knows for sure which tables he needs. Extract definitions of these tables in FlameRobin or IBExpert is easy. Create a new database with these tables inside and copy data for these tables is also easy. After that setting up replication will take less than an hour, including reading of manual. -- WBR, SD. ++ Visit http://www.firebirdsql.org and click the Documentation item on the main (top) menu. Try FAQ and other links from the left-side menu there. Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ ++ Yahoo Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com <*> To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com <*> Your use of Yahoo Groups is subject to: https://info.yahoo.com/legal/us/yahoo/utos/terms/
Re: [firebird-support] How write a query with a progressive sum field
> Il 07/03/2016 11.32, Dimitry Sibiryakov s...@ibphoenix.com > [firebird-support] ha scritto: >> sum(saldo) over (order by data) > > Thank You for your response but I can't do: sum(SALDO) because SALDO is > not a field of my table. > > I badly explained myself: I have a table with only this fields: > > DATA DOCUMENTO_ID NUMERO SERIE CARICO SCARICO > > > I need add SALDO fields in the result of the query to get a progressive > sum of CARICO - SCARICO. Although not that flexible as an ad-hoc query, but perhaps your particular use case can be achieved with a selectable stored procedure ... I'm not good in writing PSQL out of my mind at the moment, but execute your SQL (without the SUM aggregate field) in a stored procedure, iterate over the result set, remember the computation for the previous row in a local variable and use that for the addition with the current row and call SUSPEND for each result set iteration. -- With regards, Thomas Steinmaurer http://www.upscene.com/ Professional Tools and Services for Firebird FB TraceManager, IB LogManager, Database Health Check, Tuning etc. ++ Visit http://www.firebirdsql.org and click the Documentation item on the main (top) menu. Try FAQ and other links from the left-side menu there. Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ ++ Yahoo Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com <*> To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com <*> Your use of Yahoo Groups is subject to: https://info.yahoo.com/legal/us/yahoo/utos/terms/
Re: [firebird-support] How write a query with a progressive sum field
> 07.03.2016 12:55, 'Thomas Steinmaurer' t...@iblogmanager.com > [firebird-support] wrote: >> Needless to say, 3.0 is in release candidate state. Not sure if Luigi wants >> to run 3.0 in >> production yet. ;-) > > Nothing prevents him from installing of an additional server for report > purposes and > setting up replication to it from production one. Yeah. Sure. ;-) -- With regards, Thomas Steinmaurer http://www.upscene.com/ Professional Tools and Services for Firebird FB TraceManager, IB LogManager, Database Health Check, Tuning etc. ++ Visit http://www.firebirdsql.org and click the Documentation item on the main (top) menu. Try FAQ and other links from the left-side menu there. Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ ++ Yahoo Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com <*> To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com <*> Your use of Yahoo Groups is subject to: https://info.yahoo.com/legal/us/yahoo/utos/terms/
Re: [firebird-support] How write a query with a progressive sum field
07.03.2016 16:57, 'Leyne, Sean' s...@broadviewsoftware.com [firebird-support] wrote: > You think that users can spin up servers/replication on a whim to solve a > problem in a > deployed system??? A lot of them does so. -- WBR, SD. ++ Visit http://www.firebirdsql.org and click the Documentation item on the main (top) menu. Try FAQ and other links from the left-side menu there. Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ ++ Yahoo Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com <*> To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com <*> Your use of Yahoo Groups is subject to: https://info.yahoo.com/legal/us/yahoo/utos/terms/
RE: [firebird-support] How write a query with a progressive sum field
> > Needless to say, 3.0 is in release candidate state. Not sure if Luigi > > wants to run 3.0 in production yet. ;-) > >Nothing prevents him from installing of an additional server for report > purposes and setting up replication to it from production one. Seriously!?? Do you read your posts before you send them? You think that users can spin up servers/replication on a whim to solve a problem in a deployed system??? What about managing such a deployment? How about you spend more time providing real life answers/solutions to issues/problems posted here. Sean
Re: [firebird-support] How write a query with a progressive sum field
On 2016-03-07 11:32, Dimitry Sibiryakov s...@ibphoenix.com [firebird-support] wrote: > 07.03.2016 11:16, Luigi Siciliano luigi...@tiscalinet.it > [firebird-support] wrote: >> How I do? > >sum(saldo) over (order by data) That only works with Firebird 3. Mark
Re: [firebird-support] How write a query with a progressive sum field
07.03.2016 12:53, liviuslivius liviusliv...@poczta.onet.pl [firebird-support] wrote: > > Not so hurry.. FB3 is on RC2 stage not final. According to the announce it is stable enough for reporting/OLAP server. -- WBR, SD. ++ Visit http://www.firebirdsql.org and click the Documentation item on the main (top) menu. Try FAQ and other links from the left-side menu there. Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ ++ Yahoo Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com <*> To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com <*> Your use of Yahoo Groups is subject to: https://info.yahoo.com/legal/us/yahoo/utos/terms/
Re: [firebird-support] How write a query with a progressive sum field
07.03.2016 12:55, 'Thomas Steinmaurer' t...@iblogmanager.com [firebird-support] wrote: > Needless to say, 3.0 is in release candidate state. Not sure if Luigi wants > to run 3.0 in > production yet. ;-) Nothing prevents him from installing of an additional server for report purposes and setting up replication to it from production one. -- WBR, SD. ++ Visit http://www.firebirdsql.org and click the Documentation item on the main (top) menu. Try FAQ and other links from the left-side menu there. Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ ++ Yahoo Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com <*> To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com <*> Your use of Yahoo Groups is subject to: https://info.yahoo.com/legal/us/yahoo/utos/terms/
Re: [firebird-support] How write a query with a progressive sum field
> 07.03.2016 12:29, Luigi Siciliano luigi...@tiscalinet.it [firebird-support] > wrote: >> Ok, I understand but, "over" is unknow in firebird 2.5.5. > > Upgrade to 3.0. Needless to say, 3.0 is in release candidate state. Not sure if Luigi wants to run 3.0 in production yet. ;-) -- With regards, Thomas Steinmaurer http://www.upscene.com Professional Tools and Services for Firebird FB TraceManager, IB LogManager, Database Health Check, Tuning etc. > -- > WBR, SD. > > > > > > > ++ > > Visit http://www.firebirdsql.org and click the Documentation item > on the main (top) menu. Try FAQ and other links from the left-side menu > there. > > Also search the knowledgebases at > http://www.ibphoenix.com/resources/documents/ > > > ++ > > > Yahoo Groups Links > > >
Re: Re: [firebird-support] How write a query with a progressive sum field
> 07.03.2016 12:29, Luigi Siciliano luigi...@tiscalinet.it [firebird-support] > wrote: > > Ok, I understand but, "over" is unknow in firebird 2.5.5. > >Upgrade to 3.0. > > -- >WBR, SD. Not so hurry.. FB3 is on RC2 stage not final. regards, Karol Bieniaszewski
Re: [firebird-support] How write a query with a progressive sum field
07.03.2016 12:29, Luigi Siciliano luigi...@tiscalinet.it [firebird-support] wrote: > Ok, I understand but, "over" is unknow in firebird 2.5.5. Upgrade to 3.0. -- WBR, SD. ++ Visit http://www.firebirdsql.org and click the Documentation item on the main (top) menu. Try FAQ and other links from the left-side menu there. Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ ++ Yahoo Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com <*> To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com <*> Your use of Yahoo Groups is subject to: https://info.yahoo.com/legal/us/yahoo/utos/terms/
Re: [firebird-support] How write a query with a progressive sum field
Il 07/03/2016 11.58, Dimitry Sibiryakov s...@ibphoenix.com [firebird-support] ha scritto: > sum(carcio-scarcio) over () as SALDO. Ok, I understand but, "over" is unknow in firebird 2.5.5. I modified my query like this: select DT.DATA, DT.DOCUMENTO_ID, DT.NUMERO, DT.SERIE, DC.CARICO, DC.SCARICO, SUM(dc2.carico - DC2.SCARICO) AS saldo from DOC_TESTA DT JOIN DOC_CORPO DC on DT.ID = DC.DOC_TESTA_ID left JOIN DOC_CORPO DC2 ON (DC.DOC_TESTA_ID >= DC2.DOC_TESTA_ID) AND (DC.ARTICOLO_ID = DC2.ARTICOLO_ID) WHERE DC.ARTICOLO_ID = :ID GROUP BY DATA, DOCUMENTO_ID, NUMERO, SERIE ORDER BY DATA, DOCUMENTO_ID, NUMERO, SERIE but I have an issue: if I have a row with CARICO = 0 and SCARICO = 0 then SALDO is added or subtracted of number present in last row with CARICO > 0 or SCARICO > 0 Thanks -- Luigi Siciliano --
Re: [firebird-support] How write a query with a progressive sum field
07.03.2016 11:51, Luigi Siciliano luigi...@tiscalinet.it [firebird-support] wrote: > I need add SALDO fields in the result of the query to get a progressive > sum of CARICO - SCARICO. sum(carcio-scarcio) over () as SALDO. -- WBR, SD. ++ Visit http://www.firebirdsql.org and click the Documentation item on the main (top) menu. Try FAQ and other links from the left-side menu there. Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ ++ Yahoo Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com <*> To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com <*> Your use of Yahoo Groups is subject to: https://info.yahoo.com/legal/us/yahoo/utos/terms/
Re: [firebird-support] How write a query with a progressive sum field
Hallo, Il 07/03/2016 11.32, Dimitry Sibiryakov s...@ibphoenix.com [firebird-support] ha scritto: > sum(saldo) over (order by data) Thank You for your response but I can't do: sum(SALDO) because SALDO is not a field of my table. I badly explained myself: I have a table with only this fields: DATA DOCUMENTO_ID NUMERO SERIE CARICO SCARICO I need add SALDO fields in the result of the query to get a progressive sum of CARICO - SCARICO. Thanks -- Luigi Siciliano -- ++ Visit http://www.firebirdsql.org and click the Documentation item on the main (top) menu. Try FAQ and other links from the left-side menu there. Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ ++ Yahoo Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com <*> To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com <*> Your use of Yahoo Groups is subject to: https://info.yahoo.com/legal/us/yahoo/utos/terms/
Re: [firebird-support] How write a query with a progressive sum field
07.03.2016 11:16, Luigi Siciliano luigi...@tiscalinet.it [firebird-support] wrote: > How I do? sum(saldo) over (order by data) -- WBR, SD. ++ Visit http://www.firebirdsql.org and click the Documentation item on the main (top) menu. Try FAQ and other links from the left-side menu there. Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ ++ Yahoo Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com <*> To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com <*> Your use of Yahoo Groups is subject to: https://info.yahoo.com/legal/us/yahoo/utos/terms/
