Correct use of alias in Interbase subqueries

Question

I have a problem with my newly created aliases ABONO and CARGO. The ALIASES (for the column names of the result) and SQL query works fine UNTIL I try to calculate "ABONO-CARGO AS AJUSTE" => Interbase gives an error that it does not know the column "ABONO".

Dynamic SQL Error SQL error code = -206 Column unknown ABONO

What is wrong?

SELECT M."MOV_CUENTA",
       (SELECT SUM(MM."MOV_MONTO") 
          FROM "movimientos" MM
         WHERE MM."MOV_TIPOMOV" = 'A' AND MM."MOV_CUENTA" = CS."ID_CUENTAMUN"
         GROUP BY MM."MOV_CUENTA"
       ) AS ABONO,
       (SELECT SUM(XM."MOV_MONTO")
          FROM "movimientos" XM
         WHERE XM."MOV_TIPOMOV" = 'C' AND XM."MOV_CUENTA" = CS."ID_CUENTAMUN"
         GROUP BY XM."MOV_CUENTA"
       ) AS CARGO,
       ABONO-CARGO AS AJUSTE
  FROM "cuentasSaldo" CS
 INNER JOIN "movimientos" M ON (CS."ID_CUENTAMUN" = M."MOV_CUENTA")
 INNER JOIN "ajustes" AA ON (M."ID_AJUSTE" = AA."ID_AJUSTE")
 INNER JOIN "documentos" DD ON (AA."ID_DOCUMENTO" = DD."ID_DOCUMENTO")
 WHERE M."ID_AJUSTE" IN
           (SELECT A."ID_AJUSTE"
              FROM "ajustes" A
             WHERE A."ID_DOCUMENTO" IN
                     (SELECT D."ID_DOCUMENTO"
                        FROM "documentos" D
                       WHERE D."ID_EMPRESA" = 1 AND D."DOC_EDITABLE" = 1
                     )
           )
ORDER BY M."ID_AJUSTE", M."MOV_CUENTA"

is it really required that u need to do a simple field1-field2 in query itself? Cant you do it in your application part? Aliasing the way u mentioned doesnt work in few dbs I know, so it has to be the case with FireBird too — nawfal, May 08 '12 at 17:40
Well, I am programming in C++ Builder and for I am using a TDBGrid (C++) component with a datasource and dataset to fill it I was facing difficulties adding a calculated field to the dataset. The __onCalcFields__ event of the dataset is not fired so I decided to let the DB do this part and calculate the charges and payments and its sum. — Florian Wagner, May 08 '12 at 18:09

score 2 · Answer 1 · answered May 08 '12 at 17:42

Succinctly, in many SQL DBMS, the column aliases given in the select-list cannot be used elsewhere in the query, not even in other parts of the select-list.

You'll either have to do the calculation in a reporting tool, or repeat the two queries to get the value (which is painful to write, even with copy'n'paste — just one more reason to have a good reporting tool).

SELECT M."MOV_CUENTA",
       (SELECT SUM(MM."MOV_MONTO") 
          FROM "movimientos" MM
         WHERE MM."MOV_TIPOMOV" = 'A' AND MM."MOV_CUENTA" = CS."ID_CUENTAMUN"
         GROUP BY MM."MOV_CUENTA"
       ) AS ABONO,
       (SELECT SUM(XM."MOV_MONTO")
          FROM "movimientos" XM
         WHERE XM."MOV_TIPOMOV" = 'C' AND XM."MOV_CUENTA" = CS."ID_CUENTAMUN"
         GROUP BY XM."MOV_CUENTA"
       ) AS CARGO,
       ((SELECT SUM(MM."MOV_MONTO") 
          FROM "movimientos" MM
         WHERE MM."MOV_TIPOMOV" = 'A' AND MM."MOV_CUENTA" = CS."ID_CUENTAMUN"
         GROUP BY MM."MOV_CUENTA"
       ) -
       (SELECT SUM(XM."MOV_MONTO")
          FROM "movimientos" XM
         WHERE XM."MOV_TIPOMOV" = 'C' AND XM."MOV_CUENTA" = CS."ID_CUENTAMUN"
         GROUP BY XM."MOV_CUENTA"
       )) AS AJUSTE
  FROM "cuentasSaldo" CS
 INNER JOIN "movimientos" M ON (CS."ID_CUENTAMUN" = M."MOV_CUENTA")
 INNER JOIN "ajustes" AA ON (M."ID_AJUSTE" = AA."ID_AJUSTE")
 INNER JOIN "documentos" DD ON (AA."ID_DOCUMENTO" = DD."ID_DOCUMENTO")
 WHERE M."ID_AJUSTE" IN
           (SELECT A."ID_AJUSTE"
              FROM "ajustes" A
             WHERE A."ID_DOCUMENTO" IN
                     (SELECT D."ID_DOCUMENTO"
                        FROM "documentos" D
                       WHERE D."ID_EMPRESA" = 1 AND D."DOC_EDITABLE" = 1
                     )
           )
