topic Re: Union On SQL Statement On Server in QlikView

Union On SQL Statement On Server

rebelfox — Wed, 25 Nov 2020 16:16:04 GMT

I'm trying to UNION together two tables with different field names on the database server and feed into a QlikView LOAD statement.

The database is an AS400 which is always awkward.

[MyTable]:
LOAD
MyKey;

SQL SELECT AKey As MyKey
FROM Table1
WHERE AYear >= 2013

UNION

    SELECT BKey As MyKey
    From Table2
    WHERE BYear >= 2013;

However I get an error stating 'Field not found - <MyKey>'.

Is it possible to do this?

Re: Union On SQL Statement On Server

t_chetirbok — Mon, 21 Mar 2016 10:56:22 GMT

Hi!

is it work??

[MyTable]:
LOAD
MyKey;

SQL SELECT AKey As MyKey
FROM Table1
WHERE AYear >= 2013 ;

concatenate(MyTable)

LOAD

MyKey;

SQL    SELECT BKey As MyKey
    From Table2
    WHERE BYear >= 2013;

Re: Union On SQL Statement On Server

rebelfox — Mon, 21 Mar 2016 11:13:04 GMT

Not quite but it has pointed me in the right direction. The statement doesn't like the renaming of the field with the SQL statement. If I rename it in the LOAD statement it works. See below.

[MyTable]:
LOAD
AKey As MyKey;

SQL SELECT AKey

FROM Table1
WHERE AYear >= 2013 ;

concatenate(MyTable)

LOAD

BKey As MyKey;

SQL SELECT BKey

From Table2
WHERE BYear >= 2013;

Re: Union On SQL Statement On Server

PradeepReddy — Mon, 21 Mar 2016 11:21:11 GMT

seems to be there is no issues with your script, it should work..

As I tried the same thing in SQL Server, it is working fine with out any issues...

Temp_Data:

Load Temp1;

SQL SELECT DT_SR_KEY as Temp1

FROM DATE_DIM where CLNDR_YR=2014

UNION

SELECT FILING_SR_KEY as Temp1

FROM FILING_TYP_DIM;

Re: Union On SQL Statement On Server

t_chetirbok — Mon, 21 Mar 2016 11:22:40 GMT

maybe you use qualify?

Re: Union On SQL Statement On Server

rebelfox — Mon, 21 Mar 2016 12:13:36 GMT

I think this is another weird thing from the AS400/Iseries. I'm always finding odd issues like this where it works on other platforms.

Re: Union On SQL Statement On Server

maxgro — Mon, 21 Mar 2016 12:44:11 GMT

try this (no preceding load) and after the reload look at the table viewer if your MyKey field is in uppercase

MyTable:

SQL

SELECT AKey as MyKey
FROM Table1
WHERE AYear >= 2013

UNION

    SELECT BKey as MyKey
    From Table2
    WHERE BYear >= 2013;

Re: Union On SQL Statement On Server

Anonymous — Mon, 21 Mar 2016 13:28:45 GMT

Hello Roy,

you cannot use concantenate, because this solution is not the same how sql union command.

your solution is ok and works on my server. Works your sql query on sql Server? Booth keys should have the same data type. If sql querey works, buth QlikView not, you can try it:

[MyTable]:
LOAD
MyKey;

SQL

SELECT MyKey

FROM

(

    SELECT AKey As MyKey
        FROM Table1
        WHERE AYear >= 2013

UNION

    SELECT BKey
       From Table2
       WHERE BYear >= 2013

) a

;

It works for me too