[firebird-support] Re: UDF install in FB3.0 RC2

2016-03-08 Thread russ...@belding.co.nz [firebird-support] 
Thanks for suggestions Karol. I am using Win 10 (64) and FB3 (32). 
 All DLLs are 32 bit, assessed by looking in the DLL files.
 Russell

Re: AW: [firebird-support] coalesce bug in fb 3.0 rc2?

2016-03-08 Thread Mark Rotteveel m...@lawinegevaar.nl [firebird-support] 
On 2016-03-09 7:57, 'Checkmail' check_m...@satron.de [firebird-support] 
wrote:
> Hello again,
>
> sorry, outlook corrects me in German. At the moment, I have no
> sulution for the problem. Anything ideas?

I think you should create a sample database to demonstrate this 
problem, and create a ticket in the tracker with all the details.

Mark

[firebird-support] Re: UDF install in FB3.0 RC2

2016-03-08 Thread Dmitry Yemanov dim...@users.sourceforge.net [firebird-support] 
09.03.2016 05:11, russ...@belding.co.nz [firebird-support] wrote:
>
> Adding UDFs to databases in FB 3.0 RC2 is giving an error when using any
> function in the added UDF. The UDFs packaged with FB3 are OK.
>
> To the EMPLOYEE db packaged with FB3.0 RC2 I can add UDF fbudf.dll,
> which is packaged with FB3 and its functions select values OK. I have
> also installed ib_udf.dll.
>
> I have also installed two UDFs not packaged with FB3 into the UDF folder
> and when testing (eg. "select extractyear(current_date from rdb$database
> ") the engine gives error "function extractyear is not defined. module
> name or entrypoint could not be found."
>
> Extractyear is in rfunc.dll. The other DLL with the same problem is
> freeUDFLib.dll.

It looks like some system problem. IIRC, rfunc.dll depends on 
fbclient.dll, it must be available (maybe deployed to %system%). Also, 
it could be a MS runtime issue, FB3 is dynamically linked to msvcr10.dll 
while those UDFs may have different requirements.

Do you test FB3 on the same system where FB2.5 runs successfully with 
these UDFs or on a different system?


Dmitry

AW: [firebird-support] coalesce bug in fb 3.0 rc2?

2016-03-08 Thread 'Checkmail' check_m...@satron.de [firebird-support] 
Hello again,

 

sorry, outlook corrects me in German. At the moment, I have no sulution for the 
problem. Anything ideas?

 

Von: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] 
Gesendet: Dienstag, 8. März 2016 16:39
An: firebird-support@yahoogroups.com
Betreff: AW: [firebird-support] coalesce bug in fb 3.0 rc2?

 

  

Hell Mark,

 

I would get all material, where the minimum inventory amount iss maller the the 
inventory amount. 

 

Tlager_sum contains the inventory amount, tteile.minb the theoretical minimum 
amount it should be.

 

Now, the simplified query gets all records where the amount is to small and 
with the „where a.minb > coalesce(b.menge)) I get also the records, where is no 
inventory amount present.

 

If I let the code:

 

select a.teilenr, a.bezeichnung, a.minb, coalesce(b.menge,0) as m, a.typ

from tteile a left join tlager_sum b on (a.teilenr = b.teilenr)

   where ((a.minb > coalesce(b.menge,0))

  and (a.typ = 1)) order by a.teilenr

 

I get m = 0 for records, who has a inventory amount greater than the minimal 
inventory amount. If I change the

 

