Full outer join does not work when table 1 is empty

baliyan_vinay — Sat, 16 Nov 2024 19:13:53 GMT

Hello Community,

I am using the below table to get some data.

select distinct nvl(a.key1,b.key1) as key1, nvl(a.key2,b.key2) as key2, nvl(a.key3,b.key3) as key3, a.data1 b.data2 from a Full Outer join b on a.key1=b.key1 and a.key2=b.key2 and a.key3=b.key3 and b.indicator='O' and b.key1=25 and b.key2=3 where and a.indicator='P' and a.key1=25 and a.key2=3 order by nvl(a.key3,b.key3)

now this does not result in any data as table a is empty(No data for indicator P) but table b has data for indicator O.

What I wanted to do with this query is get the data, for cases where table a is empty, from table b and vice-versa and when both have data then two rows.

Can you guys let me know what I am doing wrong here?

Thank you very much

Re: Full outer join does not work when table 1 is empty

Vegar — Sun, 26 Jan 2020 23:04:29 GMT

What you are asking about is in SQL syntax, you might find better help searching SQL forums.

However, Qlik will perform the desired join if you keep the join inside Qlik and not using SQL syntax. You could probably do something like this:

a:
SQL SELECT *
FROM a;

b:
LOAD * ;
SQL SELECT *
FROM b;

JOIN (b)
Load *
Resident a;

Drop table a;

topic Full outer join does not work when table 1 is empty in QlikView

Full outer join does not work when table 1 is empty

Re: Full outer join does not work when table 1 is empty