sboydlns wrote:
> I was wondering if there was some way to handle columns
> with NULL values without using indicator variables.  If I
> were to initialize all host variables to some sensible
> value, like zero for numeric fields and an empty string
> for character fields, would those values be preserved by
> the FETCH.  That is, would NULL columns returned by the
> FETCH leave the affected host variables unchanged?

Probably, but that's an artifact of implementation that
may change arbitrarily in the future.  Or, horrors, you
may find that you've got data where zero or an empty
string is a legitimate value.

Indicator variables exist for a purpose.  Use them.

Good luck,

Ann

I was wondering if there was some way to handle columns with NULL values without
using indicator variables.  If I were to initialize all host variables to some
sensible value, like zero for numeric fields and an empty string for character
fields, would those values be preserved by the FETCH.  That is, would NULL
columns returned by the FETCH leave the affected host variables unchanged?

>
>
> sosingus wrote:
> > I've tried adding triggers to system table, it is possible.
>
> Since you use Firebird 1.5, please note that they would be gone after
> backup+restore
>
> Anyway, what you would have to do is create a trigger on RDB$RELATIONS
> (and RDB$RELATION_FIELDS if you need detailed info). This trigger could
> generate triggers for the table which is being created or modified using
> EXECUTE STATEMENT. I believe full support for this is only available in
> Firebird HEAD, so it will be there in Firebird 3.0 ?
>
>
> Even then, it is not trivial to implement. You are better off writing a
> simple application to do the job of generating triggers for you, or use
> a ready-made solution like ibLogManager.
>

Thank you for suggestions, but, what I would like to achieve is to log
changes within same transaction, I can do that by modifying application
code, but it requires too much of programming since application
architecture currently is not straight forward for likewise operations.
And, I would prefer to have it on the server side anyway.

- Sofija
>
>
> --
> Milan Babuskov
>

-> Do you use a 64-bit FB version on centos 64-bit?

Yes.

> If so, have you recompiled the UDF library for a 64-bit target?


Yes.

>
> > typedef short TISC_BlobGetSegment(int*, char*,long, long*);
>
> Last two parameters must be short and short*, respectively;
>
> > typedef struct
> > {
> >  TISC_BlobGetSegment* GetSegment;
> >  int* BlobHandle;
> >  long SegmentCount;
> >  long MaxSegmentLength;
> >  long TotalSize;
> >  TISC_BlobPutSegment* PutSegment;
> > }TBlob;
>
> SegmentCount, MaxSegmentLength and TotalSize must be int.
>
>

I will make these changes and retest.

Kind Regards,

Robert.

-> Do you use a 64-bit FB version on centos 64-bit?

Yes.

> If so, have you recompiled the UDF library for a 64-bit target?


Yes.

>
> > typedef short TISC_BlobGetSegment(int*, char*,long, long*);
>
> Last two parameters must be short and short*, respectively;
>
> > typedef struct
> > {
> >  TISC_BlobGetSegment* GetSegment;
> >  int* BlobHandle;
> >  long SegmentCount;
> >  long MaxSegmentLength;
> >  long TotalSize;
> >  TISC_BlobPutSegment* PutSegment;
> > }TBlob;
>
> SegmentCount, MaxSegmentLength and TotalSize must be int.
>
>

I will make these changes and retest.

Kind Regards,

Robert.

robert.gilland wrote:

> I have compiled and use quite nicely, below 'c' function,
> which then i use in a centos linux 32bit system.
>
> however the same function when executed in a centos 64bit system,
> causes the connection to drop out ( as is standard when an UDF function fails
)

Do you use a 64-bit FB version on centos 64-bit?
If so, have you recompiled the UDF library for a 64-bit target?

> typedef short TISC_BlobGetSegment(int*, char*,long, long*);

Last two parameters must be short and short*, respectively;

> typedef struct
> {
>  TISC_BlobGetSegment* GetSegment;
>  int* BlobHandle;
>  long SegmentCount;
>  long MaxSegmentLength;
>  long TotalSize;
>  TISC_BlobPutSegment* PutSegment;
> }TBlob;

SegmentCount, MaxSegmentLength and TotalSize must be int.


Dmitry

I have compiled and use quite nicely, below 'c' function,
which then i use in a centos linux 32bit system.

however the same function when executed in a centos 64bit system,
causes the connection to drop out ( as is standard when an UDF function fails )

