topic Re: Problem with the execution of SQL (SELECT UNION) in QlikView

Problem with the execution of SQL (SELECT UNION)

Mon, 16 Feb 2015 14:01:44 GMT

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

Re: Problem with the execution of SQL (SELECT UNION)

giakoum — Mon, 16 Feb 2015 14:07:04 GMT

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

Re: Problem with the execution of SQL (SELECT UNION)

Mon, 16 Feb 2015 14:13:29 GMT

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

Re: Problem with the execution of SQL (SELECT UNION)

maxgro — Mon, 16 Feb 2015 14:13:30 GMT

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

Re: Problem with the execution of SQL (SELECT UNION)

giakoum — Mon, 16 Feb 2015 14:24:21 GMT

Good point

Re: Problem with the execution of SQL (SELECT UNION)

maxgro — Mon, 16 Feb 2015 14:38:43 GMT

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

Re: Problem with the execution of SQL (SELECT UNION)

jonathandienst — Mon, 16 Feb 2015 14:45:25 GMT

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

Re: Problem with the execution of SQL (SELECT UNION)

Anonymous — Mon, 16 Feb 2015 14:46:39 GMT

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 (?)

Re: Problem with the execution of SQL (SELECT UNION)

Mon, 16 Feb 2015 14:59:17 GMT

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

Thanks.

Re: Problem with the execution of SQL (SELECT UNION)

Tue, 17 Feb 2015 08:38:35 GMT

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 ?

Re: Problem with the execution of SQL (SELECT UNION)

maxgro — Tue, 17 Feb 2015 09:27:30 GMT

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

Re: Problem with the execution of SQL (SELECT UNION)

Anonymous — Wed, 18 Feb 2015 16:01:43 GMT

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