Incorrect syntax near keyword 'INNER'

[#1]

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?

[#2]

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

AJ

[#3 · Accepted solution]

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 ;

[MayilVahanan]

Hi

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.

[maxgro]

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

[jagan]

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.

[#7]

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

[swuehl]

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

[#9]

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 ]

[#10]

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