I noticed this bug has been raised in a PHP forum as below.
so it clearly isn't just my fault.

http://bugs.php.net/bug.php?id=42266

Has anyone got any ideas?

Kind Regards,

Robert.

typedef short TISC_BlobGetSegment(int*, char*,long, long*);
typedef void TISC_BlobPutSegment(int*, char*, short);

typedef struct
{
	 TISC_BlobGetSegment* GetSegment;
	 int* BlobHandle;
	 long SegmentCount;
	 long MaxSegmentLength;
	 long TotalSize;
	 TISC_BlobPutSegment* PutSegment;
}TBlob;

typedef TBlob* PBlob;

PBlob StrBlob PROTO_LIST (( char*, PBlob ));

#include <stdio.h>
#include <ctype.h>
#include "global.h"
#include "blob_functions.h"

PBlob StrBlob ( sz, Blob )
   char * sz;
   PBlob Blob;

{
   if(( Blob != NULL )&&( Blob->BlobHandle != NULL ))
	 {
	   Blob->PutSegment(Blob->BlobHandle,sz,strlen(sz));
     }
   return Blob;
}

thanks Leyne for your opinion.




________________________________
From: "Leyne, Sean" <Sean@...>
To: "firebird-support@yahoogroups.com" <firebird-support@yahoogroups.com>
Sent: Tue, November 10, 2009 11:12:43 PM
Subject: RE: [firebird-support] Is it possible to cancel UPDATE with update
trigger ?




>   Is it possible to cancel update inside UPDATE trigger and perform delete
> and insert ?
> I have complex triggers for insert and delete and instead of writing also
> an update trigger i want to force
>   update =delete+insert.

I think you want to use a VIEW with triggers that apply the operations to the
real tables.

In that way you can replace any of the operations with whatever logic you want.

Sean







[Non-text portions of this message have been removed]

>   Is it possible to cancel update inside UPDATE trigger and perform delete
> and insert ?
> I have complex triggers for insert and delete and instead of writing also
> an update trigger i want to force
>   update =delete+insert.

I think you want to use a VIEW with triggers that apply the operations to the
real tables.

In that way you can replace any of the operations with whatever logic you want.


Sean

Thanks.




________________________________
From: Alexandre Benson Smith <iblist@...>
To: firebird-support@yahoogroups.com
Sent: Tue, November 10, 2009 10:25:23 PM
Subject: Re: [firebird-support] Is it possible to cancel UPDATE with update
trigger ?


Mr. John wrote:
> Thanks Alexandre,one question: in your code why UPDATE will not fire
also,because you delete 'current' record

Because an UPDATE trigger just fires for the UPDATE operation, not for
DELETE's.

see you !

--
Alexandre Benson Smith
Development
THOR Software e Comercial Ltda
Santo Andre - Sao Paulo - Brazil
www.thorsoftware. com.br







[Non-text portions of this message have been removed]

Mr. John wrote:
> Thanks Alexandre,one question: in your code why UPDATE will not fire
also,because you delete 'current' record

Because an UPDATE trigger just fires for the UPDATE operation, not for
DELETE's.

see you !

--
Alexandre Benson Smith
Development
THOR Software e Comercial Ltda
Santo Andre - Sao Paulo - Brazil
www.thorsoftware.com.br

I now about common code into stored procedure,but in my case that is what I need
,thanks for your suggestion




________________________________
From: Dimitry Sibiryakov <sd@...>
To: firebird-support@yahoogroups.com
Sent: Tue, November 10, 2009 10:05:41 PM
Subject: Re: [firebird-support] Is it possible to cancel UPDATE with update
trigger ?

> "Update or Insert"   can perform also update but I want to replace update
totally with delete and insert,of course with BEFORE UPDATE TRIGGER

    And all this because you are afraid to rub your fingers off typing
one more trigger?.. Have you ever heard about copy-paste? May be you
heard about code reuse, so you can carry common code into Stored
Procedure...
    If all this is not an option for you, you can replace table with
view. In this case you can implement any crazy logic in triggers.

    SY, SD.


------------------------------------

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Visit http://www.firebirdsql.org and click the Resources item
on the main (top) menu.  Try Knowledgebase and FAQ links !

Also search the knowledgebases at http://www.ibphoenix.com

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Yahoo! Groups Links






[Non-text portions of this message have been removed]

Thanks Alexandre,one question: in your code why UPDATE will not fire
also,because you delete 'current' record ?







________________________________
From: Alexandre Benson Smith <iblist@...>
To: firebird-support@yahoogroups.com
Sent: Tue, November 10, 2009 9:48:44 PM
Subject: Re: [firebird-support] Is it possible to cancel UPDATE with update
trigger ?


John,

Mr. John wrote:
> Hi !
>   Is it possible to cancel update inside UPDATE trigger and perform delete and
insert ?
> I have complex triggers for insert and delete and instead of writing also an
update trigger i want to force
>   update =delete+insert.
> Thanks.
>

I don't remember if I already did something like that.. but I believe so...

Create trigger TU_Table for Table before update as
begin
Delete from Table where PK = old.PK;
Insert into Table (Pk, Col1, Col2) values (new.PK, new.Col1, New.Col2);
end

see you !

--
Alexandre Benson Smith
Development
THOR Software e Comercial Ltda
Santo Andre - Sao Paulo - Brazil
www.thorsoftware. com.br







[Non-text portions of this message have been removed]

> "Update or Insert"   can perform also update but I want to replace update
totally with delete and insert,of course with BEFORE UPDATE TRIGGER

    And all this because you are afraid to rub your fingers off typing
one more trigger?.. Have you ever heard about copy-paste? May be you
heard about code reuse, so you can carry common code into Stored
Procedure...
    If all this is not an option for you, you can replace table with
view. In this case you can implement any crazy logic in triggers.

    SY, SD.

"Update or Insert"   can perform also update but I want to replace update
totally with delete and insert,of course with BEFORE UPDATE TRIGGER

Thanks.




________________________________
From: Hans <hhoogstraat@...>
To: firebird-support@yahoogroups.com
Sent: Tue, November 10, 2009 9:49:30 PM
Subject: Re: [firebird-support] Is it possible to cancel UPDATE with update
trigger ?


Maybe the 'Update or Insert Table .... ' sql statement does it all
for you automatically

----- Original Message -----
From: "Mr. John" <mr_johnmr@yahoo. com>
To: <firebird-support@ yahoogroups. com>
Sent: Tuesday, November 10, 2009 12:36 PM
Subject: [firebird-support] Is it possible to cancel UPDATE with update
trigger ?

> Hi !
>  Is it possible to cancel update inside UPDATE trigger and perform delete
> and insert ?
> I have complex triggers for insert and delete and instead of writing also
> an update trigger i want to force
>  update =delete+insert.
> Thanks.
>
>
>
>
> [Non-text portions of this message have been removed]
>
>
>
> ------------ --------- --------- ------
>
> ++++++++++++ +++++++++ +++++++++ +++++++++ +++++++++ +++++++++ +++++++++
>
> Visit http://www.firebirdsql.org and click the Resources item
> on the main (top) menu.  Try Knowledgebase and FAQ links !
>
> Also search the knowledgebases at http://www.ibphoenix.com
>
> ++++++++++++ +++++++++ +++++++++ +++++++++ +++++++++ +++++++++ +++++++++
> Yahoo! Groups Links
>
>
>
>







[Non-text portions of this message have been removed]

John,

Mr. John wrote:
> Hi !
>   Is it possible to cancel update inside UPDATE trigger and perform delete and
insert ?
> I have complex triggers for insert and delete and instead of writing also an
update trigger i want to force
>   update =delete+insert.
> Thanks.
>

I don't remember if I already did something like that.. but I believe so...

Create trigger TU_Table for Table before update as
begin
    Delete from Table where PK = old.PK;
    Insert into Table (Pk, Col1, Col2) values (new.PK, new.Col1, New.Col2);
end

see you !

--
Alexandre Benson Smith
Development
THOR Software e Comercial Ltda
Santo Andre - Sao Paulo - Brazil
www.thorsoftware.com.br

Maybe the 'Update or Insert Table .... ' sql statement does it all
for you automatically

----- Original Message -----
From: "Mr. John" <mr_johnmr@...>
To: <firebird-support@yahoogroups.com>
Sent: Tuesday, November 10, 2009 12:36 PM
Subject: [firebird-support] Is it possible to cancel UPDATE with update
trigger ?


> Hi !
>  Is it possible to cancel update inside UPDATE trigger and perform delete
> and insert ?
> I have complex triggers for insert and delete and instead of writing also
> an update trigger i want to force
>  update =delete+insert.
> Thanks.
>
>
>
>
> [Non-text portions of this message have been removed]
>
>
>
> ------------------------------------
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Visit http://www.firebirdsql.org and click the Resources item
> on the main (top) menu.  Try Knowledgebase and FAQ links !
>
> Also search the knowledgebases at http://www.ibphoenix.com
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
> Yahoo! Groups Links
>
>
>
>

Hi !
   Is it possible to cancel update inside UPDATE trigger and perform delete and
insert ?
I have complex triggers for insert and delete and instead of writing also an
update trigger i want to force
   update =delete+insert.
Thanks.




[Non-text portions of this message have been removed]

> Do you have any kind of firewall between client and server? Try turning
> it off and see what happens. If you have NOD32 on some of the machines,
> disable the IMON module and try.
>
> --
> Milan Babuskov

Milan, did you mean to respond to my question? I'm failing to see how
firewalling is relevant to line numbering in call_stack?

In any case, I did eventually hunt down the problem -- a trigger was running an
update statement that wasn't using an index properly, and the trigger didn't
show up in mon$call_stack. Apparently the line number that gets reported is at
"end of procedure" when it's a trigger that's executing, even if the trigger is
being fired by a multi-row DML statement inside said procedure, and I would
expect the line number to point to the exact statement that fired the trigger.
Doesn't make sense to me, but whatever.

Exact performance issue in the trigger:

update a set blah = null where a.parent_id in (select id from b where ...)
 -> did NOT use index on a.parent_id(took 2 hours to run bulk update)
merge into a using (select id from b where ...) z on z.id = a.parent_id when
matched then update set blah = null
 -> DID use index on a.parent_id(took 2 minutes to run bulk update)

I made no changes to the index or its statistics. I saw a similar issue in the
tracker (CORE-1969), but it was closed as the submitter had failed to recreate
the index after switching to FB2. That wasn't the case for me.*shrug* It's
fixed.

-Philip



[Non-text portions of this message have been removed]

> Does anyone know what this error message means?.

     Deadlock in place where it is impossible. Sooner of all bug in lock manager.
Reproducible test case will help to fix it.

Regards,
Vlad

Does anyone know what this error message means?. I see this in the firebird.log
every now and then.
I am using classic 2.1.3 with jaybird 2.1.6 .
Errror message:
---------------------------------------------------
SERVER02 Mon Nov 09 20:25:24 2009
	 page 0, page type 1 lock conversion denied
---------------------------------------------------
Thanks

many thanks to all of you.


--- In firebird-support@yahoogroups.com, Christian Waldmann
<Christian.Waldmann@...> wrote:
>
> Hello Iwan
>
> Here is a optimsed select in the stored procedure with a order clause to
> get the correct order:
>
> for select mut.id, mut.TRANSDATE, mut.DEBET, mut.KREDIT
>  from TBL_MUTASI mut
>          order by mut.TRANSDATE
>          into :ID, :TRANSDATE, :DEBET, :KREDIT
>
>
> > Hello Iwan
> >
> > My prefered solution is a stored procedure.
> >
> > Here is the Table and the stored procedure:
> >
> > CREATE TABLE TBL_MUTASI (
> >      ID         INTEGER,
> >      TRANSDATE  TIMESTAMP,
> >      DEBET      NUMERIC(15,2),
> >      KREDIT     NUMERIC(15,2)
> > );
> >
> > SET TERM ^ ;
> >
> > create or alter procedure PROC_CALC_SALDO
> > returns (
> >      ID integer,
> >      TRANSDATE timestamp,
> >      DEBET decimal(15,2),
> >      KREDIT decimal(15,2),
> >      SALDO decimal(15,2))
> > as
> > begin
> >      SALDO = 0;
> >      for select mut.id, mut.TRANSDATE, mut.DEBET, mut.KREDIT from
> > TBL_MUTASI mut
> >          into :ID, :TRANSDATE, :DEBET, :KREDIT
> >      do BEGIN
> >          SALDO = :SALDO + :DEBET - :KREDIT;
> >          suspend;
> >      end
> > end^
> >
> > SET TERM ; ^
> >
> >
> > And the select statment:
> >
> >
> > select * from PROC_CALC_SALDO
> >
> >
> > And the result:
> >
> >    ID            TRANSDATE  DEBET             KREDIT       SALDO
> >    10  03.11.2009 00:00:00  10'000.00              0.00    0'000.00
> >    30  05.11.2009 00:00:00       0.00          5'000.00    5'000.00
> >    20  04.11.2009 00:00:00     250.00              0.00    5'250.00
> >
> >
> > send2iwan schrieb:
> >> hi all,
> >>
> >> i want to make query like this.
> >>
> >> table name=mutasi
> >>
> >> id transdate  debet kredit saldo
> >> == ========== ===== ====== =====
> >> 10 10/01/2009 10000        10000
> >> 20 10/05/2009   250        10250
> >> 30 10/30/2009         5000  5250
> >>
> >> how to make the query espesialy for field saldo?
> >> everyday records increase 50-200 records.
> >>
> >> thanks.
> >> Iwan
> >>
> >>
> >>
> >>
> >> ------------------------------------
> >>
> >> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
> >>
> >> Visit http://www.firebirdsql.org and click the Resources item
> >> on the main (top) menu.  Try Knowledgebase and FAQ links !
> >>
> >> Also search the knowledgebases at http://www.ibphoenix.com
> >>
> >> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
> >> Yahoo! Groups Links
> >>
> >>
> >>
> >
> >
> >
> > ------------------------------------
> >
> > ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
> >
> > Visit http://www.firebirdsql.org and click the Resources item
> > on the main (top) menu.  Try Knowledgebase and FAQ links !
> >
> > Also search the knowledgebases at http://www.ibphoenix.com
> >
> > ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
> > Yahoo! Groups Links
> >
> >
> >
>

Hello Iwan

Here is a optimsed select in the stored procedure with a order clause to
get the correct order:

for select mut.id, mut.TRANSDATE, mut.DEBET, mut.KREDIT
	 from TBL_MUTASI mut
          order by mut.TRANSDATE
          into :ID, :TRANSDATE, :DEBET, :KREDIT


> Hello Iwan
>
> My prefered solution is a stored procedure.
>
> Here is the Table and the stored procedure:
>
> CREATE TABLE TBL_MUTASI (
>      ID         INTEGER,
>      TRANSDATE  TIMESTAMP,
>      DEBET      NUMERIC(15,2),
>      KREDIT     NUMERIC(15,2)
> );
>
> SET TERM ^ ;
>
> create or alter procedure PROC_CALC_SALDO
> returns (
>      ID integer,
>      TRANSDATE timestamp,
>      DEBET decimal(15,2),
>      KREDIT decimal(15,2),
>      SALDO decimal(15,2))
> as
> begin
>      SALDO = 0;
>      for select mut.id, mut.TRANSDATE, mut.DEBET, mut.KREDIT from
> TBL_MUTASI mut
>          into :ID, :TRANSDATE, :DEBET, :KREDIT
>      do BEGIN
>          SALDO = :SALDO + :DEBET - :KREDIT;
>          suspend;
>      end
> end^
>
> SET TERM ; ^
>
>
> And the select statment:
>
>
> select * from PROC_CALC_SALDO
>
>
> And the result:
>
>    ID            TRANSDATE  DEBET             KREDIT       SALDO
>    10  03.11.2009 00:00:00  10'000.00              0.00    0'000.00
>    30  05.11.2009 00:00:00       0.00          5'000.00    5'000.00
>    20  04.11.2009 00:00:00     250.00              0.00    5'250.00
>
>
> send2iwan schrieb:
>> hi all,
>>
>> i want to make query like this.
>>
>> table name=mutasi
>>
>> id transdate  debet kredit saldo
>> == ========== ===== ====== =====
>> 10 10/01/2009 10000        10000
>> 20 10/05/2009   250        10250
>> 30 10/30/2009         5000  5250
>>
>> how to make the query espesialy for field saldo?
>> everyday records increase 50-200 records.
>>
>> thanks.
>> Iwan
>>
>>
>>
>>
>> ------------------------------------
>>
>> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>>
>> Visit http://www.firebirdsql.org and click the Resources item
>> on the main (top) menu.  Try Knowledgebase and FAQ links !
>>
>> Also search the knowledgebases at http://www.ibphoenix.com
>>
>> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>> Yahoo! Groups Links
>>
>>
>>
>
>
>
> ------------------------------------
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Visit http://www.firebirdsql.org and click the Resources item
> on the main (top) menu.  Try Knowledgebase and FAQ links !
>
> Also search the knowledgebases at http://www.ibphoenix.com
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
> Yahoo! Groups Links
>
>
>

Hello Iwan

My prefered solution is a stored procedure.

Here is the Table and the stored procedure:

CREATE TABLE TBL_MUTASI (
      ID         INTEGER,
      TRANSDATE  TIMESTAMP,
      DEBET      NUMERIC(15,2),
      KREDIT     NUMERIC(15,2)
);

SET TERM ^ ;

create or alter procedure PROC_CALC_SALDO
returns (
      ID integer,
      TRANSDATE timestamp,
      DEBET decimal(15,2),
      KREDIT decimal(15,2),
      SALDO decimal(15,2))
as
begin
      SALDO = 0;
      for select mut.id, mut.TRANSDATE, mut.DEBET, mut.KREDIT from
TBL_MUTASI mut
          into :ID, :TRANSDATE, :DEBET, :KREDIT
      do BEGIN
          SALDO = :SALDO + :DEBET - :KREDIT;
          suspend;
      end
end^

SET TERM ; ^


And the select statment:


select * from PROC_CALC_SALDO


And the result:

    ID            TRANSDATE  DEBET             KREDIT       SALDO
    10  03.11.2009 00:00:00  10'000.00              0.00    0'000.00
    30  05.11.2009 00:00:00       0.00          5'000.00    5'000.00
    20  04.11.2009 00:00:00     250.00              0.00    5'250.00


send2iwan schrieb:
> hi all,
>
> i want to make query like this.
>
> table name=mutasi
>
> id transdate  debet kredit saldo
> == ========== ===== ====== =====
> 10 10/01/2009 10000        10000
> 20 10/05/2009   250        10250
> 30 10/30/2009         5000  5250
>
> how to make the query espesialy for field saldo?
> everyday records increase 50-200 records.
>
> thanks.
> Iwan
>
>
>
>
> ------------------------------------
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Visit http://www.firebirdsql.org and click the Resources item
> on the main (top) menu.  Try Knowledgebase and FAQ links !
>
> Also search the knowledgebases at http://www.ibphoenix.com
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
> Yahoo! Groups Links
>
>
>

Morning all,

Just my £0.02:

>> Hm, I really don't see how this is Firebird specific.
>> ...
Well, in my real job I'm an Oracle DBA. I can confirm that if I get onto your
server and copy the database files over to my server, where I have the role of
SYSDBA enabled, I can get into your database and see all your data. So, no, it's
not Firebird Specific.

Cheers,
Norman.

Norman Dunbar
Dunbar IT Consultants Ltd
Orchard House
10a Greenacre Park Mews
Rawdon
Leeds
LS19 6RT

Tel: 0779 3292 984
Tel: 0773 4531 439



Information in this message may be confidential and may be legally privileged.
If you have received this message by mistake, please notify the sender
immediately, delete it and do not copy it to anyone else.   We have checked this
email and its attachments for viruses. But you should still check any attachment
before opening it. We may have to make this message and any reply to it public
if asked to under the Freedom of Information Act, Data Protection Act or for
litigation.  Email messages and attachments sent to or from any Environment
Agency address may also be accessed by someone other than the sender or
recipient, for business purposes.  If we have sent you information and you wish
to use it please read our terms and conditions which you can get by calling us
on 08708 506 506.  Find out more about the Environment Agency at
www.environment-agency.gov.uk

Information in this message may be confidential and may be legally privileged.
If you have received this message by mistake, please notify the sender
immediately, delete it and do not copy it to anyone else.

We have checked this email and its attachments for viruses. But you should still
check any attachment before opening it.
We may have to make this message and any reply to it public if asked to under
the Freedom of Information Act, Data Protection Act or for litigation.  Email
messages and attachments sent to or from any Environment Agency address may also
be accessed by someone other than the sender or recipient, for business
purposes.

If we have sent you information and you wish to use it please read our terms and
conditions which you can get by calling us on 08708 506 506.  Find out more
about the Environment Agency at www.environment-agency.gov.uk

As Alexandre said, doing things on the client could be the best solution. An
alternative if you want to do it on the server (there are lots of unknown
factors that could make you prefer one or the other), could be