where ((a.minb > coalesce(b.menge,0))

 

to 

 

where ((a.minb > b.menge)..

 

the records who has a greater inventory amount than the minimal one will be not 
displayed, but also not these who has no inventory amount (null in tlager_sum)

 

In firebird 2.5 and earlier versions it works fine in both situations.

 

The result of the unchanged code:

 

TEILENRBEZEICHNUNGMINBMTYP

PrSt110x44x3   Profilrohr E235,S2 110x44x3,0 mm gebeizt  5600mm280   0  
 1

 

Inventory amount = 0

 

The result without coalesce:

 

The record is no present

 

Without the condition >..

 

TEILENRBEZEICHNUNGMINB  M   TYP

PrSt110x44x3   Profilrohr E235,S2 110x44x3,0 mm gebeizt  5600mm280   
420   1

 

I get 420 as inventory amount, 280 should it be as minimal inventory amount, 
aka minb

 

What can be the problem?

 

Von: firebird-support@yahoogroups.com  
 [mailto:firebird-support@yahoogroups.com] 
Gesendet: Dienstag, 8. März 2016 16:26
An: firebird-support@yahoogroups.com  
Betreff: Re: [firebird-support] coalesce bug in fb 3.0 rc2?

 

  

On 2016-03-08 16:18, 'Checkmail' check_m...@satron.de 
  
[firebird-support] wrote:
> Hello,
>
> in the further version of firebird I can execute the following and 
> get
> the real value of m (exists in tlager_sum)
>
> select a.teilenr, a.bezeichnung, a.minb, coalesce(b.menge,0) as m,
> a.typ
>
> from tteile a left join tlager_sum b on (a.teilenr = b.teilenr)
>
> where ((a.minb > coalesce(b.menge,0))
>
> and (a.typ = 1)) order by a.teilenr
>
> In any case when it is no amount in the table tlager_sum (null), it
> should seen as 0 (not present) and if the minimum amount iss higher..
>
> But, since fb 3 I get 0, why? The tlager_sum has for many records a
> mass. If I change to
>
> a.minb > b.menge
>
> it works for these records, but not for any with no amount.
>
> What can be the issue?

It is not clear to me what your problem is. Could you describe it in 
more detail and provide a sample dataset, expected results and actual 
results?

Mark

Re: [firebird-support] Re: UDF install in FB3.0 RC2

2016-03-08 Thread liviuslivius liviusliv...@poczta.onet.pl [firebird-support] 
Hi,
 
what is the bitnes of your udf dll 32 bit or 64 bit? And bitnes of Firebird 32 
or 64 bit?
Both must be in the same bitnes.
 
regards,
Karol Bieniaszewski
 
W dniu 2016-03-09 03:11:40 użytkownik russ...@belding.co.nz [firebird-support] 
 napisał:
 
Added: Recap:
Adding UDFs to databases in FB 3.0 RC2 is giving an error when using any 
function in the added UDF. The UDFs packaged with FB3 are OK.
 
To the EMPLOYEE db packaged with FB3.0 RC2 I can add UDF fbudf.dll, which is 
packaged with FB3 and its functions select values OK. I have also installed 
ib_udf.dll.
 
I have also installed two UDFs not packaged with FB3 into the UDF folder and 
when testing (eg. "select extractyear(current_date from rdb$database ") the 
engine gives error "function extractyear is not defined. module name or 
entrypoint could not be found."
 
Extractyear is in rfunc.dl. The other DLL with the same problem is 
freeUDFLib.dll.
< span style="color:rgb(63, 63, 63);word-spacing:normal;">I am connected to the 
DB as SYSDBA. Table RDB$USER_PRIVILEDGES has the same values for 
RBD$RELATION_NAMEs ADDMONTH (in fbudf.dll which tests OK) and EXTRACTYEAR (in 
rfunc.dll which gives the error).
 
The UDFs look to have installed OK. 
The problem does not seem to be with the new to FB3  "GRANT EXECUTE TO FUNCTION 
..." based on my look into table RDB$USER_PRIVILEGES.
 
All DLLs in the FB3 UDF folder load OK in an EXE I have for examining parts of 
DLLs.
 
It look as if FB3 is telling me "I only open UDF DLLs I like". Suggestions for 
diagnosis are welcome.
 
 
 
 

 

[firebird-support] Re: UDF install in FB3.0 RC2

2016-03-08 Thread russ...@belding.co.nz [firebird-support] 
Added: Recap: Adding UDFs to databases in FB 3.0 RC2 is giving an error when 
using any function in the added UDF. The UDFs packaged with FB3 are OK. 

 To the EMPLOYEE db packaged with FB3.0 RC2 I can add UDF fbudf.dll, which is 
packaged with FB3 and its functions select values OK. I have also installed 
ib_udf.dll.
 

 I have also installed two UDFs not packaged with FB3 into the UDF folder and 
when testing (eg. "select extractyear(current_date from rdb$database ") the 
engine gives error "function extractyear is not defined. module name or 
entrypoint could not be found."
 

 Extractyear is in rfunc.dl. The other DLL with the same problem is 
freeUDFLib.dll.
 I am connected to the DB as SYSDBA. Table RDB$USER_PRIVILEDGES has the same 
values for RBD$RELATION_NAMEs ADDMONTH (in fbudf.dll which tests OK) and 
EXTRACTYEAR (in rfunc.dll which gives the error).
 

 The UDFs look to have installed OK. 
 The problem does not seem to be with the new to FB3  "GRANT EXECUTE TO 
FUNCTION ..." based on my look into table RDB$USER_PRIVILEGES.
 

 All DLLs in the FB3 UDF folder load OK in an EXE I have for examining parts of 
DLLs.
 

 It look as if FB3 is telling me "I only open UDF DLLs I like". Suggestions for 
diagnosis are welcome.

Re: [firebird-support] How write a query with a progressive sum field

2016-03-08 Thread setysvar setys...@gmail.com [firebird-support] 
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/

AW: [firebird-support] coalesce bug in fb 3.0 rc2?

2016-03-08 Thread 'Checkmail' check_m...@satron.de [firebird-support] 
Hell Mark,

 

I would get all material, where the minimum inventory amount iss maller the the 
inventory amount. 

 

Tlager_sum contains the inventory amount, tteile.minb the theoretical minimum 
amount it should be.

 

Now, the simplified query gets all records where the amount is to small and 
with the „where a.minb > coalesce(b.menge)) I get also the records, where is no 
inventory amount present.

 

If I let the code:

 

select a.teilenr, a.bezeichnung, a.minb, coalesce(b.menge,0) as m, a.typ

from tteile a left join tlager_sum b on (a.teilenr = b.teilenr)

   where ((a.minb > coalesce(b.menge,0))

  and (a.typ = 1)) order by a.teilenr

 

I get m = 0 for records, who has a inventory amount greater than the minimal 
inventory amount. If I change the

 

where ((a.minb > coalesce(b.menge,0))

 

to 

 

where ((a.minb > b.menge)..

 

the records who has a greater inventory amount than the minimal one will be not 
displayed, but also not these who has no inventory amount (null in tlager_sum)

 

In firebird 2.5 and earlier versions it works fine in both situations.

 

The result of the unchanged code:

 

TEILENRBEZEICHNUNGMINBMTYP

PrSt110x44x3   Profilrohr E235,S2 110x44x3,0 mm gebeizt  5600mm280   0  
 1

 

Inventory amount = 0

 

The result without coalesce:

 

The record is no present

 

Without the condition >..

 

TEILENRBEZEICHNUNGMINB  M   TYP

PrSt110x44x3   Profilrohr E235,S2 110x44x3,0 mm gebeizt  5600mm280   
420   1

 

I get 420 as inventory amount, 280 should it be as minimal inventory amount, 
aka minb

 

What can be the problem?

 

Von: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] 
Gesendet: Dienstag, 8. März 2016 16:26
An: firebird-support@yahoogroups.com
Betreff: Re: [firebird-support] coalesce bug in fb 3.0 rc2?

 

  

On 2016-03-08 16:18, 'Checkmail' check_m...@satron.de 
  
[firebird-support] wrote:
> Hello,
>
> in the further version of firebird I can execute the following and 
> get
> the real value of m (exists in tlager_sum)
>
> select a.teilenr, a.bezeichnung, a.minb, coalesce(b.menge,0) as m,
> a.typ
>
> from tteile a left join tlager_sum b on (a.teilenr = b.teilenr)
>
> where ((a.minb > coalesce(b.menge,0))
>
> and (a.typ = 1)) order by a.teilenr
>
> In any case when it is no amount in the table tlager_sum (null), it
> should seen as 0 (not present) and if the minimum amount iss higher..
>
> But, since fb 3 I get 0, why? The tlager_sum has for many records a
> mass. If I change to
>
> a.minb > b.menge
>
> it works for these records, but not for any with no amount.
>
> What can be the issue?

It is not clear to me what your problem is. Could you describe it in 
more detail and provide a sample dataset, expected results and actual 
results?

Mark

Re: [firebird-support] coalesce bug in fb 3.0 rc2?

2016-03-08 Thread Mark Rotteveel m...@lawinegevaar.nl [firebird-support] 
On 2016-03-08 16:18, 'Checkmail' check_m...@satron.de 
[firebird-support] wrote:
> Hello,
>
> in the further version of firebird I can execute the following and 
> get
> the real value of m (exists in tlager_sum)
>
> select a.teilenr, a.bezeichnung, a.minb, coalesce(b.menge,0) as m,
> a.typ
>
>  from tteile a left join tlager_sum b on (a.teilenr = b.teilenr)
>
>  where ((a.minb > coalesce(b.menge,0))
>
>  and (a.typ = 1)) order by a.teilenr
>
> In any case when it is no amount in the table tlager_sum (null), it
> should seen as 0 (not present) and if the minimum amount iss higher..
>
> But, since fb 3 I get 0, why? The tlager_sum has for many records a
> mass. If I change to
>
>  a.minb > b.menge
>
> it works for these records, but not for any with no amount.
>
> What can be the issue?

It is not clear to me what your problem is. Could you describe it in 
more detail and provide a sample dataset, expected results and actual 
results?

Mark

[firebird-support] coalesce bug in fb 3.0 rc2?

2016-03-08 Thread 'Checkmail' check_m...@satron.de [firebird-support] 
Hello,

 

in the further version of firebird I can execute the following and get the
real value of m (exists in tlager_sum)

 

select a.teilenr, a.bezeichnung, a.minb, coalesce(b.menge,0) as m, a.typ

from tteile a left join tlager_sum b on (a.teilenr = b.teilenr)

   where ((a.minb > coalesce(b.menge,0))

  and (a.typ = 1)) order by a.teilenr

 

In any case when it is no amount in the table tlager_sum (null), it should
seen as 0 (not present) and if the minimum amount iss higher..

 

But, since fb 3 I get 0, why? The tlager_sum has for many records a mass. If
I change to 

 

   a.minb > b.menge 

 

it works for these records, but not for any with no amount.

 

What can be the issue?

 

Thanks

 

Best regards

 

Olaf

[firebird-support] Re: query to return the last entry in detail table for each entry in master table

2016-03-08 Thread talorigo...@yahoo.co.uk [firebird-support] 
Thanks liviuslivius.  You've taught me not to try to understand new code whilst 
tired. :)

setysvar - Now that I'm refreshed I understand  code and am very appreciative

Re: [firebird-support] Re: How write a query with a progressive sum field

2016-03-08 Thread Rik van Kekem r...@graficalc.nl [firebird-support] 

Luigi Siciliano luigi...@tiscalinet.it [firebird-support] wrote:
I get exception: "invalid expression in the select list (not contained 
in either an aggregate function or the group by clause)".
I don't understand the GROUP BY clause who I need to write in 
sub-select because if I put the same as in select: "GROUP BY DATA, 
DOCUMENTO_ID, NUMERO, SERIE" I get the exception: "cannot use an 
aggregate function in a GROUP BY clause."

