Search code examples
sqlgroup-bysumcaseinterbase

SQL(Interbase) : Case of string with Aggregate function


I am not able to make the following SQL work in Interbase: i get the following error:

Error at line 1 Attempt to execute an unprepared dynamic SQL statement

SQL:

SELECT CASE
   WHEN kl='K' THEN (SUM(DB)-SUM(CR))
   WHEN kl='L' THEN (SUM(CR)-SUM(DB))
   END AS Saldo
FROM  Table1
GROUP BY KL

for the Table1 defined this way:

Column name   Data type
------------  ------------
KL            varchar(1)
DB            int
CR            int

Yet it works in SQL fiddle

the following also works!

SELECT CASE 
   WHEN kl='K' THEN 1
   WHEN kl='L' THEN 2
   END as Saldo
FROM  Table1
Group By KL

Solution

  • Found It!

    SELECT Sum(CASE
           WHEN kl='K' THEN db
           WHEN kl='L' THEN cr
           END )
          -Sum(CASE
           WHEN kl='K' THEN CR
           WHEN kl='L' THEN DB
           END )
    
     FROM  Table1
    Group By KL