topic Re: Linking Direct Discovery table twice in QlikView

Linking Direct Discovery table twice

agigliotti — Wed, 13 Sep 2017 14:32:57 GMT

Hello,

I'd need to write an sql statement for direct query (DB2/400).

Specifically I need to join the same table twice using different where clause something similar below:

DIRECT QUERY

DIMENSION

"ORDNUM" as "AliasColumn1",

"ORDNRG" as "AliasColumn2",

"ORDAGO" as "AliasColumn3"

MEASURE

ORDNUM,

ORDIFT,

ORDCRD

FROM "library1"."table"

JOIN "library2"."table1"

ON (SUBSTR("library2"."table1"."ATBKEY", 14, 2) ="library1"."table"."ORDTIP" and "library2"."table1"."ATBCOD" = 'C21')

JOIN "library2"."table1"

ON (SUBSTR("library2"."table1"."ATBKEY", 14, 2) ="library1"."table"."ORDTPR" and "library2"."table1"."ATBCOD" = 'C58')

...

but It give me an error for "library2"."table1" duplicated !

I read on direct discovery documentation as below:

It is not possible to refer to tables by alias in the FROM clause, you need to use the full table name.

Can someone help me with this ?

Many thanks in advance.

Best Regards

Andrea

Re: Linking Direct Discovery table twice

Oleg_Troyansky — Thu, 14 Sep 2017 15:14:53 GMT

Hi Andrea,

I'm not a huge expert on complex SQL statements, but it looks like the problem is caused by using the same table twice in the same SQL statement. I think you might be able to go around the problem by creating two separate views for the same table, with different WHERE conditions, and then refer to different views in your JOIN ON statements.

Cheers,

Oleg Troyansky

Upgrade your Qlik skills at the Masters Summit for Qlik - coming to Boston, MA this October!

Re: Linking Direct Discovery table twice

agigliotti — Thu, 14 Sep 2017 15:21:06 GMT

I guess the only way to workaround this direct query limitation could be using a database view.

Thanks for your quick reply.

Andrea

Re: Linking Direct Discovery table twice

Miguel_Angel_Baeyens — Thu, 14 Sep 2017 16:09:30 GMT

The Technical Addendum for the Direct Discovery documentation states some limitations when using multiple tables, which is supported and should work.

Nevertheless, you may want to try as in the examples (see 3.8.2 or 3.8.3 in the doc above).

See also 3.8 with regards to limitations for multiple tables, for example the cardinality rule. Indeed, creating a view, if possible, would be also my preferred solution.

Re: Linking Direct Discovery table twice

agigliotti — Mon, 18 Sep 2017 15:18:40 GMT

does is possible to use CASE statement in DIRECT QUERY ?