Problem with the execution of SQL (SELECT UNION)

[#1]

Good morning, I have the following problem:
Use the following code

Importi:

LOAD

    FIELD_1,

    FIELD_2,

    FIELD_3,

    FIELD_4,

    FIELD_5;

SQL select sum (Importo) from

(SELECT sum(FIELD_1) as Importo from $(library_path) where FIELD_2= 117030 and FIELD_5= 'D'

     union

     SELECT sum (FIELD_1)*-1 as Importo from $(library_path) where FIELD_2= 117030 and FIELD_5= 'A')

as Residuo from $(library_path);

But when I execute thce code I receive the following message:

SQL##f - SqlState: 37000, ErrorCode: 4294967097, ErrorMsg: [IBM][ODBC driver for the System i Access] [DB2 for i5/OS]SQL0199 - .FROM keyword not provided. Valid Token: FOR SKIP WITH FETCH ORDER UNION EXCEPT OPTIMIZE.

SQL select sum (Importo) from

(SELECT sum(CNRATA) as Importo from ............where CNCLIE = 117030 and CNDAVE = 'D'

     union

     SELECT sum (CNRATA)*-1 as Importo from ............where CNCLIE = 117030 and CNDAVE = 'A')

as Residuo from ............

Does anyone have any idea what the problem is?

Thanks a lot

  Giovanni

[maxgro]

from what I understand

Residuo is the alias you choose for this (inline view)

(    SELECT sum(CNRATA) as Importo from $(library_path) WHERE CNCLIE = '117030' and CNDAVE = 'D'           

    union

    SELECT sum (CNRATA)*-1 as Importo from $(library_path) a where CNCLIE = 117030 and CNDAVE = 'A'

)

0001 is the alias the dbms gives to

sum (Importo)

because you didn't give to sum(Importo) an alias (as ....)

[Anonymous]

You have to alias sum() rather than SQL...  See my post above.