>Thanks for this.  I am more fine with zeros instead of NULL.  And I need to deliver the end result rather than expecting the users to do the same

>in Excel because this is a report they need to print day in and day out, the report being for eg. Sales Register.  I did try to run your code but I get

>the following error.  Please advise
>
>Preparing statement: EXECUTE BLOCK returns (Mystatement varchar(4096)) AS
>DECLARE VARIABLE S VARCHAR(256)
>Error: *** IBPP::SQLException ***
>Context: Statement::Prepare( EXECUTE BLOCK returns (Mystatement varchar(4096)) AS
>DECLARE VARIABLE S VARCHAR(256) )
>Message: isc_dsql_prepare failed
>
>SQL Message : -104
>Invalid token
>
>Engine Code    : 335544569
>Engine Message :
>Dynamic SQL Error
>SQL error code = -104
>Unexpected end of command - line 2, column 31

Is this a Firebird 2.5 database with dialect 3 (if the database was created with InterBase before Firebird was born, it could be dialect 1, if it is a newer database it is most likely dialect 3 unless it is explicitly set to a different dialect)?

 

I created two tables (MACCOUNTS & SSALEINVOICEFOOTER), added the records you mentioned and ran the SQL through Database Workbench before I posted my reply, so the code ought to work. To me it seems as whatever tool you’re using to execute the query thinks that the semicolon actually ends the query rather than being part of the statement. You could of course try

 

SET TERM ^^ ;

 

before EXECUTE BLOCK, but I’ve never had to do that with EXECUTE BLOCK. I’m sorry that I’ve no other suggestion for you, if the database is not Firebird 2.5 with dialect 3 (OK, I jumped directly from Firebird 1.5 to Firebird 2.5, so I’m not certain whether it will work with older Firebird 2.x), then I simply don’t think my suggestion can help you.

 

Set

Yes you are correct Set maybe I will have to update to FB 2.5 because I am on 2.1.4 still.  Glad to know you did the jump, I was think to jump to FB 3 directly when it comes but I guess I will have to update to 2.5 inbetween.

Thanks and regards
Bhavbhuti