Sql Server Join Problem

[#1]

Hi guys, I have been using QlikView and reading from flatfiles successfully, but I now have a requirement to read from a SQL Server database for my application.

I managed to connect to the db without any issues, but I am just trying to take my SQL Server code and paste it in QlikView as follows:

SQL SELECT

top 200 * from dbname.dbo.view1

inner join dbname.dbo.view2

on dbname.dbo.view1.common_col = dbname.dbo.view2.common_col

QlikView keeps throwing up an error on the join, but I know the join is correct, because it works perfectly in SQL Server.

I also noticed as well that QlikView did not like the alias names I used for the tables which were working fine in SQL Server so I took them out. I am pretty sure I am missing something very obvious and would appreciate it if someone can tell me where I am going wrong so i can proceed.

Thanks

[Miguel_Angel_Baeyens · Accepted solution]

Hi,

Note that when you use SQL the following SELECT is passed by the driver on to the database server, so it has nothing to do with QlikView, to the extent that the driver may not understand the statement even when it works in the query analyzer as it happens in your case, but the query is neither interpreted nor executed by QlikView.

But whay it might be happening is that the returning set has more than one field named alike, and since this is not allowed in QlikView, hence the error. That's why I strongly recommed to always use the LOAD part, so you are in full control on what you pull from the database and put into memory, renaming when necessary. If this is what is happening, though, you will need to rename in the SELECT statement, so all fields come with different names when loaded into QlikView.

Say for example that your working query in SQL but not in QlikView is (names are fake)

SQL SELECT
top 200 dbname.dbo.view1.common_col, dbname.dbo.view1.ID, dbname.dbo.view2.ID from dbname.dbo.view1
inner join dbname.dbo.view2
on dbname.dbo.view1.common_col = dbname.dbo.view2.common_col

You have two ID fields when it comes to QlikView loading, so you will need to rename at least one of them

SQL SELECT
top 200 dbname.dbo.view1.common_col, dbname.dbo.view1.ID, dbname.dbo.view2.ID AS ID2 from dbname.dbo.view1
inner join dbname.dbo.view2
on dbname.dbo.view1.common_col = dbname.dbo.view2.common_col

So the final script looks like

TableName:
LOAD common_col,
     ID,
     ID2;
SQL SELECT
top 200 dbname.dbo.view1.common_col, dbname.dbo.view1.ID, dbname.dbo.view2.ID AS ID2 from dbname.dbo.view1
inner join dbname.dbo.view2
on dbname.dbo.view1.common_col = dbname.dbo.view2.common_col

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

[#3]

Thanks a lot Miguel.

Your correct answer has led to another question.

For some fields, when I put them in the Load part so I can control the select statement, they error saying field not found, but the fields actually exist.

Just wondering if you have come across that also and know a way to handle this.

Cheers

[Miguel_Angel_Baeyens]

Hi,

That's not unsual either. Check that fields are spelled exactly as they are in the database, not in the SQL SELECT statement. QlikView is case sensitive for both field names and values. Assuming all fields in the database are uppercase, the following will work in both QlikView and your query analyzer:

SQL SELECT id, name, address
FROM database.table;

However, the correct LOAD statement will look like

Customer:
LOAD ID,
     NAME,
     ADDRESS;
SQL SELECT id, name, address
FROM database.table;

If the driver returns the fields uppercase even when you wrote them lowercase, then QlikView will read them uppercase. So if in the example you spell instead

Customer:
LOAD ID,
     name, // this will fail, this field doesn't exist because "name" <> "NAME"
     ADDRESS;
SQL SELECT id, name, address
FROM database.table;

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica