Can't "SQL Select" from multiple tables?

[#1]

I've been struggling with getting something to work correctly in Qlik Sense. What I'm trying to do is load records from two tables in our Oracle database. One of the tables contains the universe of data that I'm selecting from. The second table represents a key field in the first table that I'm wanting to use to exclude records from the selection query. Whereas I would typically use an IN clause (for example, select * where table1.keyfield not in ('keyfieldvalue1','keyfieldvalue2', etc.)) to exclude the records I don't want, there are more than 1,000 values that I don't want which exceeds the max number of entries allowable using the IN clause. Therefore, I'm wanting to pull the data from the first table where table1.keyfield is not equal to table2.keyfield.

In SQL this is simple:

Select a.blahblah

          b.blahblahblah

From table1 a, table2 b

Where a.keyfield != b.keyfield;

Yet in Qlik Sense I haven't figured out how to even get the following to work. For example, the following:

TempTable:

  SQL SELECT

      A."CODE",

      A."DESCRIPTION",

      B."CODE",

      B."DESCRIPTION"

  FROM Schema.Table1 as A, Schema.Table2 as B;

generates the following error message when attempting the data load:

SQL##f - SqlState: S1000, ErrorCode: 933, ErrorMsg: [Oracle][ODBC][Ora]ORA-00933: SQL command not properly ended

TempTable:

  SQL SELECT

      A."CODE",

      A."DESCRIPTION",

      B."CODE",

      B."DESCRIPTION"

  FROM Schema.Table1 as A, Schema.Table2 as B

Any help with getting this to work or other alternate means to arrive at the desired subset of data would be greatly appreciated!! Thanks!

[Anonymous]

I think that there needs to be a JOIN in your SQL SELECT code.

Example:

SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate

FROM Orders

INNER JOIN Customers

ON Orders.CustomerID=Customers.CustomerID;

[jduenyas]

Does the script work when in Oracle Management Studio?

[#4]

Within Oracle SQL Developer the script gives the same 933 error. When I change the "as A" and "as B" wording  to remove the word "as" it runs correctly. Yet within Qlik Sense the script

SQL SELECT

      A."CODE",

      A."DESCRIPTION",

      B."CODE",

      B."DESCRIPTION"

  FROM Schema.Table1 A, Schema.Table2 B

results in a "ODBC read failed" message.

[maxgro]

In QlikView, with ODBC and Oracle

this works

select A.EMPNO, B.ENAME

FROM TEST.EMP A, TEST.EMP2 B;

this doesn'twork, same field name

select A.EMPNO, B.EMPNO

FROM TEST.EMP A, TEST.EMP2 B;

this works

select A.EMPNO, B.EMPNO B_EMPNO

FROM TEST.EMP A, TEST.EMP2 B;

I think this is the same in Sense, alias the field in sql

[jduenyas]

Why do you ODBC and not OLE DB?

[Anonymous]

Because you are accessing a DBMS and not a stand alone database on your local machine.

[Anonymous]

I meant to add "you are accessing a DBMS over a network"

[richard_cioci]

This worked for me. I've seen tutorials use 'as' and also exclude it. I believe I read somewhere that it depends on the database your accessing.

So yea, leaving out the 'as' and directly aliasing the table immediately after, 

i.e. Schema.Table1 t1

works for me, and in case anyone is wondering, I'm using an OLE DB connection.