I'm confused :(
Yeah, I wonder why that worked in the first place in your original 
select. You didn't group by DC.CARICO and DC.SCARICO there too.


But with a (SELECT)-field you don't need to group anymore.

Try this:

select
  DT.DATA,
  DT.DOCUMENTO_ID,
  DT.NUMERO,
  DT.SERIE,
  DC.CARICO,
  DC.SCARICO,
  (SELECT SUM(DC2.CARICO - DC2.SCARICO) FROM DOC_CORPO DC2
   WHERE (DC2.DOC_TESTA_ID = DT.ID) AND (DC2.ARTICOLO_ID = DC.ARTICOLO_ID)
   AND (DC2.DOC_TESTA_ID <= DC.DOC_TESTA_ID)
  ) AS SALDO
from DOC_TESTA DT
JOIN DOC_CORPO DC on DC.DOC_TESTA_ID = DT.ID
WHERE DC.ARTICOLO_ID = :ID
ORDER BY DT.DATA, DT.DOCUMENTO_ID, DT.NUMERO, DT.SERIE

Re: [firebird-support] Re: How write a query with a progressive sum field

2016-03-08 Thread Luigi Siciliano luigi...@tiscalinet.it [firebird-support] 

Hallo,

Il 08/03/2016 11.24, Aage Johansen aagjo...@online.no [firebird-support] 
ha scritto:



