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

[giakoum]

if instead of using a variable you hard code the from table, does it work?

[#3]

The problem is the same. The error is related to the word from.

[maxgro]

try first the SQL query on your database

when it works, use the query in QlikView

also, from what I understand your query should return 1 field and in Qlik (load) you have 5 fields

[giakoum]

Good point

[maxgro]

also I don't understand the bold part

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);

[jonathandienst]

Hi

I think that is the problem - one too many 'froms'. As Massimo said, test this and get it working in SQLMS first, then try it in QV with literals, and then when that is working replace the literals with the variables.

HTH

Jonathan

[Anonymous]

Giovanni,

I guess you mean this:

SQL select sum (Importo) as Residuo 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')

;

But I have no idea what you preceding load is about (?)

[#9]

Yes, the error was in the from $(library_path) at the end.

Thanks.

[#10]

Good morning, the code tha I use is the following:

Importi:

LOAD

    *;

SQL SELECT sum (Importo) from

(    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'

) as Residuo;

I can't understand why the result where the name of the field is 00001 and not Residuo.

Why ?