Search code examples
sqlfirebirdfirebird-3.0

How to make ledger using Firebird database

I want to design Crystal Report from mentioned query, but I'm unable to fetch data from a Firebird database.

SELECT TRANSACTION_DATE=null, PARTICULAR='Opening Balance', DEBIT=null, CREDIT=null, sum(DEBIT)-sum(CREDIT) as balance
FROM ROZNAMCHA 
WHERE TRANSACTION_DATE < '2021-06-07'
union all
SELECT TRANSACTION_DATE, PARTICULAR, DEBIT, CREDIT, DEBIT-CREDIT 
FROM ROZNAMCHA 
WHERE TRANSACTION_DATE BETWEEN '2021-06-08' AND '2021-06-08'

I want something like this

1

Facing error

2

Solution

  • There are two problems with your query:

    1. You currently have boolean expressions in your select list where you probably just want to declare fixed values with an alias:

      TRANSACTION_DATE=null, PARTICULAR='Opening Balance', DEBIT=null, CREDIT=null

      should be

      null as TRANSACTION_DATE, 'Opening Balance' as PARTICULAR, null as DEBIT, null as CREDIT

    2. As a result of issue 1, you're using columns from the table in your query, while you also use aggregate functions. When using aggregate functions, columns that occur outside aggregate functions must be listed in the GROUP BY clause.

      In this case, fixing the first issue, also fixes this issue.

    In short, your query should be:

    SELECT null as TRANSACTION_DATE, 'Opening Balance' as PARTICULAR, 
       null as DEBIT, null as CREDIT, 
       sum(DEBIT)-sum(CREDIT) as balance
FROM ROZNAMCHA 
WHERE TRANSACTION_DATE < '2021-06-07'
union all
SELECT TRANSACTION_DATE, PARTICULAR, DEBIT, CREDIT, DEBIT-CREDIT 
FROM ROZNAMCHA 
WHERE TRANSACTION_DATE BETWEEN '2021-06-08' AND '2021-06-08'

    Be aware, this doesn't produce your desired output. For that you'll need to do more.