You could try
coalesce(sum(DC2.CARICO-DC2.SCARICO),0) as SALDO


In this case I obtain that the SALDO field reports 0 instead of null.

Thanks.
--

Luigi Siciliano
--

Re: [firebird-support] Re: How write a query with a progressive sum field

2016-03-08 Thread Luigi Siciliano luigi...@tiscalinet.it [firebird-support] 

Hallo,

Il 08/03/2016 10.05, r...@graficalc.nl [firebird-support] ha scritto:


I'm looking at your statement and my first instinct was that it should 
be correct (also for CARICO = 0 and SCARICO = 0). But you need to make 
sure that they are really filled with 0 and not NULL.




Yes, they are ever NOT NULL because I provide default value = 0. I 
verified :)


Furthermore... You do a LEFT JOIN with DC2 on DOC_CORPO but there is 
no ORDER BY for the DC2. Doesn't that mean that DC2 is un-ordered 
(ordering can be anything). Using the SUM() in that case seems 
dangerous because now you assume the ordering of DC2 is the same as DC.


The query seems to works ok but is not ever true because it could be 
that I have a DC.DOC_TESTA_ID not in same order by DATA + DOCUMENTO_ID + 
NUMERO + SERIE and in this case the result of SALDO field is not correct :(




You could try the following:
select
  DT.DATA,
  DT.DOCUMENTO_ID,
  DT.NUMERO,
  DT.SERIE,
  DC.CARICO,
  DC.SCARICO,
  (SELECT SUM(DC2.CARICO - DC2.SCARICO) FROM DOC_CORPO DC2
   WHERE (DC2.DOC_TESTA_ID = DT.ID)AND(DC2.ARTICOLO_ID = DC.ARTICOLO_ID)
   AND (DC2.DOC_TESTA_ID <= DC.DOC_TESTA_ID) ) AS SALDO
from DOC_TESTA DT
JOIN DOC_CORPO DC on DC.DOC_TESTA_ID = DT.ID
WHERE DC.ARTICOLO_ID = :ID
GROUP BY DATA, DOCUMENTO_ID, NUMERO, SERIE
ORDER BY DATA, DOCUMENTO_ID, NUMERO, SERIE

I get exception: "invalid expression in the select list (not contained 
in either an aggregate function or the group by clause)".
I don't understand the GROUP BY clause who I need to write in sub-select 
because if I put the same as in select: "GROUP BY DATA, DOCUMENTO_ID, 
NUMERO, SERIE" I get the exception: "cannot use an aggregate function in 
a GROUP BY clause."

