SQL Statements in Qlikview

[#1]

Hi,

How to use the SQL complex requests in qlikview.

An exemple of using statement JOIN is welcome.

Thank you

[#2]

From my experience it is usually more efficient to perform the join in the load script than the sql select statement.

Qlikview supports all manner of joins (left/right/inner) and becasue the function is being performed in memory usuing qlikview's own algorithms it is usually much faster e.g. something like:

table1:
load primarykey,a,b,c;
from sql select * from dbo.table1;
right join (table1)
load primarykey,d,e,f;
from sql select * from dbo.table2;

does the same as sql statement:

"select primarykey,a,b,c,d,e,f from dbo.table1 right join dbo.table2 on table1.primarykey=table2.primarykey "

the syntax is from memory so there may be comma or colon misplacements

[#3]

I run this statement in my back and I QlikView errors in TOAD FOR ORACLE, however it works correctly.

select count(a.nmat)
from rhpagent a
left join rhfnom f on f.cdos = a.cdos and
f.nume = '1' and f.cacc = a.nato and
f.ctab = 4
left join rhfnom f1 on f1.cdos = a.cdos and
f1.nume = '1' and f1.cacc = a.cat and
f1.ctab = 8
where a.cdos = '01' and a.nato = '001' and a.cat = '02'
and a.dtes < to_date('01/01/2010');

What to do in this case?

[#4]

It would be useful to know what error it is producing and what the load script entry looks like, i.e. does it go something like:

table1:
load *
from sql select count(a.nmat)
 from rhpagent a
 left join rhfnom f on f.cdos = a.cdos and
 f.nume = '1' and f.cacc = a.nato and
 f.ctab = 4
 left join rhfnom f1 on f1.cdos = a.cdos and
 f1.nume = '1' and f1.cacc = a.cat and
 f1.ctab = 8
 where a.cdos = '01' and a.nato = '001' and a.cat = '02'
 and a.dtes < to_date('01/01/2010');