Why doesn't multple SQL Joins script work in QlikView? Even though it works in standard SQL engine

[jblomqvist]

Hi,

I have the following SQL query. This works when I run it in a standard SQL query engine (e.g. SQL Server etc)

But it doesn't work in QlikView. Is there a problem with QlikView when it comes to using multiple joins?

One SQL join works but as soon as I do multiple joins it doesn't.

I have ensured the field names are correct and table names are correct so this is not the issue.

SELECT

OrderID,

Orders.CustomerID,

CustomerName,

Orders.EmployeeID,

FirstName,

LastName,

OrderDate

FROM

Orders

INNER JOIN

[Customers]

ON

Orders.CustomerID = Customers.CustomerID

Inner JOIN

Employees

ON

Orders.EmployeeID = Employees.EmployeeID

[ecolomer]

really it is not necessary as the two join qlikview automatically relate the tables if the field name is called equal

you can try this.

[Anonymous]

As Henric says: Don't join - use Applymap instead

     Qlik Design Blog : Don't join - use Applymap in... | Qlik Community

QlikView is a different animal to SQL and is designed for analytical BI, whereas SQL is designed for transactional systems.  Doing things the QlikView way results in good scripts and good data models.  Fighting against QlikView and trying to do things the SQL way results in pain.

There is an old saying about square pegs and round holes.

[marcus_sommer]

Such behaviour has nothing to do with qlikview then qlikview didn't perform the sql-statement - qlilkview transferred only the sql-statement per odbc/oledb-driver to the database which then execute the query and returned the result to qlikview again.

In your case might maybe the driver not appropriate or the query take too long and any timeouts happens or your network isn't stable enough. But before I would check if your user has the proper access-rights to your second join-table Employees which will be probably have some restrictions. Sometimes are external queries on certain data forbidden, too.

- Marcus

[jblomqvist]

Thank you for your replies but I still don't understand why this method won't work.

There seems to be a problem with the way QlikView processes multiple join in SQL Select, FROM part of scripting to me.

I know the result is possible to achieve using QlikView code but I want to know why SQL code won't work when it works in other engines.

[Anonymous]

What kind of "not work" ?  Does it fail with an error, or run ok but returns unexpected data ?

Could you share the log file from the load ?

[sunny_talwar]

I would say share log file and your script? Do you have preceding LOAD statement after SQL SELECT? I would think to get the same results from your SQL query in QlikView and within any other places.

[maxgro]

I tried your query on a sql server database and it works.

I tried the same query in Qlikview, using the same sql database, the script is below and it works, same result; it works.

Could you please post the error?

Qlikview doesn't run the sql query, it just send the query to the DBMS and the DBMS send the result back to Qlik. Every query that runs in sql should run in QlikView.

OLEDB CONNECT32 TO.............;

SELECT

OrderID,

Orders.CustomerID,

CustomerName,

Orders.EmployeeID,

FirstName,

LastName,

OrderDate

FROM Orders

INNER JOIN [Customers] ON Orders.CustomerID = Customers.CustomerID

Inner JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID;