topic Re: Incorrect syntax near keyword 'INNER' in QlikView

Incorrect syntax near keyword 'INNER'

Wed, 08 Jan 2014 20:17:09 GMT

I have been trying to join two tables, an incident table and and a company table

Incident:

SQL SELECT

Company_Id,

Last_Updated

Company.Country,

Company.Company_Name

FROM

Incidents

WHERE

Create_Date >= '2012-01-01'

ORDER BY

Last_Updated

INNER JOIN

Company ON Incidents.Company_Id=Company.Company_Id;

Any ideas why I get the syntax error?

Re: Incorrect syntax near keyword 'INNER'

Wed, 08 Jan 2014 21:09:06 GMT

Hey Mark,

My quick suggestion would be to preload the two tables separately and inner join them in Qlikview rather than using SQL script.

Thanks

Re: Incorrect syntax near keyword 'INNER'

Wed, 08 Jan 2014 21:18:35 GMT

Your inner join should come just after "From Incidents". You are also missing a comma in the "Select". Maybe something like this:

SQL SELECT

Company_Id,

Last_Updated,

Company.Country,

Company.Company_Name

FROM

Incidents

INNER JOIN

Company ON Incidents.Company_Id=Company.Company_Id

WHERE

Create_Date >= '2012-01-01'

ORDER BY

Last_Updated ;

Re: Incorrect syntax near keyword 'INNER'

MayilVahanan — Wed, 08 Jan 2014 22:24:19 GMT

Try like this

SQL SELECT

Company_Id,

Last_Updated,

Company.Country,

Company.Company_Name

FROM

Incidents

INNER JOIN

Company ON Incidents.Company_Id=Company.Company_Id

WHERE

Incidents.Create_Date >= '2012-01-01'

ORDER BY

Incidents.Last_Updated ;

But my suggestion is, if you reload each and every, its gives load to db to fetch the data. Its takes more time. So store the values in qvd. And then use join for faster and avoid load to db.

Re: Incorrect syntax near keyword 'INNER'

maxgro — Wed, 08 Jan 2014 23:29:22 GMT

Hi Mark

you're trying to join at the database level so the sql syntax should be as Mayil and and mhmalpass suggest; in sql you can also use implicit join notation

SQL SELECT

Incidents.Company_Id,

Incidents.Last_Updated,

Company.Country,

Company.Company_Name

FROM

Incidents, Company

WHERE

Incidents.Company_Id=Company.Company_Id

and Incidents.Create_Date >= '2012-01-01'

ORDER BY

Incidents.Last_Updated ;

About joining at the database level or Qlikview level, it depends on many things; for example, if you have a lot of data (2 big tables to join) and a small result set (few record) after the join, I prefer load the db and transfer few data from db to qlik; but this is a subject that deserves a more thorough discussion, the example was just to give you an idea

Regards

Re: Incorrect syntax near keyword 'INNER'

jagan — Thu, 09 Jan 2014 02:49:10 GMT

Hi,

This is the syntax of the select query

SELECT Select_List

FROM Table_List

[WITH (BUFFERING = lExpr)]

[WHERE Conditions]

[GROUP BY Column_List]

[UNION Clause]

[HAVING Conditions]

[ORDER BY Column_List]

[INTO Clause | TO Clause ]

[Additional_Display_Options]

So, Inner join part should come before WHERE clause.

Hope this helps you.

Regards,

Jagan.

Re: Incorrect syntax near keyword 'INNER'

Thu, 09 Jan 2014 10:06:07 GMT

The missing comma was a just a typo when entering this post.

I had also tried the JOIN statement after the FROM but it stills gives the same error...in fact it doesn't matter where I put it it still gives the same error

Re: Incorrect syntax near keyword 'INNER'

swuehl — Thu, 09 Jan 2014 10:35:17 GMT

What SQL dialect / DBMS are you using? Does this work when you are using the SQL from the DBMS console?

Re: Incorrect syntax near keyword 'INNER'

Thu, 09 Jan 2014 11:57:23 GMT

The QlikView Help list this syntax like this...so I had the JOIN at the end rather than after the FROM

select[all | distinct | distinctrow | top n
[percent] ]
*fieldlist

from tablelist
[where criterion ]
[group by fieldlist [having criterion ] ]
[order by fieldlist [asc | desc] ]
[ (inner | left | right | full)join tablename on fieldref = fieldref ]

Re: Incorrect syntax near keyword 'INNER'

Thu, 09 Jan 2014 12:02:16 GMT

This query will be part of an incremental load strategy so I will be storing the data in qvd files now that I have the query working

Re: Incorrect syntax near keyword 'INNER'

swuehl — Thu, 09 Jan 2014 12:42:20 GMT

The Help also states:

"However, the extent to which the SELECT statements are accepted depends largely on the ODBC driver or OLE DB provider used."