topic Re: Can't "SQL Select" from multiple tables? in QlikView

Can't "SQL Select" from multiple tables?

Thu, 15 Jan 2015 17:58:33 GMT

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!

Re: Can't "SQL Select" from multiple tables?

Anonymous — Thu, 15 Jan 2015 18:05:18 GMT

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;

Re: Can't "SQL Select" from multiple tables?

jduenyas — Thu, 15 Jan 2015 18:11:13 GMT

Does the script work when in Oracle Management Studio?

Re: Can't "SQL Select" from multiple tables?

Thu, 15 Jan 2015 18:21:20 GMT

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.

Re: Can't "SQL Select" from multiple tables?

maxgro — Thu, 15 Jan 2015 19:00:02 GMT

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

Re: Can't "SQL Select" from multiple tables?

jduenyas — Thu, 15 Jan 2015 21:07:46 GMT

Why do you ODBC and not OLE DB?

Re: Can't "SQL Select" from multiple tables?

Anonymous — Fri, 16 Jan 2015 13:47:54 GMT

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

Re: Can't "SQL Select" from multiple tables?

Anonymous — Fri, 16 Jan 2015 13:49:07 GMT

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

Re: Can't "SQL Select" from multiple tables?

richard_cioci — Tue, 22 Dec 2015 14:50:57 GMT

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.