I'm confused :(

(I'm not sure if you should add DC.DOC_TESTA_ID to the order clause. 
If that works you should also do an ORDER BY in the sub-select)




If I understand correctly, not works.

Thanks

--

Luigi Siciliano
--

[firebird-support] Re: How write a query with a progressive sum field

2016-03-08 Thread Aage Johansen aagjo...@online.no [firebird-support] 
Luigi Siciliano wrote:
<<
select
DT.DATA,
DT.DOCUMENTO_ID,
DT.NUMERO,
DT.SERIE,
DC.CARICO,
DC.SCARICO,
SUM(dc2.carico - DC2.SCARICO) AS saldo
from
...

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?
 >>

You could try
coalesce(sum(DC2.CARICO-DC2.SCARICO),0) as SALDO

-- 
Aage J.

[firebird-support] Re: How write a query with a progressive sum field

2016-03-08 Thread r...@graficalc.nl [firebird-support] 
I'm looking at your statement and my first instinct was that it should be 
correct (also for CARICO = 0 and SCARICO = 0). But you need to make sure that 
they are really filled with 0 and not NULL. 

 Furthermore... You do a LEFT JOIN with DC2 on DOC_CORPO but there is no ORDER 
BY for the DC2. Doesn't that mean that DC2 is un-ordered (ordering can be 
anything). Using the SUM() in that case seems dangerous because now you assume 
the ordering of DC2 is the same as DC.
 
 And last... you should use the same criteria for DC2 as you did for DC. So:
 LEFT JOIN DOC_CORPO DC2 ON DT.ID = DC2.DOC_TESTA_ID
   AND (DC.ARTICOLO_ID = DC2.ARTICOLO_ID)
AND (DC2.DOC_TESTA_ID <= DC2.DOC_TESTA_ID)
 But I think that's the same because of the DC.ARTICOLO_ID = :ID but I can't be 
sure because you didn't provide the complete DDL.
 

 You could try the following:
 select
  DT.DATA,
  DT.DOCUMENTO_ID,
  DT.NUMERO,
  DT.SERIE,
  DC.CARICO,
  DC.SCARICO,
  (SELECT SUM(DC2.CARICO - DC2.SCARICO) FROM DOC_CORPO DC2

WHERE (DC2.DOC_TESTA_ID = DT.ID) AND (DC2.ARTICOLO_ID = DC.ARTICOLO_ID)
AND (DC2.DOC_TESTA_ID <= DC.DOC_TESTA_ID) ) AS SALDO
 from DOC_TESTA DT

 JOIN DOC_CORPO DC on DC.DOC_TESTA_ID = DT.ID
 WHERE DC.ARTICOLO_ID = :ID
GROUP BY DATA, DOCUMENTO_ID, NUMERO, SERIE
ORDER BY DATA, DOCUMENTO_ID, NUMERO, SERIE

 

 (I'm not sure if you should add DC.DOC_TESTA_ID to the order clause. If that 
works you should also do an ORDER BY in the sub-select)
 

 

 
---In firebird-support@yahoogroups.com,  wrote :

 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.