[second attempt to send to the list, the first attempt was returned as
coming from not a member, even though the address is the very same as
yahoogroups send firebird-support messages to.]

Hi Adriano!

I'll answer your first query, since this - in my opinion - has most
errors. Note that I do not know anything about Fb 2.0, so my answer is
based on experience with Fb 1.5.

First, translating your query to plain SQL with parametres:

SELECT Contabile.*, (Select SUM(Contabile.Importo) FROM Contabile
WHERE Contabile.Tipo = 'Entrata' AND
Contabile.Anno = :Anno) As TotaleEntrate
FROM Contabile
WHERE Contabile.Tipo = 'Entrata' AND
Contabile.Anno = :Anno
UNION
SELECT Fatture.*, (Select sum(Fatture.Totalefattura) FROM Fatture AS
Fatture
WHERE Fatture.anno = :Anno) AS TotaleEntrate
FROM Fatture WHERE (Fatture.Anno=:Anno)

Even though it is legal, using * in a select is not recommended. Using
it in a UNION adds to potential problems in that it requires not only
the number of fields, but also the order of fields in Contabile and
Fatture to match.

You also refer to Contabile twice without using any alias, and this is
asking for trouble. The same goes for using an alias with the same
name as the table! Add different alias to all your table references.

Thirdly, you try to give a column two different names. As said, I
don't know about 2.0, but to me it sounds about as sensible as if my
parents had told the birth registry: "His name shall be 'Artur' unless
his weight is above 75 kg - then he shall be called 'Set'".

So, in short, my recommendation is to change your query to something like:

SELECT C1.Field1, C1.Field2, (Select SUM(C2.Importo)
FROM Contabile C2
WHERE C2.Tipo = C1.Tipo AND C2.Anno = C1.Anno) As TotaleEntrateFatture
FROM Contabile C1
WHERE C1.Tipo = 'Entrata' AND C1.Anno = :Anno
UNION
SELECT F1.Field1, F1.Field2, (Select sum(F2.Totalefattura)
FROM Fatture F2
WHERE F2.anno = F1.Anno)
FROM Fatture F1 WHERE F1.Anno=:Anno

HTH,
Set (I'll never become a real Artur)

--- In firebird-support@yahoogroups.com, Adriano wrote:
> FB 2.x
> ODBC Driver
> I received an unexpected end of command in this union query:
> Dynamic SQL Error
> SQL error code -104
>
> query = "SELECT Contabile.*, (Select SUM(Contabile.Importo)
> FROM Contabile " & _
> "WHERE Contabile.Tipo = 'Entrata' AND Contabile.Anno =" +
> Anno.Text + ") As TotaleEntrate " & _
> "FROM Contabile WHERE Contabile.Tipo = 'Entrata' AND
> Contabile.Anno =" + Anno.Text + " " & _
> "UNION SELECT Fatture.*, (Select sum(Fatture.Totalefattura)
> FROM Fatture AS Fatture " & _
> "WHERE Fatture.anno =" + Anno.Text + ") AS TotaleEntrate " & _
> "FROM Fatture WHERE (Fatture.Anno=" + Anno.Text + ") "" "
>
> Set rs = cn.Execute(query, , adCmdText)
>
> why ?
> thanks
> Adriano