SELECT RECURSIVE SUM_MUTASI(ACCOUNT, ID, DEBET, CREDIT, SALDO)
AS (SELECT M.ACCOUNT, M.ID, M.DEBIT, M.CREDIT, COALESCE(M.DEBET,
0)-COALESCE(M.CREDIT, 0)
     FROM MUTASI M
     WHERE NOT EXISTS(SELECT * FROM MUTASI M2
                      WHERE M2.ID < M.ID
                        AND M2.ACCOUNT = M.ACCOUNT)
     UNION ALL
     SELECT SM.ACCOUNT, M.ID, M.DEBET, M.CREDIT, SM.SALDO+COALESCE(M.DEBET,
0)-COALESCE(M.CREDIT, 0)
     FROM SUM_MUTASI SM
     JOIN MUTASI M ON SM.ACCOUNT = M.ACCOUNT
                  AND SM.ID < M.ID+0
     WHERE NOT EXISTS(SELECT * FROM MUTASI M2
                      WHERE M2.ID+0 > SM.ID
                        AND M2.ID+0 < M.ID
                        AND M2.ACCOUNT = M.ACCOUNT))
SELECT *
FROM SUM_MUTASI
ORDER BY ACCOUNT, ID

There is a maximum depth of recursion, I wonder whether it could be as small as
1024, so it will not work for long if you want a running total for the entire
table and the table is more than a few days/weeks old (assuming 50-200 new
records per day).

HTH,
Set

-----Original Message-----
From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com]
On Behalf Of send2iwan
Sent: 10. november 2009 07:03
To: firebird-support@yahoogroups.com
Subject: [firebird-support] need advice in query...

hi all,

i want to make query like this.

table name=mutasi

id transdate  debet kredit saldo
== ========== ===== ====== =====
10 10/01/2009 10000        10000
20 10/05/2009   250        10250
30 10/30/2009         5000  5250

how to make the query espesialy for field saldo?
everyday records increase 50-200 records.

thanks.
Iwan




------------------------------------

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Visit http://www.firebirdsql.org and click the Resources item
on the main (top) menu.  Try Knowledgebase and FAQ links !

Also search the knowledgebases at http://www.ibphoenix.com

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Yahoo! Groups Links

Which means that

select mod(5.3*10, 2.0*10)/10.0 from rdb$database

possibly could work (I've never used MOD with Firebird).

HTH,
Set

-----Original Message-----
From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com]
On Behalf Of Gabor Boros
Sent: 9. november 2009 21:17
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] MOD question

Hi,

No way. MOD is working with integers.

http://www.firebirdsql.org/refdocs/langrefupd21-intfunc-mod.html

Gabor

ionutz gorea írta:
> Hi,
>
> What's the "easiest way" to get the correct result for MOD(5.3, 2.0) from a
Firebird 2.1?
>
> select mod(5.3, 2.0) from rdb$database returns 1 instead of 1.3
>
> Thanks
>
> Ionutz

send2iwan wrote:
> hi all,
>
> i want to make query like this.
>
> table name=mutasi
>
> id transdate  debet kredit saldo
> == ========== ===== ====== =====
> 10 10/01/2009 10000        10000
> 20 10/05/2009   250        10250
> 30 10/30/2009         5000  5250
>
> how to make the query espesialy for field saldo?
> everyday records increase 50-200 records.
>
> thanks.
> Iwan
>

If I were you, I would do it on the client side, usually report
generators have something like "Running Total" variables.

see you !

--
Alexandre Benson Smith
Development
THOR Software e Comercial Ltda
Santo Andre - Sao Paulo - Brazil
www.thorsoftware.com.br

hi all,

i want to make query like this.

table name=mutasi

id transdate  debet kredit saldo
== ========== ===== ====== =====
10 10/01/2009 10000        10000
20 10/05/2009   250        10250
30 10/30/2009         5000  5250

how to make the query espesialy for field saldo?
everyday records increase 50-200 records.

thanks.
Iwan

Hi,

No way. MOD is working with integers.

http://www.firebirdsql.org/refdocs/langrefupd21-intfunc-mod.html

Gabor

ionutz gorea írta:
> Hi,
>
> What's the "easiest way" to get the correct result for MOD(5.3, 2.0) from a
Firebird 2.1?
>
> select mod(5.3, 2.0) from rdb$database returns 1 instead of 1.3
>
> Thanks
>
> Ionutz