ORDER BY M."ID_AJUSTE", M."MOV_CUENTA"

It is rather easy to lose that solitary - in amongst those expressions.

Another way to do it is:

SELECT "MOV_CUENTA", ABONO, CARGO, (ABONO - CARGO) AS AJUSTE
  FROM (SELECT M."ID_AJUSTE",
               M."MOV_CUENTA",
               (SELECT SUM(MM."MOV_MONTO") 
                  FROM "movimientos" MM
                 WHERE MM."MOV_TIPOMOV" = 'A' AND MM."MOV_CUENTA" = CS."ID_CUENTAMUN"
                 GROUP BY MM."MOV_CUENTA"
               ) AS ABONO,
               (SELECT SUM(XM."MOV_MONTO")
                  FROM "movimientos" XM
                 WHERE XM."MOV_TIPOMOV" = 'C' AND XM."MOV_CUENTA" = CS."ID_CUENTAMUN"
                 GROUP BY XM."MOV_CUENTA"
               ) AS CARGO
          FROM "cuentasSaldo" CS
         INNER JOIN "movimientos" M ON (CS."ID_CUENTAMUN" = M."MOV_CUENTA")
         INNER JOIN "ajustes" AA ON (M."ID_AJUSTE" = AA."ID_AJUSTE")
         INNER JOIN "documentos" DD ON (AA."ID_DOCUMENTO" = DD."ID_DOCUMENTO")
         WHERE M."ID_AJUSTE" IN
                   (SELECT A."ID_AJUSTE"
                      FROM "ajustes" A
                     WHERE A."ID_DOCUMENTO" IN
                             (SELECT D."ID_DOCUMENTO"
                                FROM "documentos" D
                               WHERE D."ID_EMPRESA" = 1 AND D."DOC_EDITABLE" = 1
                             )
                   )
       ) AS X
ORDER BY "ID_AJUSTE", "MOV_CUENTA"

I think you should be selecting ID_AJUSTE too, but that's your decision. I'm not sure whether you really need all those delimited identifiers, either, but I've largely left them untouched.

In Fireibrd quoted identifiers are only necessary if the identifier is a keyword or if you want case-sensitive identifiers. — Mark Rotteveel, May 08 '12 at 17:57
@MarkRotteveel: so, in that respect, Firebird is standard SQL. Standard SQL only needs delimited identifiers for keywords, case-sensitive identifiers, or identifiers that contain characters (such as spaces and dashes) that can't be in a normal identifier. — Jonathan Leffler, May 08 '12 at 18:01
The first one at least executes, I think I just have to be careful for some accounts have and so my sum is For I am using test data and just a few accounts all sums are . I am gonna check that. The second part you posted does not execute in firebird => Dynamic SQL Error SQL error code = -104 Token unknown - line 2, char 7 SELECT (This is the SELECT after the FROM) Meanwhile I will try to add a calculated field to the TIBDataSet... — Florian Wagner, May 08 '12 at 18:45
Slightly puzzled; Firebird 2.5 supports the nested FROM (SELECT ...) notation according to the manual, and has done since Firebird 2.0. All I did was take your query, indent it a couple of times, wrap parentheses around it, and add a new SELECT list and leave the ORDER BY below; and add "ID_AJUSTE" to the select-list of the inner query. But take what works. — Jonathan Leffler, May 08 '12 at 19:02
Oh, I got confused between two C++ projects. I'm sorry! This database is Interbase and in interbase I have to use a view or a stored procedure. I read for example here [link](http://www.tek-tips.com/viewthread.cfm?qid=552816) that this kind of nesting is not implemented. Is that correct? — Florian Wagner, May 08 '12 at 21:48
Since there's a document about migrating from InterBase 6.0 to Firebird 1.5 and 2.0, it is a reasonable guess that the `FROM (SELECT ...)` syntax is not available in InterBase, which would be why the second query failed for you. In which case, I suggest collecting the two values from your original query in your C++ program, and calculating the difference yourself in your program. And an upgrade to Firebird, of course. — Jonathan Leffler, May 08 '12 at 22:57

score 0 · Answer 2 · answered May 08 '12 at 19:42

0

You should have a field ABONO in table "cuentasSaldo" (CS), and you have a subquery aliased as ABONO. So you have two "objects" with the same name. I suggest you try to call field in format: query.field. Example in Line 6: instead of: ABONO-CARGO AS AJUSTE use: CS.ABONO-CARGO AS AJUSTE.

answered May 08 '12 at 19:42

Nelson H C Nepomuceno

1

I don't have any field called ABONO / CARGO. That comes from the table "movimientos" (movements) - Field: MOV_TIPOMOV: "A" or "C" => So this is not an ambiguous name but thanks anyway. – Florian Wagner May 08 '12 at 21:42

Correct use of alias in Interbase subqueries

2 Answers2