Linking Direct Discovery table twice

[agigliotti]

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

[Oleg_Troyansky]

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!

[agigliotti]

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

Thanks for your quick reply.

BR

Andrea

[Miguel_Angel_Baeyens]

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.

[agigliotti]

does is possible to use CASE statement in DIRECT QUERY ?